Re: Parameters in view

In article <D8107.2120$Jp5.36060_at_news1.oke.nextra.no>, "Frank" says...
>
>Hi!
>
>Could it decrease overhead if you refered to a package public variable
>instead of a function?
>Instead of calling the function get_myvar, refer to WHERE .....=
>tpms.context_package.myvar
>
>when you ley myvar be specified in the spec part of the package.
>

no, you cannot refer to packaged variables in normal SQL (that is not in plsql - just keyed in at sqlplus for example). you can only refer to a function.

>Frank
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:9hptqu01ph9_at_drn.newsguy.com...
>> 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
>>
>
>

--
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