select with in-clause as parameter

[Ralf]

Hello,

I want to use a query like:

select * from table where id in (1,2,3)

but then with a parameter for the in-set.

select * from table where id in test)
or
select * from table where id in :test

how do i assign the variable?
can you give me a sample?
do i have to use a TOracleObject?

 

[Andrey Maximenko. Ukraine]

Did you read Thomas Kyte
"Expert One-on-One Oracle",
Wrox Press Ltd&

 

[Andrey Maximenko. Ukraine]

CREATE OR REPLACE
TYPE spis_integer AS OBJECT
(
ID NUMBER
)
/

CREATE OR REPLACE
TYPE Int_Table AS TABLE OF spis_integer
/

CREATE OR REPLACE FUNCTION Get_Spis_Integer(p_spis IN VARCHAR2)
RETURN Int_Table
DETERMINISTIC
AS

mytbl Int_Table := Int_Table();
i NUMBER;
j NUMBER;
k NUMBER;
k1 NUMBER;
k2 NUMBER;
v_spis_str VARCHAR2(100);
v_str VARCHAR2(100);
v_spis NUMBER;
v_error BOOLEAN;
iCount NUMBER;
maxCount NUMBER DEFAULT 100000;
BEGIN
IF p_spis IS NULL THEN
RETURN mytbl;
END IF;
i := 1;
iCount := 0;
LOOP
j := INSTR(p_spis, ',', i);
IF j = 0 THEN
j := INSTR(p_spis, ';', i);
END IF;
BEGIN
IF j > 0 THEN
v_spis_str := SUBSTR(p_spis, i, j-i);
ELSE
v_spis_str := SUBSTR(p_spis, i);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- EXIT WHEN v_spis_str IS NULL;
k := INSTR(v_spis_str, '-', 1);
BEGIN
IF k > 0 THEN
k1 := trim(SUBSTR(v_spis_str, 1, k-1));
k2 := trim(SUBSTR(v_spis_str, k+1));
ELSE
k1 := v_spis_str;
k2 := k1;
END IF;
v_error := FALSE;
EXCEPTION
WHEN OTHERS THEN
v_error := TRUE;
DBMS_OUTPUT.put_line ('Error in (FUNCTION Get_Spis_Integer)' || CHR(13) ||
SQLERRM || CHR(13) || p_spis=' || p_spis);
END;
IF v_error = FALSE AND k1 IS NOT NULL THEN
k2 := NVL(k2, k1);
FOR k IN k1 .. k2 LOOP
mytbl.extend(1);
mytbl(mytbl.LAST):=spis_integer(k);
EXIT WHEN mytbl.LAST > maxCount;
END LOOP;
END IF;
iCount := iCount + 1;
i := j+1;
EXIT WHEN j = 0;
EXIT WHEN iCount > 100000;
END LOOP;

RETURN mytbl;
END;
/

SELECT * FROM the(SELECT cast(Get_Spis_Integerp_spis) AS Int_Table) FROM dual)

 

[Ralf]

i hope this can be done in another way.
this one is very slow.
i think it can be done with a TOracleObject, but i don't know how.

 

[Andrey Maximenko. Ukraine]

And, Did you read Thomas Kyte ?

 

[Marco Kalter]

I want to use a query like:

select * from table where id in (1,2,3)

but then with a parameter for the in-set.

select * from table where id in test)

In this case you can declare the :test variable as a substitution variable (otSubst), and assign "1,2,3" as a value (without the quotes). Substitution variable values are inserted into the SQL before it is sent to the server, so this should work properly.

 

[Andrey Maximenko. Ukraine]

"the substitution variable is replaced by its value in the SQL text before the statement is sent to the server"
We will get new SQL text after every setVariable.
As for me, It's not good idea.

 

[Ralf]

The target is to speed up queries.
Previously we replaced markers in the SQL on the client side. So every query has to be evaluated by Oracle (explain plan etc). Therefor we want to use as much parameters as possible, making it possible for oracle to use the same explain plan.
The suggestion by Andrey is more than 2 times slower than putting the arguments directly in the SQL clientside.
Does the substitution variable make it possible for oracle to use the same explain-plan (sounds like it does not)?

PS : I didn't read Thomas Kyte...

 

[Marco Kalter]

Does the substitution variable make it possible for oracle to use the same explain-plan (sounds like it does not)?

No, but the cost based optimizer may be able to come up with optimized query plans for different sets of id's. Which option is faster depends on many factors.

 

[Ralf]

But if I want to do this with a parameter.
A table-parameter of something like that (not a string/function combination).
Is that possible or not?

 

[Ralf]

So I want an equivalent in Delphi of the following in PL/SQL:

create or replace function table_select_test
return integer
is
lvDeleted myTableType := myTableType(0, 1);
lvCount integer;
begin
select count(*)
into lvCount
from receiver
where deleted in (select * from table(table_select_test.lvDeleted));
return lvCount;
end;
/

 

[Marco Kalter]

It might work like this (haven't tried it!!):

begin
  select count(*)
    into :lvCount
    from receiver
   where deleted in (select * from table(cast :lvDeleted as myTableType));
end;

The :lvDeleted variable is associated with a collection that is instantiated and filled in Delphi.

 

[Ralf]

And how does my delphi-code look like???

 

[Marco Kalter]

That depends. What does your myTableType look like?

 

[Ralf]

myTableType looks like whatever is going to work!
I don't care what type it is.
Maybe my problem isn't clear...

I want to use a TOracleDataset in which I put SQL that contains an in-struct and in which the in-part is a variable. So something like:

'select * from table where id in test)'

Runtime I assign a list of integers to the variable 'test' and execute the query.
So I want to use variables all the way to Oracle (no substitution etc.).
How do I do this in Delphi and what types do I need in Oracle? How does the query look like?
Can you provide me a full example?

Thanks in advance.

 

[Marco Kalter]

I will try to work something out.

 

[Worad]

May be the following example help you...

In Oracle:
--Is Number in set?
create or replace type TNumbers as table of number(9);
/
create or replace function Value_In(p_Value in number, p_Set in TNumbers) return number is
  v_Result number(1) := 0;
begin
  begin
    select 1 into v_Result from dual
      where p_Value in (select * from table(p_Set));
  exception
    when NO_DATA_FOUND then null;
  end;
  return v_Result;
end;
/
//
In Delphi the PL/SQL block for TOracleDataSet component SQL property:
select * from yourtable t where Value_In(:v_ID, :v_Set) = 1;
Though you need to bind value to variables v_ID and v_Set.
To bind value to v_Set you need to use the followning code:
...
var
  v_Set: TOracleObject;
...
begin
...
  v_Set := TOracleObject.Create(orcSession, 'TNUmbers', '');
...
  v_Set.Elements[1] := 1;
  v_Set.Elements[2] := 2;
...
  orcYourTable.SetVariable('v_ID', 1);
  orcYourTable.SetComplexVariable('v_Set', v_Set);
...
end;
// :)

You can test the function with the following code in "Test window"(PL/SQL Developer):

declare
  r number(1) := Value_In(:v_ID, :v_Set);
begin
  dbms_output.put_line(r);
end;
with the following initialization values:
Test1:
  v_ID = "1";
  v_Set = "new TNumbers(1, 2, 3)";
  Tab sheet "DBMS Output" contain "1" as a result.
Test2:
  v_ID = "1";
  v_Set = "new TNumbers(2, 3)";
  Tab sheet "DBMS Output" contain "0" as a result.

 

[Ralf]

Ooops...

Read following post.

 

[Ralf]

Sorry, made a mistake in my test program.
Now it works great!

Thanks Worad for the example!