select from table of varchar2

January 14, 2022

i have a type
create or replace TYPE "CUSTOM_DATA" IS TABLE OF VARCHAR2(4000) .
i can assign some values and print to output without problem

declare
cust_data CUSTOM_DATA;
begin
cust_data:=CUSTOM_DATA('A','B','C');

 FOR i IN 1 .. cust_data.COUNT
           LOOP
              DBMS_OUTPUT.put_line (cust_data(i));
           END LOOP; 

end;

But how can i use cust_data in select clause , because i will assign them to a ref cursor , how can i do this? my code below is not working

SELECT COLUMN_VALUE as val FROM table(cust_data);

>Solution :

Your code works:

DECLARE
  cust_data CUSTOM_DATA;
BEGIN
  cust_data:=CUSTOM_DATA('A','B','C');

  FOR i IN (SELECT COLUMN_VALUE as val FROM table(cust_data))
  LOOP
    DBMS_OUTPUT.put_line(i.val);
  END LOOP; 
END;
/

or:

DECLARE
  cust_data CUSTOM_DATA;
  cur       SYS_REFCURSOR;
  value     VARCHAR2(4000);
BEGIN
  cust_data:=CUSTOM_DATA('A','B','C');

  OPEN cur FOR
    SELECT COLUMN_VALUE as val FROM table(cust_data);

  LOOP
    FETCH cur INTO value;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.put_line(value);
  END LOOP; 
END;
/

Which both output: