Re: Parameters in view

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.

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
>
Received on Mon Jul 02 2001 - 11:16:34 CDT