Re: Parameters in view

In article <9hp9ge$fg7$1_at_rex.ip-plus.net>, "Ian says...
>
>Another way is to put all your context variables into a package with
>procedures to set the variables and a function to return it. The advantage
>of this is if there is anything you need to do following the setting of the
>variable you can hide it from the calling program. For example you can
>execute a procedure with will insert a result set into a temporary table and
>then browse on that.
>

You can do this, but beware -- Oracle might invoke your function ONCE PER ROW of the query -- not once per query.

SYS_CONTEXT and an application context would be infinitely preferable to this method and much much more performant. SYS_CONTEXT is treated like a bind variable and invoked once per query -- not once per row per query.

>Here's the code:
>/* Begin create Package */
>
>CREATE
>PACKAGE tpms.context_package
>IS
> PROCEDURE set_myvar (p_myvar IN NUMBER);
>
> FUNCTION get_myvar
> RETURN NUMBER;
>
> PRAGMA RESTRICT_REFERENCES (get_myvar, WNDS, WNPS);
>END;
>/
>
>CREATE
>PACKAGE BODY tpms.context_package
>IS
> v_myvar NUMBER;
>
> PROCEDURE set_myvar (p_myvar IN NUMBER)
> IS
> BEGIN
> v_myvar := p_myvar;
> END;
>
> FUNCTION get_myvar
> RETURN NUMBER
> IS
> BEGIN
> RETURN v_myvar;
> END;
>END;
>/
>
>/* End create Package */
>
>/* Begin set Variable */
>
>BEGIN
> context_package.set_myvar (1);
> COMMIT;
>END;
>
>/* End set Variable */
>
>/* Begin get Variable */
>
>SELECT context_package.get_myvar
>FROM dual
>
>/* End get Variable */
>
>
>"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
>news:9hoitm0486_at_drn.newsguy.com...
>> In article <fDN%6.149236$qM3.18028774_at_news.soneraplaza.nl>, "Julian
 says...
>> >
>> >Hi,
>> >
>> >I am having a big problem with Oracle. We use WebDB in our company to
 make
>> >reports. Now the queries in WebDB can't be that large, so I created a
 view
>> >for a big query. Now the query takes way too long to execute, because a
 view
>> >fetches its results first and then webdb filters the date (with
 parameters).
>> >Because it takes too long webdb generetes a time-out and customers don't
 see
>> >results.
>> >
>> >If I use the source of the view as a query and give as a parameter for
>> >example customer_id = 2 than the query is very fast. It would be nice, or
>> >essential to have parameters within the view itself. But is that
 possible?
>> >Or are there any other ways to do this?
>> >
>> >Please help!!!!
>> >
>> >Julian Kooiker
>> >
>> >
>>
>>
>> Sure, depends in your version, but I'll cover a couple of ways....
>>
>>
>> First and easiest for a single parameter is
>> dbms_application_info.set_client_info. Consider:
>>
>> scott_at_TKYTE816> create or replace view emp_v
>> 2 as
>> 3 select ename, empno
>> 4 from emp
>> 5 where ename like userenv( 'client_info' )
>> 6 /
>>
>> View created.
>>
>> scott_at_TKYTE816> select * from emp_v;
>> no rows selected
>>
>> scott_at_TKYTE816> exec dbms_application_info.set_client_info( 'K%' )
>> PL/SQL procedure successfully completed.
>>
>> scott_at_TKYTE816> select * from emp_v;
>>
>> ENAME EMPNO
>> ---------- ----------
>> KING 7839
>>
>>
>> userenv('client_info') returns whatever you stuffed into it via
 set_client_info.
>> In a before page or report plsql block, you can set the parameter (using
 substr
>> on userenv, you could set more then one).
>>
>> A more sophisticated method is an APPLICATION CONTEXT, this is 8i or up
 (no one
>> EVER seems to post their version, never):
>>
>> scott_at_TKYTE816> create or replace context my_context using my_procedure
>> 2 /
>> Context created.
>>
>> scott_at_TKYTE816>
>> scott_at_TKYTE816> create or replace procedure my_procedure( p_name in
 varchar2,
>> p_val in varchar2 )
>> 2 as
>> 3 begin
>> 4 dbms_session.set_context( 'my_context', p_name, p_val );
>> 5 end;
>> 6 /
>>
>> Procedure created.
>>
>> scott_at_TKYTE816> create or replace view emp_v
>> 2 as
>> 3 select ename, empno
>> 4 from emp
>> 5 where ename like sys_context( 'my_context', 'ename_like' )
>> 6 OR empno = to_number( sys_context( 'my_context', 'empno' ) )
>> 7 /
>>
>> View created.
>>
>> scott_at_TKYTE816> select * from emp_v;
>> no rows selected
>>
>> scott_at_TKYTE816> exec my_procedure( 'empno', 7654 )
>>
>> PL/SQL procedure successfully completed.
>>
>> scott_at_TKYTE816> select * from emp_v;
>>
>> ENAME EMPNO
>> ---------- ----------
>> MARTIN 7654
>>
>> scott_at_TKYTE816> exec my_procedure( 'empno', null )
>>
>> PL/SQL procedure successfully completed.
>>
>> scott_at_TKYTE816> exec my_procedure( 'ename_like', 'A%' )
>>
>> PL/SQL procedure successfully completed.
>>
>> scott_at_TKYTE816> select * from emp_v;
>>
>> ENAME EMPNO
>> ---------- ----------
>> ADAMS 7876
>> ALLEN 7499
>>
>> --
>> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
>> http://www.amazon.com/exec/obidos/ASIN/1861004826/
>> Opinions are mine and do not necessarily reflect those of Oracle Corp
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp