Nested Tables in a stored procedure

Question

The PL/SQL script shown below is failing to execute at line # 20:

    declare 

      type string_table is table of varchar(100);
      v_names string_table := string_table();

    begin

      v_names.EXTEND(3);

      v_names(1) := 'name1';
      v_names(2) := 'name2';
      v_names(3) := 'name3';

      dbms_output.put_line(v_names(1));
      dbms_output.put_line(v_names(2));
      dbms_output.put_line(v_names(3));
      dbms_output.put_line(v_names.COUNT());

20    select * from table(v_names);

    end;

The following are the error messages reported:

ORA-06550: line 20, column 23:
PLS-00642: local collection types are not allowed in SQL statements

ORA-06550: line 20, column 17:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item

ORA-06550: line 20, column 3:
PL/SQL: SQL Statement ignored.

Any ideas how to resolve this issue.

Please note I wrote this code only for the purpose of posting this question to SO. Similar code is part of a larger package...

Thanks in advance!

Zen

score 3 · Accepted Answer · answered Jul 04 '11 at 16:45

You can only use SQL (like SELECT) on types that are defined in the database, using CREATE TYPE:

create type string_table is table of varchar(100);
/

Then:

declare 

      v_names string_table := string_table();

    begin

      v_names.EXTEND(3);

      v_names(1) := 'name1';
      v_names(2) := 'name2';
      v_names(3) := 'name3';

      dbms_output.put_line(v_names(1));
      dbms_output.put_line(v_names(2));
      dbms_output.put_line(v_names(3));
      dbms_output.put_line(v_names.COUNT());

      select * from table(v_names);

    end;

Thanks Tony! It worked. I apreciate your help very much indeed. Do you know how can a stored procedure that takes in an argument of this user defined type be called from C#? — UncleZen, Jul 04 '11 at 17:38

Nested Tables in a stored procedure

1 Answers1