CODEWITHSUNDEEP
arraysoracle-databaseplsql
AlanShar
AlanShar

Reputation: 25

Query from array

I am getting error while trying to select values from an array, like following code

declare result CLOB;
  myarray selected_pkg.num_array := selected_pkg.num_array();
begin
  myarray.extend(3);
  myarray(1) := 1; myarray(2) := 5; myarray(3) := 9;
  EXECUTE IMMEDIATE 'select column_value from table (cast(myarray AS selected_pkg.num_array))';
  COMMIT;
end;

ORA-00904: "MYARRAY": invalid identifier

Please suggest. Thanks, Alan

Upvotes: 0

Views: 1360

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

  • First off, there doesn't appear to be any reason to use dynamic SQL here.
  • Second, if you want to run a SELECT statement, you need to do something with the results. You'd either need a cursor FOR loop or you'd need to BULK COLLECT the results into a different collection or otherwise do something with the results.
  • Third, if you want to use a collection in SQL, that collection must be defined in SQL not in PL/SQL.

Something like this will work (I'm not sure if that's what you want to do with the results)

SQL> create type num_arr is table of number;
  2  /

Type created.

SQL> declare
  2    l_nums num_arr := num_arr( 1, 2, 3, 7 );
  3  begin
  4    for i in (select column_value from table( l_nums ))
  5    loop
  6      dbms_output.put_line( i.column_value );
  7    end loop;
  8  end;
  9  /
1
2
3
7

PL/SQL procedure successfully completed.

Upvotes: 2

Dmitry Orlov
Dmitry Orlov

Reputation: 43

execute immediate is not need at this point. Use fetch or loop cursors in proc.

Upvotes: 0

Related Questions