Question (Was: PL/SQL - Returning resultset-like values from functions)

From: Teresa <mypokky_at_hotmail.com>
Date: 20 Jan 2003 13:59:25 -0800
Message-ID: <c8e4a165.0301201359.54946a2f_at_posting.google.com>

To Adrain or Someone:
I am trying to create a package.
In the pacakge, it will (1)a function(or procedure) that will return a chunk of recordset.
And then (2) join it with anther query at another procedure. While doing the search, I found Adrain's sample code. I tried and it didn't like the CAST part. (I use TOAD to do the compile.)

Can someone help me?
What did I do wrong?

thanks... ~T

ORACLE ERROR MESSAGE:
PLS-00513: PL/SQL function called from SQL must return value of legal SQL type

MY SOURCE CODE:
CREATE OR REPLACE PACKAGE PackTest
AS
  TYPE t_cursorRef IS REF CURSOR;
  TYPE myTableType IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;   FUNCTION test_fnc RETURN myTableType;
  PROCEDURE test (i_fuins in number := null, i_fuinv in number := null,

                  io_sum_num in out number, io_str in out varchar2,
		  io_cursor in out t_cursorRef);			 

END PackTest;
/

CREATE OR REPLACE PACKAGE BODY PackTest
AS

---

• FUNCTION test_fun

  ---

  FUNCTION test_fnc RETURN myTableType AS CURSOR my_cursor IS select object_name from sys.all_objects where object_name in ('ALL_ALL_TABLEA', 'ALL_COL_PRIVS');

      t_return_array myTableType;
      i number;

  BEGIN
    i := 1;
    FOR thecur IN my_cursor LOOP

      t_return_array(i) := thecur.object_name;
      dbms_output.put_line('Ans: ' || thecur.object_name || 
                            ' --- ' || t_return_array(i));
      i := i + 1;		

    END LOOP;
    RETURN t_return_array;
  END test_fnc;

---

• PROCEDURE test

  ---

  PROCEDURE test (i_fuins in number := null, i_fuinv in number := null, io_sum_num in out number, io_str in out varchar2, io_cursor in out t_cursorRef) AS v_cursor t_cursorRef; sum_num number; BEGIN dbms_output.put_line('###### test ######'); select count(*) into sum_num from sys.all_objects where object_name in (select column_value from table(cast(test_fnc() as myTableType)));

    io_sum_num := sum_num;                 

    open v_cursor for
    select owner, object_name
    from sys.all_objects
    where object_name in
      (select column_value from table(cast(test_fnc() as myTableType)));

    io_cursor := v_cursor;
    close v_cursor;
  EXCEPTION
    WHEN OTHERS THEN
    BEGIN
      dbms_output.put_line(SUBSTR(SQLERRM,1,150));     END;
  END test;
END PackTest;
/ Received on Mon Jan 20 2003 - 22:59:25 CET