Stored Procedure Output Parameter 'is table'
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Stored Procedure Output Parameter 'is table'

  1. #1
    Join Date
    Jun 2000
    Location
    Bethel CT, USA
    Posts
    18

    Question 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 05:12 PM.

  2. #2
    Join Date
    Feb 2001
    Posts
    180
    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

  3. #3
    Join Date
    Jun 2000
    Location
    Bethel CT, USA
    Posts
    18

    Thumbs up

    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
  •  



Click Here to Expand Forum to Full Width