Re: DBMS_SQL Dynamically creating views.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/09/02
Message-ID: <35ef4ebc.3886388@192.86.155.100>#1/1

A copy of this was sent to Helmut Hahn <helmut.hahn_at_bitoek.uni-bayreuth.de> (if that email address didn't require changing) On Tue, 01 Sep 1998 10:14:56 +0200, you wrote:

>Yves Roy, IPA wrote:
>
>> Hi Rodger,
>>
>> Here's a way to have a dynamic view:
>>
>> Use package variable in your where clause of the view.
>> Then before selecting the view, initialize it.
>>
>> ex. create view daily_sales as
>> select * from your_sales_tables
>> where sales_date = package_x.global_variable_date;
>>
>> Before selecting the view... in a pl/sql or a C program
>> initialyze it like package_x.global_variable_date = '1998-08-28'
>>
>> then select * from daily_sales ;
>> will give you the sales of the '1998-08-28'.
>>
>> Hope this help.
>> Yves.
>> yroy_at_sctr.net
>
>I read this thread and your solution sounds very good (also for my problem).
>The only question is about the isolation in the package variable. Is it the same value
>for a session although another session changed the variable, but it is still used by
>the first session?
>
>Helmut

Each session has its own package state, session 1 can NEVER see session 2's data in a package. This method could be called a 'parameterized view'.

To create a parameterized view, you would do the following:

create package my_params
as

    pragma restrict_references( my_params, wnds, rnds, wnps, rnps );

    function get_param1 return number;
    pragma restrict_references( get_param1, wnds, rnds, wnps );

    (other get functions here with pragmas)

    param1 number;

    (other params here with appropriate types) end;
/

create package body my_params
as

function get_param1 return number is begin return param1; end;

(other functions here)
end;
/

And then in SQL*Plus for example you would:

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

SQL> exec my_params.param1 = 7369
SQL> select * from demo;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
DEPTNO      SALES

---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------

      7369 SMITH CLERK 7903 17-DEC-80 800 20  

Note that you need to use a FUNCTION to get the value since SQL will only recognize pl/sql functions, not pl/sql variables. Also, I like to use (select F(x) from dual ) whenever possible instead of simply f(x). This will make it so the pl/sql function is evaluated ONCE per query instead of sometimes once per ROW per query (slower....)  

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 Wed Sep 02 1998 - 00:00:00 CDT