I have a stored function which returns table of varchar2. And I want to use it in select

stored function :-

create or replace PACKAGE TESTSTR AS 

 TYPE strings_t IS TABLE OF VARCHAR2 (4000);

FUNCTION strings
   RETURN strings_t; 

END TESTSTR;

select * from  TABLE (TESTSTR.strings())

it is throwing me invalid data type error

ORA-00902: invalid datatype

2 Answers

2
Matthew McPeak On Best Solutions

If you want to use your list outside of PL/SQL, as in your post, you need to define an OBJECT type in the database. E.g.,

CREATE OR REPLACE TYPE my_string AS OBJECT (val varchar2(4000));
CREATE OR REPLACE TYPE my_string_t AS TABLE OF my_string;

... and then use my_string_t in place of your PL/SQL strings_t type.

As long as your list of strings has fewer than 32768 elements, you can use the pre-defined SYS.ODCIVARCHAR2LIST object type instead, saving you the need to declare any new object types.

E.g.,

CREATE OR REPLACE PACKAGE teststr AS
  FUNCTION strings RETURN sys.odcivarchar2list;
END teststr;

CREATE OR REPLACE PACKAGE BODY teststr AS
  FUNCTION strings RETURN sys.odcivarchar2list IS
    l_list sys.odcivarchar2list;
  BEGIN
    SELECT rownum 
    BULK COLLECT INTO l_list
    FROM DUAL
    CONNECT BY ROWNUM <= 1000;

    RETURN l_list;
  END strings;
END teststr;

SELECT * FROM table(teststr.strings);
0
harshkumar satapara On

Create function with below option like function strings return string_t pipelined is --declare your type variable as empty begin --put your logic here pipe row(out_variable); exception --if any end;

get out out by using query: select * from table(tester.strings);