BIND multiple

[Carlos Tesch]

HELP ME !!!!
Using bind to multiple variables.
Example:

SELECT vCOMPL.ID_TRABA, vCOMPL.ID_FOTRA
FROM vCOMPL
WHERE vCOMPL.ID_COREQ IN ID_COREQ)
-------------------
where ID_COREQ = 6616, 6618, 5816, 8886
for example.

 

[sinys]

Use "substitution" variable, set string

var
s : String;
begin
...
s := '6616, 6618, 5816, 8886';
...
OracleDataSet1.SetVariable('ID_COREQ', s); // where ID_COREQ - "substitution" variable

 

[Carlos Tesch]

The replacement does not apply to the concept of bind, because Oracle loads in Shared Memory instructions (HASH). By replacing all the instructions are different for exchange is the parameter's declared value.
I need the implementation of BIND that is, to Oracle, these instructions are always the same, regardless of the value of the parameter.

 

[Roeland]

Use collections and then cast them to a table.

Make a Type

CREATE OR REPLACE TYPE t_Array_Integer IS TABLE OF integer

Use this type in your method

SELECT vCOMPL.ID_TRABA, vCOMPL.ID_FOTRA
FROM vCOMPL
WHERE vCOMPL.ID_COREQ IN (SELECT column_value
                          FROM TABLE (CAST (:ID_COREQ AS t_Array_Integer)))

That's how we do it.

Roeland