Bind Variables

Horace · Post by **Horace** » Tue 22 Jan 2008 18:53

Hello,

I am writing this because I cannot find anything under the keywords BIND VARIABLES in ODAC Help file.

I need to execute the SELECT query listed below, MANY MANY times with different parameters (right now it is listed with hardcoded literals like: ANNA BANANA ).

Normally I would use Bind Variables to avoid HARD PARSING and to avoid re-planning this query by the Oracle Server, each time it is executed.

...but ODAC help has no entry under "Bind Variables" so I need to ask you how to open this query multiple times with different parameters yet perform SOFT PARSING only (like with Bind Variables) ?

Note, that I do not want ODAC to concatenate the parameters into the SQL query string as literals and send it like that to the Oracle Server - that's wrong and very inefficient.
I want the Oracle Server to perform the variable substitiution (not ODAC) without replanning the query (a.k.a. softparsing). For an example see:
http://www.oracle-base.com/articles/mis ... iables.php

Also, which ODAC Class is best for doing this (TOraSQL, TOraQuery, TOraSmartQuery, TOraStoredProc, etc...) ?

I'm using Oracle 10g, ODAC v6.25, Delphi v7 in direct NET mode.

Code: Select all

SELECT
     ENAM,FAMILIEN,GEBURT, i
FROM    (  
            SELECT /*+ INDEX_RS_ASC(PATIENT ENAM_FAMI_IDDE) FIRST_ROWS */
               row_number() OVER (PARTITION BY 
NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN') ORDER BY 
NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID) I,          
               ENAM,
               FAMILIEN,
               GEBURT
            FROM PATIENT
            WHERE NLSSORT("ENAM",'nls_sort=GERMAN')>=NLSSORT('ANNA','nls_sort=GERMAN') AND 
(NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR 
NLSSORT("FAMILIEN",'nls_sort=GERMAN')>=NLSSORT('BANANA','nls_sort=GERMAN'))
            ORDER BY NLSSORT("ENAM",'nls_sort=GERMAN'),NLSSORT("FAMILIEN",'nls_sort=GERMAN'),ID
           ) a
  WHERE rownum=3 OR  
NLSSORT("ENAM",'nls_sort=GERMAN')>NLSSORT('ANNA','nls_sort=GERMAN') OR 
NLSSORT("FAMILIEN",'nls_sort=GERMAN')>NLSSORT('BANANA','nls_sort=GERMAN'));

Challenger · Post by **Challenger** » Wed 23 Jan 2008 13:10

ODAC doesn't substitute parameters with their values in SQL text, it uses variables binding internally.
If you are going to execute the same SQL statement with different parameter values, you should call the TOraQuery.Prepare method before execution. For more information please refer to the "Increasing performance" topic in ODAC help.

Horace · Post by **Horace** » Wed 23 Jan 2008 18:27

The "Increasing performance" topic in ODAC help states that :

To execute SQL statements TOraSQL component is more preferable than TOraQuery. It can give additional several percent performance gain.

...but in your reply you suggested using TOraQuery. Which is more correct ?

Could you provide a sample Delphi code for using the bind variables, for a trivial query such as:

Code: Select all

SELECT name, dob FROM emp WHERE dob >= :dob

I cannot find a useful code sample in the ODAC help file.

Horace · Post by **Horace** » Mon 28 Jan 2008 15:52

Challenger, are you there ?

Horace

Challenger · Post by **Challenger** » Tue 29 Jan 2008 07:33

You can not open SELECT statements using TOraSQL component. The recommendation about using TOraSQL component is correct for DML operations.

Code: Select all

  OraQuery1.SQL.Text := 'SELECT name, dob FROM emp WHERE dob >= :dob';
  OraQuery1.Prepare;
  while True do begin
    OraQuery1.ParamByName('dob').AsInteger := p;
    OraQuery1.Open;
    ...
    OraQuery1.Close;
  end;

Horace · Post by **Horace** » Tue 29 Jan 2008 22:38

Challenger,

The "SELECT" statement belongs to DML class of operations. See:
http://www.orafaq.com/faq/what_are_the_ ... l_commands
Did you mean to write: "using TOraSQL component is correct for DDL operations" ?

Thanks for the sample code on using parameters. It clears things up :)

Q1: Am I forced to explicitly specify the parameter data type as e.g. "TParam.AsInteger" to match the data type of the column in the table ?

Q2: What if the column "dob" is of type DateTime but I write ...ParamByName('dob').AsString := '2008.01.29' ?

Q3: Does ODAC know the data type of the column, that is referred to by a given parameter ?
...if yes, at what point does ODAC learn the column data type ?

Challenger · Post by **Challenger** » Wed 30 Jan 2008 09:30

I meant all operations that do not retrieve rows form database.

Q1: You should either set DataType and Value properties of TParam or use AsXXX (AsInteger) property. In the second case DataType will be set automatically.

Q2: The parameter datatype will be ftString when you set the value by the AsString property. Everything will be fine if Oracle supports this type of conversion.

Q3: ODAC knows about parameter typeswhen describing stored procedures only (CreateProcCall, TOraStoredProc). In other cases you should set datatypes manually.