CODEWITHSUNDEEP
delphidelphi-xe7
BIBD
BIBD

Reputation: 15414

How can I pass a list of values as a parameter for a TOraQuery?

I have a TOraQuery with SQL defined something like this

SELECT ML.ID, Ml.detail1, Ml.detail2
FROM MY_LIST ML 
WHERE 
    ML.ID in (:My_IDS)

If I was to build this query on the fly, I'd naturally end up with something like this:

SELECT ML.ID, Ml.detail1, Ml.detail2
FROM MY_LIST ML 
WHERE 
    ML.ID in (14001,14002,14003)

However, I'd like to pass in 14001,14002,14003 as a parameter.

  myListQuery.Active := False;
  myListQuery.ParamByName('My_IDS').AsString := '14001,14002,14003';
  myListQuery.Active := True;

But of course that generates an ORA-01722: invalid number. Do I have any other option other than building up the query on the fly.

Upvotes: 1

Views: 2347

Answers (3)

BIBD
BIBD

Reputation: 15414

You can use a "Macro"

Not quite what I was looking for, but it's a hair closer to a parameter than building the SQL on the fly.

Create the TOraQuery like this

SELECT ML.ID, Ml.detail1, Ml.detail2
FROM MY_LIST ML 
WHERE 
    ML.ID in (&My_IDS)

Now I can pass in 14001,14002,14003 as a macro.

myListQuery.Active := False;
myListQuery.MacroByName('My_IDS').value := '14001,14002,14003';
myListQuery.Active := True;

Upvotes: 0

Sam M
Sam M

Reputation: 4166

You can do it but it requires some additional setup. Hopefully this works with your version of Oracle.

  1. Create a table type
  2. Create a function that converts your string to your table type

  3. Use CAST in the subquery. Pass your value to the bind variable using the same thing you have in your code (i.e. ParamByName('').AsString).

    create or replace type myTableType as table of varchar2 (255);

create or replace function in_list( p_string in varchar2 ) return myTableType as 
    l_string        long default p_string || ',';
    l_data          myTableType := myTableType();
    n               number;
begin
  loop
      exit when l_string is null;
      n := instr( l_string, ',' );
      l_data.extend;
      l_data(l_data.count) := 
             ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
      l_string := substr( l_string, n+1 );
  end loop;

return l_data;
end;

select * from THE ( select cast( in_list(:MY_BIND_VARIABLE) as mytableType ) from dual ) a

If this works for you, credit for the answer and example code goes to Tom Kyte from Oracle who runs asktom.com. https://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:210612357425

Upvotes: 1

Jens Borrisholt
Jens Borrisholt

Reputation: 6402

AFAIK, it is not possible directly.

You'll have to convert the list into a SQL list in plain text.

For instance:

function ListToText(const Args: array of string): string; overload;
var 
  i: integer;
begin
  result := '(';
  for i := 0 to high(Args) do 
    result := result+QuotedStr(Args[i])+',';
  result[length(result)] := ')';
end;


function ListToText(const Args: array of integer): string; overload;
var 
  i: integer;
begin
  result := '(';
  for i := 0 to high(Args) do 
    result := result+IntToStr(Args[i])+',';
  result[length(result)] := ')';
end;

To be used as such:

SQL.Text := 'select * from myTable where intKey in '+ListToText([1,2,3]);
SQL.Text := 'select * from myTable where stringKey in '+ListToText(['a','b','c']);

Or in your case: 

myListQuery.SQL.Text := 'SELECT ML.ID, Ml.detail1, Ml.detail);
myListQuery.SQL.Add('FROM MY_LIST ML ');
myListQuery.SQL.Add('WHERE ');
myListQuery.SQL.Add('ML.ID in ') +  ListToText([14001,14002,14003]);

Upvotes: 1

Related Questions