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.
|
