Oracledataset with runtime query & variables

[pderuiter]

I have an oracledataset with which i want to run queries.
Setting the sql is no problem, but when the sql contains variables i get stuck.
What is the correct way to determine and add variables to an oracledataset from a query set at runtime?

I tried using TParam.ParseSQL, but when i add the params i find that way i get an ORA error about an incorrect variable name.

p := TParams.Create;
    try
      p.ParseSQL(OracleDataSet.SQL.Text, true);
      for i := 0 to Pred(p.Count) do
      begin
        s := InputBox('Enter value for variable', p[i].Name, '');
        OracleDataSet.DeclareVariable(p[i].Name, otString);
        OracleDataSet.SetVariable(p[i].Name, s);
      end;
    finally
      p.Free;
    end;
    OracleDataSet.Open; // <- ORA-01745 Invalid variable name

The query:
SELECT
*
FROM
ecare.wens wns
WHERE
Upper(wns.resources) like '%' || :RESOURCE || '%'

 

[Andrey Maximenko. Ukraine]

SELECT *
  FROM V$RESERVED_WORDS
 WHERE keyword = 'RESOURCE'

You have to change "RESOURCE" to "P_RESOURCE"

 

[pderuiter]

argh.. Thank you

Is the way i'm doing this (using TParam) the correct way? What does the button "Scan SQL" in the property editor do? I would have expected "native" support for dynamic variable gatering

 

[Marco Kalter]

If you want to prompt for variable values, then you must indeed to some processing like this. There is no explicit support for this in the TOracleDataSet component.