Is there way to use table of varchar 2 in select statement

Question

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

Possible duplicate of [Create an Oracle function that returns a table](https://stackoverflow.com/questions/2829880/create-an-oracle-function-that-returns-a-table) — Kaushik Nayak, Apr 25 '19 at 13:47
You may want to use a `PIPLELINED TABLE` function as in the linked answer. — Kaushik Nayak, Apr 25 '19 at 13:48

score 2 · Accepted Answer · answered Apr 25 '19 at 15:14

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);

score 0 · Answer 2 · answered Apr 25 '19 at 15:14

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);

Is there way to use table of varchar 2 in select statement

2 Answers2