Re: Using Host Variables in ODBC -Oracle Enviornment

"FaheemRao" <faheemrao_at_yahoo.com> wrote in message
>
> My Application using ODBC to connect to Oracle Database,
> Now as I am hardcoding the values in SQL queries like this
> select * from table_name where column_name = 1234;
> Now every time I issue above SQL if column_name is different every
> time like
> select * from table_name where column_name = 444;
>
> Oralce parse this SQL , which takes time,

The time Oracle spend on parsing is negligible in a situation like this. It should not be a factor.

The primary reason for bind variables is when dealing with a high volume OLTP system, where you have 1000's of SQL update per minute, many using the the same SQL update statement, with different values.

However, you are doing a SELECT. It is unlikely that you are running 100's of this same SELECT at the same time.

Not saying that you do not need to use bind variables. Where possible, use them always. But if you have a performance problem with your application and this particular SELECT statement, I doubt that making use of bind variables will solve it (unless, as I mentioned, you have 100's of these running at the same time).

> now my question is that is there any way that I can use host
> variables Using ODBC, Or any other way so that Oracle do not parse
> my SQL Queries every time .

The ODBC API does support bind variables. But you are likely not using the ODBC API directly, or else you will have seen that in the sample code and API specs. Which means that you are likely using Visual Basic? It should be possible in VB too.. checks the docs. In Delphi, it is pretty simple:

--
begin
  query.SQL.Clear;
  query.SQL.Add('select * from table_name where column_name = :NAME');
  query.Params[0].AsNumber := 1234;
  query.Open;

  -- do stuff --
  query.Close;
  query.Params[0].AsNumber := 400;
  query.Open;
end;
--

VB should support something similar.


--
Billy