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.

Neothor:

As a suggestion, try using Associative Arrays with BULK COLLECT and FORALL. I am thinking the variable should point to an Associative Array to hold your table..

__________________
Disclaimer: The above comments are solely the opinion of one person and not to be construed as a directive or an incentive to commit fraudulent acts.