parameterized view

From: <suisum_at_ecn.ab.ca>
Date: 3 Jan 99 20:54:16 GMT
Message-ID: <368fd8f8.0@ecn.ab.ca>

Hi Thomas:

I followed your example for parameterized view in url:   http://www.dejanews.com/getdoc.xp?AN=363868651

But I have few questions, please help.

1. I don't know the purpose of the pragma restrict_refereneces. Please explain.
2. I don't know how the 'exec my_params.g_param1 := 7369' statement can affect the query for the demo view. Will this parameter will last for the entire session?
3. Please post the table definition for the sample table T used in url:

  http://www.dejanews.com/getdoc.xp?AN=373620126

Thanks a lot,

SQL> create or replace package my_params   2 as
  3
  4 pragma restrict_references( my_params, wnds, rnds, wnps, rnps );   5

  6      function get_param1 return number;
  7      pragma restrict_references( get_param1, wnds, rnds, wnps );
  8  
  9      g_param1 number;

 10
 11 end;
 12 /

Package created.

SQL> create or replace package body my_params   2 as
  3 function get_param1 return number is   4 begin
  5 return g_param1;
  6 end;
  7 end;
  8 /

Package body created.

SQL> create or replace view demo as select * from emp where empno = ( select
  2 my_params.get_param1 from dual );

View created.

SQL> exec my_params.g_param1 := 7369
PL/SQL procedure successfully completed.

SQL> select * from demo;

    EMPNO ENAME JOB MGR HIREDATE SAL --------- ---------- --------- --------- --------- --------- -

     7369 SMITH CLERK 7902 17-DEC-80 800 Received on Sun Jan 03 1999 - 14:54:16 CST