Re: Oracle Questions - Parameters

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 07 Aug 1998 17:48:09 GMT
Message-ID: <35d3367a.15587894_at_192.86.155.100>

A copy of this was sent to NeedaHoliday
(if that email address didn't require changing) On Fri, 07 Aug 1998 12:53:12 GMT, you wrote:

>Is there a way to pass parameter to a view?

Yes, in a fashion....

We can use packages to store the parameters and functions in the package to get those values to SQL.

For example:

SQL> create or replace package view_params   2 as

  3          function get_param return varchar2;
  4          pragma restrict_references( get_param, wnds, rnds, wnps );
  5  
  5          procedure set_param( p_value in varchar2 );

  6
  6
  6 pragma restrict_references( view_params, wnds, rnds, wnps, rnps );   7 end;
  8 /
Package created.

SQL> create or replace package body view_params   2 as
  3 g_value varchar2(255);
  4 function get_param return varchar2
  5 is
  6 begin
  7 return g_value;
  8 end;
  9
  9 procedure set_param( p_value in varchar2 )  10 is
 11 begin
 12 g_value := p_value;
 13 end;
 14
 14 end;
 15 /
Package body created.

So, now we have a package to get/set parameter values for our view. Now we create the view as such:

SQL> create or replace view emp_view
  2 as
  3 select *
  4 from emp, (select view_params.get_param Param from dual)   5 where ename like Param
  6 /
View created.

I use a cartesian product in this case to make is so that the pl/sql function gets called ONCE per query instead of once per row per query.... If I used "where ename = param" instead of like, I could have coded:

   select * from emp where ename = ( select view_params.get_param from dual )

instead of joining...

Now we run it:

SQL> exec view_params.set_param( '%A%' ); PL/SQL procedure successfully completed.

SQL> select ename, empno from emp_view;

ENAME EMPNO
---------- ----------

ALLEN            7499
WARD             7521
MARTIN           7654
BLAKE            7698
CLARK            7782
ADAMS            7876
JAMES            7900

7 rows selected.

SQL> exec view_params.set_param( '%K%' ); PL/SQL procedure successfully completed.

SQL> select ename, empno from emp_view;

ENAME EMPNO
---------- ----------

BLAKE            7698
CLARK            7782
KING             7839



 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Aug 07 1998 - 19:48:09 CEST