Results 1 to 6 of 6

Thread: TABLE function

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: TABLE function

    Perhaps this isn't possible, but I am trying to get more than one column to return from a nested table function. I can get this to work:

    Code:
    SQL> create or replace type return_rec_t as table of varchar2(10);
      2  /
    
    Type created.
    
    SQL> CREATE OR REPLACE function return_rec_f return return_rec_t
      2  as
      3    return_rec_row return_rec_t;
      4  begin
      5    select first_name
      6    bulk collect into return_rec_row
      7    from td_staff;
      8    
      9    return return_rec_row;
     10    
     11  end;
     12  /
    
    Function created.
    
    SQL> SELECT column_value
      2    FROM TABLE (return_rec_f) names;
    
    COLUMN_VAL
    ----------
    DAN
    LOGON
    BRETT
    TASHA
    GRETCHEN
    ANITA
    SATA
    ...
    
    225 rows selected.
    I can't get this to work so far, though, when I try to set things up so that multiple columns will be returned from the function "return_rec_f". I'm not even sure if, ultimately, I could get that to work as a nested table either.

    Code:
    SQL> create or replace type return_rec_obj 
      2  as object (
      3   fname varchar2(10),
      4   lname varchar2(15)
      5  );
      6  /
    
    Type created.
    
    SQL> create or replace type return_rec_t as table of return_rec_obj;
      2  /
    
    Type created.
    
    SQL> CREATE OR REPLACE function return_rec_f return return_rec_t
      2  as
      3    return_rec_row return_rec_t;
      4  begin
      5    select first_name, last_name
      6    bulk collect into return_rec_row
      7    from td_staff;
      8    
      9    return return_rec_row;
     10    
     11  end;
     12  /
    
    Warning: Function created with compilation errors.
    
    SQL> show errors
    Errors for FUNCTION RETURN_REC_F:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/3      PL/SQL: SQL Statement ignored
    7/3      PL/SQL: ORA-00947: not enough values
    -Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    I got something slightly different to work, thanks to http://www.databasejournal.com/featu...le.php/2222781

    Code:
    drop type return_rec_obj;
    
    create or replace type return_rec_obj 
    as object (
     fname varchar2(10),
     lname varchar2(15)
    );
    
    drop type return_rec_t;
    
    create or replace type return_rec_tab as table of return_rec_obj
    
    
    CREATE OR REPLACE function return_rec_f return return_rec_tab PIPELINED
    is
      TYPE ref0 is REF CURSOR;
      cur0 ref0;
      out_rec return_rec_obj := return_rec_obj(NULL,NULL);
    begin
      open cur0 for 
        'select first_name, last_name from td_staff';
    	loop
    	  fetch cur0 into out_rec.fname, out_rec.lname;
    	  exit when cur0%NOTFOUND;
    	  PIPE ROW (out_rec);
    	end loop;
      close cur0;
      
      return;
      
    end;
    
    select * from TABLE(return_rec_f);
    -Chuck

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I would've tried concatenating the two columns together and then split them on an outer function. Can you try this ?

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by chuck_forbes
    Perhaps this isn't possible, but I am trying to get more than one column to return from a nested table function. I can get this to work:

    Code:
    SQL> create or replace type return_rec_t as table of varchar2(10);
      2  /
    
    Type created.
    
    SQL> CREATE OR REPLACE function return_rec_f return return_rec_t
      2  as
      3    return_rec_row return_rec_t;
      4  begin
      5    select first_name
      6    bulk collect into return_rec_row
      7    from td_staff;
      8    
      9    return return_rec_row;
     10    
     11  end;
     12  /
    
    Function created.
    
    SQL> SELECT column_value
      2    FROM TABLE (return_rec_f) names;
    
    COLUMN_VAL
    ----------
    DAN
    LOGON
    BRETT
    TASHA
    GRETCHEN
    ANITA
    SATA
    ...
    
    225 rows selected.
    I can't get this to work so far, though, when I try to set things up so that multiple columns will be returned from the function "return_rec_f". I'm not even sure if, ultimately, I could get that to work as a nested table either.

    Code:
    SQL> create or replace type return_rec_obj 
      2  as object (
      3   fname varchar2(10),
      4   lname varchar2(15)
      5  );
      6  /
    
    Type created.
    
    SQL> create or replace type return_rec_t as table of return_rec_obj;
      2  /
    
    Type created.
    
    SQL> CREATE OR REPLACE function return_rec_f return return_rec_t
      2  as
      3    return_rec_row return_rec_t;
      4  begin
      5    select first_name, last_name
      6    bulk collect into return_rec_row
      7    from td_staff;
      8    
      9    return return_rec_row;
     10    
     11  end;
     12  /
    
    Warning: Function created with compilation errors.
    
    SQL> show errors
    Errors for FUNCTION RETURN_REC_F:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/3      PL/SQL: SQL Statement ignored
    7/3      PL/SQL: ORA-00947: not enough values
    -Chuck
    You were close , you just needed to select an object based on the columns in order to bulk collect in the object table, not the columns directly :

    Code:
    rbaraer@Ora10g> create table td_staff
    (
        first_name varchar2(10),
        last_name varchar2(15)
    );  2    3    4    5
    
    Table created.
    
    rbaraer@Ora10g> create or replace type return_rec_obj
    as object (
        fname varchar2(10),
        lname varchar2(15)
    );
    /  2    3    4    5    6
    
    Type created.
    
    rbaraer@Ora10g> create or replace type return_rec_t as table of return_rec_obj;
    /  2
    
    Type created.
    
    rbaraer@Ora10g> CREATE OR REPLACE function return_rec_f return return_rec_t
    as
      return_rec_row return_rec_t;
    begin
      select return_rec_obj(first_name, last_name)
      bulk collect into return_rec_row
      from td_staff;
    
       return return_rec_row;
    
    end;
    /  2    3    4    5    6    7    8    9   10   11   12
    
    Function created.
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Cool!

    So, does one approach have an advantage over the other: BULK COLLECT over CURSOR with PIPELINE? It seems like the former would benefit from a lack of context-switching between the PL/SQL & SQL engine, while the other may have a performance benefit due to the PIPELINE, balanced out by the slower cursor-based processing.

    -Chuck

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by chuck_forbes
    Cool!

    So, does one approach have an advantage over the other: BULK COLLECT over CURSOR with PIPELINE? It seems like the former would benefit from a lack of context-switching between the PL/SQL & SQL engine, while the other may have a performance benefit due to the PIPELINE, balanced out by the slower cursor-based processing.

    -Chuck
    I don't know. I think both are good ways to achieve your goal but I can't tell which one would be more efficient. I think you will have to test and benchmark .

    You will also have to weigh the pros and cons of creating object types on one side and creating pipelined functions on the other side .

    If you do such benchmark between the two methods, I'd be interested in your results.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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