PL/SQL - cannot access rows from a non-nested table item

Question

I am trying to get all the column names of my_table, and would like to make it as string using my_fn. For example, let say my_table has columns named "year", "month", "day". Get this column names using all_tab_cols table, and store it into tmp collection. Using for-loop, I want these column names as "month, year, day" using my_fn. Currently, I am getting the following error.

ERROR LOG - This is what I am getting as an error

SQL Error: ORA-22905: cannot access rows from a non-nested table item
22905. 00000 -  "cannot access rows from a non-nested table item"
*Cause:    attempt to access rows of an item whose type is not known at
           parse time or that is not of a nested table type
*Action:   use CAST to cast the item to a nested table type

SQL CODE - Here is my code.

CREATE OR REPLACE TYPE col_array as table of varchar2(1000);
/
CREATE OR REPLACE FUNCTION my_fn (
    input_1 IN VARCHAR2,
    input_2 IN VARCHAR2,
    input_3 IN VARCHAR2) 
    RETURN varchar2 AS
      tmp col_array;
      txt varchar2(1000);

    BEGIN
    SELECT column_name bulk collect into tmp
    FROM all_tab_cols 
    where owner = 'me' and table_name ='my_table'; 

      for i in 1..tmp.count loop
        txt := txt || to_char( tmp(i) ) || ',';
      end loop;

    //txt := 'wow';

    RETURN txt;

    END my_fn;
/
SELECT * FROM TABLE(my_fn('','',''));

I also tried the following simple code, but still not working. I may need more knowledge on how to use CAST function :(

BEGIN        
    txt := 'wow';    //or  txt := CAST('wow' as varchar2);         
RETURN txt;

Your help would be much appreciated!!

What Oracle version are you using?

Mureinik
– Mureinik

2014-10-10 13:28:03 +00:00
Commented Oct 10, 2014 at 13:28 — Mureinik
– Mureinik, Commented Oct 10, 2014 at 13:28

Thorsten Kettner · Accepted Answer · 2014-10-10 13:34:06Z

6

Your function returns a string not a collection. You could use

SELECT my_fn('','','') FROM dual;

to get the value.

answered Oct 10, 2014 at 13:34

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PL/SQL - cannot access rows from a non-nested table item

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related