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?

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

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_Integer(:p_spis) AS Int_Table) FROM dual)

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.

And, Did you read Thomas Kyte ?

[quote]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)[/quote]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.

"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.

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...

[quote]Does the substitution variable make it possible for oracle to use the same explain-plan (sounds like it does not)?[/quote]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.

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?

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;
/

It might work like this (haven't tried it!!):
The :lvDeleted variable is associated with a collection that is instantiated and filled in Delphi.

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

That depends. What does your myTableType look like?

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.

I will try to work something out.

May be the following example help you...
You can test the function with the following code in "Test window"(PL/SQL Developer):

Ooops...

Read following post.

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

Thanks Worad for the example!