Re: O9i: bind variable questions (special)

On Apr 3, 12:04 pm, Andreas Mosmann <mosm..._at_expires-30-04-2007.news- group.org> wrote:
> Hi ng,
>
> maybe that questions sound a bit stupid:
>
> If I use bind variables in queries I can increase the performance
> because
> a) the statement is parsed only 1 time
> b) a statement keeps in cache longer because the same statement with
> different parameters still is the same statement.
>
> 1st Question:
> If I want to do this in an application there is no problem
> SELECT * FROM T1 WHERE T1.F1=:MyParam;
> :MyParam :='A';
>
> But if I work f.e. with SQL worksheet, can I write it in any way like
> {:MyParam :='A'} SELECT * FROM T1 WHERE T1.F1=:MyParam;
>
> 2nd Question:
> A query
> SELECT * FROM T1 WHERE T1.F1='A';
> can be translated into
> SELECT * FROM T1 WHERE T1.F1=:MyParam;
>
> Is there a way to translate
> SELECT * FROM T1 WHERE T1.F1 in ('A','B');
> into
> SELECT * FROM T1 WHERE T1.F1 in (:MyParamList);
> ?
> If not the consequence is that there is no possibility to avoid parsing
> if you have queries like
> SELECT * FROM T1 WHERE T1.F1 in ('A','B');
> SELECT * FROM T1 WHERE T1.F1 in ('A','D','F');
> Is this right?
>
> sorry for that questions, but I did not find an answer by google or by
> Manual. bind variable is a bad keyword
>
> Andreas Mosmann
>
> --
> wenn email, dann AndreasMosmann <bei> web <punkt> de

1 in Sql*plus you would do that by declaring a variable variable p_deptno number;
exec :p_deptno := 10
select * from emp where deptno = :p_deptno

hardly use sql*worksheet, but assume it works in sql worksheet too. exec is short hand for
begin <code> end;

2 Search on asktom.oracle.com for 'dynamic in list'

Hth

--
Sybrand Bakker
Senior Oracle DBA