| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: parametrized Views or use of PL/SQL procedures in queries
A copy of this was sent to "Michael Dahlinger" <M.Dahlinger_at_gsi.de>
(if that email address didn't require changing)
On 18 Jun 1998 09:31:10 GMT, you wrote:
>Hello,
>I am trying to implemet a complex query over many tables, which involves
>also a query, wher a parameter must be given at the beginning (the
>requested version).
>
>For testing purposes, I designed my queries with MS Acess and Oracle
>Browser. In both applications I have the possibility to define a query
>dependend of a parameter. In MS Access I can use this query later in other
>queries. This is exactly what I want, but then the query runs in my client
>application.
>Due to the complexity of the query, the query should run in the database
>server, however.
>
>How can I realize this in Oracle. I would like to create a VIEW, which
>selects other tables and views and *parametrized queries*. I am aware of
>the fact that I can write a Procedure which returns a Cursor, but how can I
>use the results of this proceduer in subsequent VIEWS?
>
>Any hints would be appreciated !
>
>My best regards
>Michael Dahlinger
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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
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 Thu Jun 18 1998 - 08:55:33 CDT
 |
 |