0

I need to dynamically create a query and execute using Execute Immediate, I am facing the problem in appending the Vaaray variable. Getting Error

pls-00306 wrong number or types of arguments in call to ||

Vaaray //Its a type number

 select ver_id bulk collect into Ver_Array from ( Select id from table)

No issue with below query as only id variable is used:

Execute Immediate 'Select ID, name , Date, time
from table 
where id = ' || v_UC2_id

Error with below query

Execute Immediate 'Select ID, name , Date, time
from table 
where id = ' || v_UC2_id
|| ' and ver_id in ( SELECT * FROM TABLE ( ' 
|| Ver_Array 
|| ' )'

Tried to extract the query and concatenate in comma saperated values but the final result comes as String but field used in query is Number

Not sure how to handle this in dynamic query

Siva
  • 9,043
  • 12
  • 40
  • 63

1 Answers1

1

The SQL you're writing is concatenating an array with a string, so you get an error.

You can do it like this:

create or replace type vat is varray(10) of number:
/

declare
ivat vat:=vat(1,2,3);
res number;
begin
execute immediate 'select sum(rn) from tarr where rn in (select column_value from table (:varrt))' into res using ivat;
dbms_output.put_line(res);
end;
/

Here I'm selecting just one row and value. If you have multiple rows and columns, then you better declare a cursor for this SQL, and loop thru it.

gsalem
  • 1,957
  • 1
  • 8
  • 7
  • Thanks for the answer, yes realised arrays can't be handled just like numbers. Will try but if I extract a single value then I am forced to run the query every time and query I use is big and will be run on millions of rows at least which will make process slow in the due course. Thanks for the answer will try what was suggested – Siva Aug 13 '20 at 10:32