Home » SQL & PL/SQL » SQL & PL/SQL » How to return a locally created PL/SQL table from a procedure
How to return a locally created PL/SQL table from a procedure [message #36141] Wed, 07 November 2001 08:34 Go to next message
Poonamb
Messages: 16
Registered: October 2001
Junior Member
Hello,

I have a procedure in a package in which (due to the requirments, this was the best alternative), I have created a PL/SQL table of multiple columns and populated them.

Now, I need to send this PL/SQL table back to the web client. My problem is that since it is locally created (from a local cursor within the procedure), it is not visible outside the scope of the procedure.

What is the best way to return this PL/SQL table contents?

Please advise.
Thanks in advance.

----------------------------------------------------------------------
Re: How to return a locally created PL/SQL table from a procedure [message #36149 is a reply to message #36141] Wed, 07 November 2001 18:44 Go to previous messageGo to next message
Malli
Messages: 10
Registered: November 2001
Junior Member
Hi PB,
U have to use Reference Cursors.
First u declare the Ref Cursor in a Package
as follows.
-------------------------------------
CREATE Procedure TestPack
AS
TYPE rc IS REF CURSOR;
END;
--------------------------------------
Then u declare a "out" mode parameter in ur procedure with this "rc" type as follows.
--------------------------------------------------Create Procedure
(
param1 IN NUMBER,
rcusor OUT TestPack.rc
)
AS
BEGIN
--suppose ur temporary table name is "TempTab"
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE TempTab(Col1 Number, Col2 VARCHAR)';
EXECUTE IMMEDIATE 'Insert INTO TempTab VALUES(1,''TEST'')';
OPEN rcursor FOR
'SELECT * FROM TempTab';

END;
--------------------------------------------------
Try it. I think it will help u. Here i use Global Temporary tables, if u want to use PL/SQL tables then also the same logic will work for u.

----------------------------------------------------------------------
Re: How to return a locally created PL/SQL table from a procedure [message #36161 is a reply to message #36149] Thu, 08 November 2001 05:55 Go to previous message
Poonamb
Messages: 16
Registered: October 2001
Junior Member
Thanks Malli.

I am using a PL/SQL table and after populating the table, I give the following command :

OPEN SendAllCursor FOR
'SELECT * FROM tbl_AllTickerData';

P.S. I have declared SendAllCursor as an out return cursor and tbl_AllTickerData is the name of my PL/SQL table.

However, it gives me an error ORA-00942 (table does not exist). What could be the problem?

Thanks in advance.

----------------------------------------------------------------------
Previous Topic: how to? on insert a record to parent > insert to child also
Next Topic: procedure
Goto Forum:
  


Current Time: Fri Mar 29 00:31:13 CDT 2024