Query from array

Question

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

Justin Cave · Accepted Answer · 2013-04-26 12:24:30Z

2

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.

answered Apr 26, 2013 at 12:24

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

2 Comments

AlanShar Over a year ago

Now I modified my code to be ` declare result CLOB; myarray selected_pkg.num_array := selected_pkg.num_array(1,5,9); begin for i in (select column_value from table( myarray )) loop dbms_output.put_line( i.column_value ); end loop; COMMIT; end; ` But still I am getting error ORA-21700: object does not exist or is marked for delete

Justin Cave Over a year ago

@user2323433 - Note item 3 from the list above. If you want to use a collection in SQL, you'd need to define the collection in SQL. You can't declare the collection in PL/SQL. You can't use the collection defined in the package. You'd need to define the collection in SQL as I did in my example.

Dmitry Orlov · Accepted Answer · 2013-04-26 12:22:00Z

0

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

answered Apr 26, 2013 at 12:22

Dmitry Orlov

435 bronze badges

Collectives™ on Stack Overflow

Query from array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related