Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 21st, 2009, 06:18 PM
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.
 
Old August 21st, 2009, 10:53 PM
Friend of Wrox
Points: 793, Level: 10
Points: 793, Level: 10 Points: 793, Level: 10 Points: 793, Level: 10
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2005
Location: OKC, OK, USA.
Posts: 227
Thanks: 1
Thanked 7 Times in 7 Posts
Default

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.




Similar Threads
Thread Thread Starter Forum Replies Last Post
usin g stored procedure with parameter Sheraz Khan ASP.NET 2.0 Basics 1 September 5th, 2007 12:27 AM
Query Parameter of a Stored Procedure tarang SQL Server 2000 4 July 25th, 2007 11:43 AM
How to pass a parameter to a stored procedure? MaxGay2 VB.NET 2002/2003 Basics 1 November 8th, 2006 02:48 PM
out parameter from procedure??? cgpedja Access 2 July 8th, 2004 01:37 PM
passing table as a parameter to stored procedure pankaj_daga SQL Server 2000 7 September 30th, 2003 05:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.