is it possible to do something like that?

say if i pass down a array of values, can i dynamically create a SQL from those values to execute?

now that i think about it....
maybe ProC will better suited for this?

Mark

Sounds like dynamic SQL, but I'd need an example of what you want to do to be sure.

for example,

i will pass down a array of values( the total count of values varies)

and i will build a generic SQL:

select value
from test_table
where (value = arrary_of_values[1])
(value = arrary_of_values[2])
.
.
.
;

statement within the parenthesis is what i will need to construct, based on how many values are there.

Mark

Ah, that doesn't require dynamic SQL, but it uses some fancy stuff:

SQL> create type numtab_type as table of number;
2 /

Type created.

SQL> declare
2 array_of_values numtab_type := numtab_type( 51778, 41400, 38460 );
3 begin
4 for r in
5 ( select * from all_objects
6 where object_id in (select * from table(select cast(array_of_values as numtab_type) from dual))
7 )
8 loop
9 dbms_output.put_line(r.object_name);
10 end loop;
11* end;
SQL> /
EMP
DEPT
SALGRADE

PL/SQL procedure successfully completed.

As you can see, I queried from ALL_OBJECTS the 3 rows with OBJECT_IDs 51778, 41400 and 38460 which were stored in the array.
The array needs to be of a SQL type though (i.e. CREATE TYPE statement), it will not work with PL/SQL collections.

thanks for the help!

so if i want to use numtab_type as a input for my procedure, can i do soemthing like:

create procedure test123(array_of_num numtab_type in)

begin
end test123;
/

or execute it like: EXEC test123((11, 22, 33));?


Thanks,

Mark

Almost:

Actually I´m actually doing a program that reads an array of queries and stores the result in an array. In order to archive that i´ve a pl/sql "The Processor" who receives the array and process each record with the EXECUTE IMMEDIATE command, its something like this:


EXECUTE IMMEDIATE vCODE INTO vVALOR(vID_REGLA);

where vVALOR was declared this way:

TYPE aVALORES IS TABLE OF VARCHAR2(30);
vVALOR aVALORES:=aVALORES();

after the DECLARE section you must expand de index of the array in order to get

vVALOR.EXTEND(9999);


I HOPE THIS HELP YOU.

SALUDOS FROM MEXICO