Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Question Unanswered: Oracle Functions Returning multiple values

    I need to return multiple values from an oracle function and do not know how accomplish this task.

    I have read that you can return an array, but am unable to do so, or find examples.

    Any help would be appreciated.

    The idea is below - it stops after the first return - like one would expect. So how do I get the data into an array and return the array?


    CREATE OR REPLACE FUNCTION STATS
    (pStatDate Date, pStatType char) RETURN number IS

    vStat1 number;
    vStat2 number;
    vStat3 number;
    vStatString varchar2(6);
    vEndDate date;

    if UPPER(pStatType) = 'M' then
    vStatString := to_char(pStatDate,'mmyyyy');
    select sum(Field1), sum(field2),sum(field3)
    into vstat1, vstat2,vstat3
    from v_stat_daily
    where to_char(Start_date,'mmyyyy') = vStatString;
    else
    vStatString := to_char(pStatDate,'yyyy');
    select sum(Field1), sum(field2),sum(field3)
    into vstat1, vstat2,vstat3
    from v_stat_daily
    where to_char(Start_date,'yyyy') = vStatString;
    end if;

    RETURN vstat1;
    RETURN vstat2;
    RETRUN vstat3;

    END STATS;
    Last edited by whaslam; 10-08-03 at 10:06.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    if you're only calling this function via PL/SQL (or anonymous blocks) then you can use in/out parameters.

    PHP Code:
    function fredid in numberp1 out numberp2 out numberp3 out number) return number is....
    p1 := 0;
    p2 := 10;
    P3 := 20;
    return (
    1);
    end Fred
    You could also use a cursor to "union all" a number of values where the number of return values is not know.

    Hth
    Bill

  3. #3
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Indeed you can use IN/OUT parameters, but doing so, you can no longer invoke your function from from within a query or DML-statement.

    CVM.

  4. #4
    Join Date
    Sep 2003
    Posts
    16
    You could use a PL/SQL table (array) to return your values.

    For instance, first create a type:-

    TYPE MyArray IS TABLE OF NUMBER;

    Then define your function:-

    CREATE OR REPLACE FUNCTION STATS
    (pStatDate Date, pStatType char) RETURN MyArray IS
    <your code>


    Then implement a counter, increment it for each value and populate the PL/SQL table. Your calling app will get the array returned.

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    The post from BillM was helpful, but need to call this function as
    select Stats(date,type) from dual. When doing this with output parameters, I get an oracle error - like cvandemaele has said.

    I was trying to work out PaulMcClure's method but am having trouble with it. More details on how to make that work would be greatly appreciated.


    WRH

  6. #6
    Join Date
    Sep 2003
    Posts
    16
    Here's a simplified version...

    First create your pl/sql table type

    CREATE OR REPLACE TYPE pmc_tab AS TABLE OF NUMBER;

    Then a table:-

    CREATE TABLE v_stats_daily(start_date date, field1 number, field2 number, field3 number);

    Some insert into the table so we've got test data...

    insert into v_stats_daily values('08-OCT-2003',10,20,30);
    insert into v_stats_daily values('08-OCT-2003',40,50,60);
    insert into v_stats_daily values('08-OCT-2003',70,80,90);


    Then create your function:-

    CREATE OR REPLACE FUNCTION PMC_STATS
    (pStatDate Date) RETURN pmc_tab IS

    MyArray pmc_tab;
    vstat1 NUMBER;
    vstat2 NUMBER;
    vstat3 NUMBER;

    BEGIN

    MyArray := pmc_tab();

    select sum(Field1), sum(field2),sum(field3)
    into vstat1, vstat2,vstat3
    from v_stats_daily
    where Start_date = pStatDate;

    MyArray.extend;
    MyArray(1) := vstat1;
    MyArray.extend;
    MyArray(2) := vstat2;
    MyArray.extend;
    MyArray(3) := vstat3;

    RETURN MyArray;

    END;

    In SQL*Plus:-

    SQL>set serverout on


    Then a lump of PL/SQL to run your function:-

    DECLARE
    MyDate DATE;
    MyArray pmc_tab;
    i NUMBER;
    numOut NUMBER;
    BEGIN
    MyArray := pmc_stats('08-OCT-2003');

    dbms_output.put_line('Table count: '||to_char(MyArray.count));

    for i in 1..MyArray.last LOOP
    numOut := MyArray(i);
    --if numOut is null then
    dbms_output.put_line('Value: '||to_char(numOut));
    --end if;
    END LOOP;

    END;
    /

    Your output will look like:-

    Table count: 3
    Value: 120
    Value: 150
    Value: 180

    Hope this helps,

    Paul.

  7. #7
    Join Date
    Oct 2003
    Posts
    3

    Cool Many Thanks

    Thank you for spelling that out in detail. I have been able to follow the example you gave and make it work for my situation. I really appreciate your help!

    WRH

  8. #8
    Join Date
    May 2010
    Posts
    1

    Thumbs up Nice !!

    Thanks, I appreciate the detail (wish everyone could answer like this).


    Quote Originally Posted by PaulMcClure View Post
    Here's a simplified version...

    First create your pl/sql table type



    CREATE OR REPLACE TYPE pmc_tab AS TABLE OF NUMBER;

    Then a table:-

    CREATE TABLE v_stats_daily(start_date date, field1 number, field2 number, field3 number);

    Some insert into the table so we've got test data...

    insert into v_stats_daily values('08-OCT-2003',10,20,30);
    insert into v_stats_daily values('08-OCT-2003',40,50,60);
    insert into v_stats_daily values('08-OCT-2003',70,80,90);


    Then create your function:-

    CREATE OR REPLACE FUNCTION PMC_STATS
    (pStatDate Date) RETURN pmc_tab IS

    MyArray pmc_tab;
    vstat1 NUMBER;
    vstat2 NUMBER;
    vstat3 NUMBER;

    BEGIN

    MyArray := pmc_tab();

    select sum(Field1), sum(field2),sum(field3)
    into vstat1, vstat2,vstat3
    from v_stats_daily
    where Start_date = pStatDate;

    MyArray.extend;
    MyArray(1) := vstat1;
    MyArray.extend;
    MyArray(2) := vstat2;
    MyArray.extend;
    MyArray(3) := vstat3;

    RETURN MyArray;

    END;

    In SQL*Plus:-

    SQL>set serverout on


    Then a lump of PL/SQL to run your function:-

    DECLARE
    MyDate DATE;
    MyArray pmc_tab;
    i NUMBER;
    numOut NUMBER;
    BEGIN
    MyArray := pmc_stats('08-OCT-2003');

    dbms_output.put_line('Table count: '||to_char(MyArray.count));

    for i in 1..MyArray.last LOOP
    numOut := MyArray(i);
    --if numOut is null then
    dbms_output.put_line('Value: '||to_char(numOut));
    --end if;
    END LOOP;

    END;
    /

    Your output will look like:-

    Table count: 3
    Value: 120
    Value: 150
    Value: 180

    Hope this helps,

    Paul.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •