Re: package variable

A copy of this was sent to "boris" <boris_at_antec.carrier.kiev.ua> (if that email address didn't require changing) On Mon, 27 Mar 2000 15:09:42 +0400, you wrote:

>Hi everybody.
>In the where clause I compare column with the function in package that
>return the package variable. What I need to do that Oracle call this
>function once (not for all records)?
>thanks a lot.
>
>

where column_name = ( select pkg.function_name from dual )

usually does it. Another variation on that them that typically works is:

select * from t, ( select pkg.function_name PKG_VALUE from dual )  where t.column_name = pkg_value
/

(join to the function instead of comparing to it).

In Oracle8i, release 8.1 there are "deterministic" functions (see http://osi.oracle.com/~tkyte/article1/index.html for example usage) as well.

Also, in Oracle8i, release 8.1 they've added a feature called application contexts. See http://osi.oracle.com/~tkyte/article2/index.html for example uses of that. Basically, with an application context -- I can put values into a named context and then use them in queries. for example:

select * from t
where column_name = sys_context( 'Name_Of_My_context',

                                 'Variable_I_set_in_context' );

that query will be rewritten in effect as:

select * from t where column_name = :bind_variable

and the sys_context function will be evaluated once per query, not once per row.

--
http://osi.oracle.com/~tkyte/  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Mar 27 2000 - 09:51:41 CST