Using Bind Variables inside of Query Templates

[Former Member]

Is it possible to use bind variables inside of a query template against an oracle database? I have queries that are executed from Business Logic Services many times and rather than having oracle parse the query everytime I would like to use oracle bind variables which will improve performance. If it is possible an example would be greatly appreciated.

[salvatore_castro]

Brett,

According to this site it is supported via the JDBC interface:

<a href="http://www.akadia.com/services/ora_bind_variables.html">http://www.akadia.com/services/ora_bind_variables.html</a>

Now to define a bind variable in a Fixed Query:

variable deptno number

exec :deptno := 10

select * from emp where deptno = :deptno;

This should work, please let me know.

Sam

[Former Member]

this is seen as an invalid sql statement when run through a fixed query.

"A SQL Error has occurred on query, ORA-00900: invalid SQL statement"

[salvatore_castro]

Brett,

Think I forgot to put in the semicolons...

variable deptno number;

exec :deptno := 10;

select * from emp where deptno = :deptno;

Give that a try...

Sam

[Former Member]

I tried that as well and I recieve the same invalid sql error. Does anyone know if this is a limitation of the query template / jdbc driver combination? Is anyone using bind variables successfully within an xMII query template?