Re: Parameters in view

Tried your code and got the same result. I also checked whether putting the function into my_pkg would do anything and it didn't. So yes, sys_context is a better bet.

However (there's always that!), I'll probably stick with my package on the basis that in our system setting the context will also trigger some other actions, which can be encapsulated in the same procedure. I agree that it's not the most efficient, but the maintainability of the code is also important to me, and I reckon the trade-off is worth it. I will run some tests using the two methods, though.

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9hq40h02n03_at_drn.newsguy.com...
> In article <9hq1r3$k1u$1_at_rex.ip-plus.net>, "Ian says...
> >
> >Thomas,
> >
> >I've just run a litt test on the calling of a function:
> >
> >I added a counter to the get_myvar function to note how many times it had
> >been called, then included a second function to return the number of
 calls
> >to the get_myvar function. What happens is intereting. When I select
 the
> >function from Dual, the counter is incremented by 1, as expected.
 However
> >when I add it to a larger query (14,000 records) the function is called
> >TWICE, not once per row. The result is that I can still wrap the context
> >management in my own package, while keeping the efficiency of the calls.
> >
> >I haven't found the details in the manual, but my guess is that the
 second
> >call would check for differences before binding the result to the view.
> >
> >Any ideas?
> >
>
> DUAL is magical -- its special.
>
> It all depends on how the optimizer does the plan. Consider:
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> create or replace package my_pkg
> 2 as
> 3 cnt number;
> 4 end;
> 5 /
> Package created.
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> create or replace function foo return
 number
> 2 as
> 3 begin
> 4 my_pkg.cnt := my_pkg.cnt + 1;
> 5
> 6 return 0;
> 7 end;
> 8 /
> Function created.
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> exec my_pkg.cnt := 0
> PL/SQL procedure successfully completed.
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> select count(*) from all_objects where
 object_id
> = foo;
>
> COUNT(*)
> ----------
> 0
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> exec dbms_output.put_line( my_pkg.cnt );
> 2
>
> PL/SQL procedure successfully completed.
>
>
> There is used the index so Oracle evaluated the function AND THEN went to
 the
> index. However:
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> exec my_pkg.cnt := 0
>
> PL/SQL procedure successfully completed.
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> select count(*) from all_objects where
> object_id+0 = foo;
>
> COUNT(*)
> ----------
> 0
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> exec dbms_output.put_line( my_pkg.cnt );
> 27759
>
> PL/SQL procedure successfully completed.
>
> ops$tkyte_at_ORA817.US.ORACLE.COM>
>
>
> There is did it once per row in the query. As I said "it might" -- i
 would not
> suggest using this approach in Oracle8i and up as it totally depends on
 the
> query plan as to how many times foo will be called.
>
> A trick that "usually" works is:
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> exec my_pkg.cnt := 0
>
> PL/SQL procedure successfully completed.
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> select count(*) from all_objects where
> object_id+0 = (select foo+rownum-1 from dual );
>
> COUNT(*)
> ----------
> 0
>
> ops$tkyte_at_ORA817.US.ORACLE.COM> exec dbms_output.put_line( my_pkg.cnt );
> 1
>
> PL/SQL procedure successfully completed.
>
>
> there I used rownum in the subquery which should force the subquery to be
> resolved off to the side in a temporary table, avoiding the call to FOO
 every
> time.
>
> Again, sys_context would be better.
>
>
> >
> >"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_at_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
>
Received on Tue Jul 03 2001 - 02:52:24 CDT