-
Stored Procedure Output Parameter 'is table'
Can some one advise how to return a 'table' type as output parameter from a stored procedure.
I have a front-end reporting apps that needs multiple-rows as result set (not via cursors). I was told to use a stored procedure that returns 'table' type variables.
I created this package below, but do not know how to test it from SQLPLUS window, to prove that it returns data...
The front-end apps is not Oracle (it is Actuate product). So if you also know what the front-end developers need define the bind variables?
Thanks in advance.
egavish@snet.net
----------------------
--code:
CREATE OR REPLACE PACKAGE pkg_test
AS
--
/* Declare externally visible types, cursor, exception. */
--
/* Declare externally callable subprograms. */
--Use pl/sql tables 12/12/2002 test with psql type tables
TYPE Fld1TabType IS TABLE OF
thetable.field1 %TYPE
INDEX BY BINARY_INTEGER;
TYPE Fld2TabType IS TABLE OF
thetable.field2 %TYPE
INDEX BY BINARY_INTEGER;
fld1_tab Fld1TabType;
fld2_tab Fld2TabType;
PROCEDURE usp_ProcTest1 (
fld1_tab OUT Fld1TabType,
fld2_tab OUT Fld2TabType
);
END pkg_test;
/
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
--GLOBAL VARIABLES
--
v_TradesTotAmt REAL;
v_TradesCnt Int;
--SUB-PROGRAMS: sprocs, functions, cursors, ...
--
--test1
PROCEDURE usp_ProcTest1 (
fld1_tab OUT Fld1TabType,
fld2_tab OUT Fld2TabType)
AS
i binary_integer := 0;
BEGIN
FOR therec IN (select field1,field2
from thetable)
LOOP
i:=i+1;
fld1_tab(i):=therec.field1;
fld2_tab(i):=therec.field2;
END LOOP;
END usp_ProcTest1;
END pkg_test;
/
---script to create base table and load
--create and load thetable
CREATE TABLE Thetable (Field1 NUMBER, Field2 varchar2(25) );
/*
(2) Insert 3 records into the test table
*/
INSERT INTO Thetable VALUES (12, 'A');
INSERT INTO Thetable VALUES (1, 'B');
INSERT INTO Thetable VALUES (3, 'C');
----------------------------------------------------------------
Last edited by egavish; 12-16-2002 at 06:12 PM.
-
Hi,
Test it should be something like this:
set serveroutput on
clear
declare
this_fld1_tab pkg_test.Fld1TabType;
this_fld2_tab pkg_test.Fld2TabType;
i integer;
begin
pkg_test.usp_ProcTest1(this_fld1_tab,this_fld2_tab);
i := this_fld1_tab.first;
while i is not null
loop
dbms_output.put_line('Fld1: '||this_fld1_tab);
dbms_output.put_line('Fld2: '||this_fld2_tab);
i := this_fld1_tab.next(i)
end loop;
end;
/
I don't have an answer to "How to bind it in the front-end"
Regards
Ben de Boer
-
Greate. Thank you.
It worked for me (with minor modifications)
----
declare
this_fld1_tab pkg_test.Fld1TabType;
this_fld2_tab pkg_test.Fld2TabType;
i integer;
begin
pkg_test.usp_ProcTest1(this_fld1_tab,this_fld2_tab);
i := this_fld1_tab.first;
while i is not null
loop
dbms_output.put_line('Fld1: '||to_char(this_fld1_tab(i)));
dbms_output.put_line('Fld2: '||this_fld2_tab(i));
i := this_fld1_tab.next(i);
end loop;
end;
/
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|