Do you also know how i read the parameters entered in the webdb page? I
think if I use it as you described, oracle webdb isn't asking for the
parameters anymore. How do i do that?
"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
>
Received on Mon Jul 02 2001 - 05:42:26 CDT
