Print Thread
Page 2 of 2 1 2
Re: select with in-clause as parameter
#7299 04/08/05 06:07 PM
Joined: Feb 2004
Posts: 41
R
Ralf Offline OP
Member
OP Offline
Member
R
Joined: Feb 2004
Posts: 41
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;
/

Re: select with in-clause as parameter
#7300 04/08/05 07:50 PM
Joined: Aug 1999
Posts: 22,221
Member
Offline
Member
Joined: Aug 1999
Posts: 22,221
It might work like this (haven't tried it!!):
Code
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.


Marco Kalter
Allround Automations
Re: select with in-clause as parameter
#7301 04/11/05 10:56 AM
Joined: Feb 2004
Posts: 41
R
Ralf Offline OP
Member
OP Offline
Member
R
Joined: Feb 2004
Posts: 41
And how does my delphi-code look like???

Re: select with in-clause as parameter
#7302 04/11/05 09:33 PM
Joined: Aug 1999
Posts: 22,221
Member
Offline
Member
Joined: Aug 1999
Posts: 22,221
That depends. What does your myTableType look like?


Marco Kalter
Allround Automations
Re: select with in-clause as parameter
#7303 04/12/05 11:19 AM
Joined: Feb 2004
Posts: 41
R
Ralf Offline OP
Member
OP Offline
Member
R
Joined: Feb 2004
Posts: 41
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.

Re: select with in-clause as parameter
#7304 04/12/05 09:12 PM
Joined: Aug 1999
Posts: 22,221
Member
Offline
Member
Joined: Aug 1999
Posts: 22,221
I will try to work something out.


Marco Kalter
Allround Automations
Re: select with in-clause as parameter
#7305 04/13/05 07:03 PM
Joined: Mar 2005
Posts: 14
Russian
W
Member
Offline
Member
W
Joined: Mar 2005
Posts: 14
Russian
May be the following example help you...
Code
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):
Code
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.

Re: select with in-clause as parameter
#7306 04/14/05 01:49 PM
Joined: Feb 2004
Posts: 41
R
Ralf Offline OP
Member
OP Offline
Member
R
Joined: Feb 2004
Posts: 41
Ooops...

Read following post.

Re: select with in-clause as parameter
#7307 04/14/05 02:36 PM
Joined: Feb 2004
Posts: 41
R
Ralf Offline OP
Member
OP Offline
Member
R
Joined: Feb 2004
Posts: 41
Sorry, made a mistake in my test program.
Now it works great!

Thanks Worad for the example!

Page 2 of 2 1 2

Moderated by  support 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.1.33 Page Time: 0.039s Queries: 14 (0.010s) Memory: 2.5592 MB (Peak: 3.0437 MB) Data Comp: Off Server Time: 2024-05-20 22:14:02 UTC
Valid HTML 5 and Valid CSS