Re: Parameters in view

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.

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
>
Received on Mon Jul 02 2001 - 02:57:22 CDT