Tuesday, November 24, 2015

Oracle Procedure returns table Pl-Sql

Create a table type


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;    



 

2 comments:

  1. I blog frequently and I really appreciate your information. This great article has truly peaked my interest

    ReplyDelete
  2. By Visiting This Site I Found Cool Stuff Here Keep It Up. picbear

    ReplyDelete