View Single Post
 
Old August 21st, 2009, 06:18 PM
Neothor Neothor is offline
Registered User
 
Join Date: Aug 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Unhappy Procedure with table as parameter

HI

I'm doing a procedure in Oracle 10g that receives as parameter the name of a table, and based on this throws a select and keep it in a variable for insert it with bulk in another table, the procedure works only if I indicate the name of the table in this way:

DECLARE
TYPE t_pruebap IS TABLE OF siintegra.MUL_TRANSACCION%ROWTYPE;
v_pruebap t_pruebap;
CURSOR c1 IS
select * from siintegra.mul_transaccion where rownum<=150;
BEGIN
OPEN c1;
loop
FETCH c1 BULK COLLECT INTO v_pruebap limit 100;


But if I try to make it stored how I said initially giving the parameter of entry it does not work I annex the code and the mistakes that it send:


CREATE OR REPLACE PROCEDURE
proc_insert (nombre IN char) IS

TYPE t_pruebap IS TABLE OF nombre %ROWTYPE;
v_pruebap t_pruebap;
CURSOR c1 IS
select * from siintegra.nombre where rownum<=50;
BEGIN
OPEN c1;
loop
FETCH c1 BULK COLLECT INTO v_pruebap limit 100;
begin
FORALL i IN 1..v_pruebap.count save exceptions
insert into siintegra.MUL_PRUEBAJGC values v_pruebap(i);

exception when OTHERS then
dbms_output.put_line('Errors:'||sql%bulk_exception s.count);
/*for i in 1 .. sql%bulk_exceptions.count loop
dbms_output.put_line('index:'||sql%bulk_exceptions (i).error_index);
dbms_output.put_line('code:'||sql%bulk_exceptions( i).error_code);
dbms_output.put_line('message:');
dbms_output.put_line(sqlerrm(sql%bulk_exceptions(i ).error_code));
end loop;*/

end;
commit;
exit when c1%notfound;
end loop;
commit;
CLOSE c1;
END;
/

Warning: Procedure build with compilation errors.

SQL> SHOW ERRORS PROCEDURE proc_insert

Errors for PROCEDURE PROC_INSERT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/3 PL/SQL: Item ignored
4/30 PLS-00310: with %ROWTYPE attribute, 'NOMBRE' must name a table,
cursor or cursor-variable

7/9 PL/SQL: SQL Statement ignored
7/33 PL/SQL: ORA-00942: table or view does not exist
11/7 PL/SQL: SQL Statement ignored
11/34 PLS-00597: expression 'V_PRUEBAP' in the INTO list is of wrong
type

14/3 PL/SQL: SQL Statement ignored
14/46 PL/SQL: ORA-00904: : invalid identifier

I tried to put pipes (||) before the parameter as says the documentation and sends me mistake also, help me please I am new in this of PLSQL.

THANKS.