| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Parameters in view
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 Sun Jul 01 2001 - 20:31:34 CDT
 |
 |