Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004

    Unanswered: cannot access rows from a non-nested table

    Hi All,

    I would like to write a procedure which will do followings:

    1. store value into a TABLE type collection
    2. store the TABLE data into a refcursor.
    3. Procedure will return the refcursor

    The code is as below:

    create or replace procedure test1(v_ref_cur out sys_refcursor)
    type g_rec is record (eno number, enm varchar2(20));
    type g_tt is table of g_rec;
    l_tt g_tt := g_tt();
    for i in 1..5 loop
    l_tt(l_tt.last).eno := i;
    l_tt(l_tt.last).enm := i||'desc';
    end loop;
    for i in l_tt.first..l_tt.last loop
    end loop;

    open v_ref_cur for select eno,enm from (select column_value from table (l_tt));


    but the code is showing the below error :
    cannot access rows from a non-nested table

    Could any one please help?

    Thanks with Regards,

  2. #2
    Join Date
    Mar 2007

    when I ran your code (as anonymous block), I found this line in error stack:
    PLS-00642: local collection types not allowed in SQL statements
    You should see it too. It is self-explanative enough - local collections (PL/SQL) cannot be used in SELECT (SQL). The only workaround would be creating collection as SQL type (CREATE TYPE) - as RECORDs are not present in SQL, the G_REC should be OBJECT type.

    You should resume whether store those data in local collections or return it as CURSOR. The first one could be replaced with dynamic SELECT statement in CURSOR, something like this:
    v_ref_cur sys_refcursor;
    l_stmt varchar2(4000);
      l_stmt := 'select 1 eno, ''1test'' enm from dual';
      for i in 2..5 loop
        l_stmt := l_stmt||' union all select '||i||', '''||i||'test'' from dual';
      end loop;
      open v_ref_cur for l_stmt;
    Of course, storing those values in table or SQL collection would be better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts