CREATE OR REPLACE TYPE XX_REC_TYPE AS OBJECT
(
a1 VARCHAR2(50) ,
a2 VARCHAR2 (50)
);
CREATE OR REPLACE TYPE XX_TBL_TYPE
IS
TABLE OF XX_REC_TYPE;
Create a procedure with out arguments of table type
CREATE OR REPLACE PROCEDURE XX_SVC(
p_input in VARCHAR2,
p_output1 out varchar2,
p_XX_TBL_TYPE out XX_TBL_TYPE )
AS
ord_tbl XX_TBL_TYPE:=XX_TBL_TYPE ();
BEGIN
select 'xxx' into p_output1 from dual;
FOR r1 IN
(
SELECT value1,value2 from xx_tbl
AND value1 = p_input
)
LOOP
ord_tbl.Extend;
ord_tbl(ord_tbl.last) := XX_REC_TYPE (r1.value1,r1.value2);
END LOOP;
p_XX_TBL_TYPE := ord_tbl;
END;
Call the procedure to return the values
set serveroutput on
clear screen
declare
lc_output varchar2(240);
ord_tbl XX_TBL_TYPE;
begin
XX_SVC(
p_input =>'12345',
p_output1 => lc_output,
p_XX_TBL_TYPE => ord_tbl);
dbms_output.put_line('p_output1-->'||lc_output);
for i in ord_tbl.first .. ord_tbl.last loop
dbms_output.put_line(ord_tbl(i).value1 || ' ' ||
ord_tbl(i).value2);
end loop;
end;
I blog frequently and I really appreciate your information. This great article has truly peaked my interest
ReplyDeleteBy Visiting This Site I Found Cool Stuff Here Keep It Up. picbear
ReplyDelete