Thanks for the question, rama.

Asked: October 26, 2002 - 9:10 am UTC

Last updated: May 12, 2006 - 9:22 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

tom,

thanks for the opprtunity to post a question.

My question may look stupid, but I would like you to shed some light on how to achieve this.

I need to create a view. I have a working query. But, this view needs to be parametrised so that, when called with parameters, it would use them in the underlying where condition to get the results.

Like this.

Create view abc(dno number)
as
select * from emp where deptno = dno;

Now, I should be able to call this as

select * from abc(10).

To my knowledge, not possible with view. but, is there any work around where I can select ALL the columns from the view, but can pass parameters, so that they would be used for the query ?

thanks

and Tom said...

you need to use a ref cursor and stored procedure to accomplish this. there is no such thing as "parameters to a view" or any sql query really.

see
</code> http://asktom.oracle.com/~tkyte/ResultSets/index.html <code>

for howto use ref cursors quickly.

Rating

(12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Parametrised view !

Rama, October 26, 2002 - 2:22 pm UTC

Thanks ! I knew it is not possible with views.. I was looking for a work around. I will try that and let you know.

What is the problem using Where condition

Reviewer, October 27, 2002 - 1:55 am UTC

If you are creating a view

Create view viewname as select * from table

always u can use select * From viewname where colname=your poarameter value. for this why u want to go for ref cursor and all....

Tom from your book ...

Sudhir, October 27, 2002 - 10:16 am UTC

I have some code from your book which does this, doesn't it?

create or replace view emp_view as
select * from emp where deptno = sys_context('userenv','client_info');

exec :dno:=10;
exec dbms_application_info.set_client_info(:dno);
select * from emp_view;

would that not work?

October 27, 2002 - 11:25 am UTC

Yes, that is what "i" call a parameterized view. however, they question asker wanted to:

select * from abc(10).

call the view like a procedure with parameters. The closest thing to that is "ref cursors" in a real procedure.

My Approach

Michael, November 04, 2002 - 4:40 am UTC

I tried the following to get a "Parametrised view":

create or replace type emp_type
as object (
empno number(4),
ename varchar2(10)
)
/

create or replace type emp_type_table as table of emp_type;
/

create or replace function emp_of_dept(p_deptno in number)
return emp_type_table
pipelined as
l_emp_type emp_type;
begin
for l_myrow in (select empno, ename from emp
where deptno = p_deptno) loop
l_emp_type := emp_type(l_myrow.empno, l_myrow.ename);
pipe row (l_emp_type);
end loop;
return;
end emp_of_dept;
/

select *
from table(emp_of_dept(10));

EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER

select *
from table(emp_of_dept(30));

EMPNO ENAME
---------- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES

Tom - what do you think about this?

November 04, 2002 - 8:29 am UTC

I think it is alot more code then:

create or replace
procedure foo( p_deptno in number, p_result_set out sys_refcursor )
as
begin
open p_result_set for
select empno, ename from emp where deptno = p_deptno;
end;

and having the client (which is doing Oracle stuff anyway) issue

begin foo( :n, :m ); end;

It'll be less performant as well.

Neat "trick" but not best practice.

A reader, November 04, 2002 - 8:50 pm UTC

Tom or Michael..

In your function you have used "pipelined" & "piperow"

can you please explain what is that..

Thanks.

/********
create or replace function emp_of_dept(p_deptno in number)
return emp_type_table
pipelined as
l_emp_type emp_type;
begin
for l_myrow in (select empno, ename from emp
where deptno = p_deptno) loop
l_emp_type := emp_type(l_myrow.empno, l_myrow.ename);
pipe row (l_emp_type);
end loop;
return;
end emp_of_dept;

***********/

November 05, 2002 - 9:19 am UTC

search for

pipelined

on this site to see many examples and details of this new Oracle9i feature that allows a plsql function to act just like a database table.

Read the documentation

Michael, November 05, 2002 - 3:08 am UTC

Start here:
</code> http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/08_subs.htm#19677 <code>

Performance of Ref Cursor vs. Pipelined Function

Michael, November 05, 2002 - 7:16 am UTC

Tom, you are right when you write:
"It'll be less performant as well."

I made a test with both things and the Ref Cursor is 8 - 10 times faster than "my" pipelined function idea.

So, i will use the Ref Cursors where possible.

Calling parameterised views from Forms

Carmen Burn, June 10, 2003 - 8:32 am UTC

Hi,

I set about using a parameterised view and piped function, where the view is called from Forms9i in a bast table block. However, how do I go about calling the following :
exec dbms_application_info.set_client_info(:variabl)
from forms to pass a parameter to the view? I tried forms_ddl, but that had no effect.

many thanks,
Carmen

June 10, 2003 - 9:16 am UTC

in forms, you just naturally call plsql?

begin
dbms_application_info.set_client_info( :block.item );
end;

for example.

Carmen, June 11, 2003 - 10:19 am UTC

Hi, now why didn't I think to try that? It worked fine.
Thanks.

Dave Thompson, May 03, 2006 - 11:06 am UTC

Using the methid described above:

create or replace view emp_view as
select * from emp where deptno = sys_context('userenv','client_info');

exec :dno:=10;
exec dbms_application_info.set_client_info(:dno);
select * from emp_view;

How could you incorporate more than one predicate into the view?

Thanks.

May 03, 2006 - 1:16 pm UTC

if you want more than on sys_context value, you would use an application context:


ops$tkyte@ORA10GR2> create or replace context my_ctx using my_proc
  2  /

Context created.

ops$tkyte@ORA10GR2> create or replace procedure my_proc( p_a in number, p_b in date, p_c in varchar2 )
  2  as
  3  begin
  4          dbms_session.set_context( 'my_ctx', 'a', to_char(p_a) );
  5          dbms_session.set_context( 'my_ctx', 'b', to_char(p_b,'yyyymmddhh24miss') );
  6          dbms_session.set_context( 'my_ctx', 'c', p_c );
  7  end;
  8  /

Procedure created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace view my_view
  2  as
  3  select sys_context( 'my_ctx', 'a' ) a,
  4         sys_context( 'my_ctx', 'b' ) b,
  5         sys_context( 'my_ctx', 'c' ) c
  6    from dual;

View created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec my_proc( 42, sysdate, 'hello world' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> select * from my_view;

A          B               C
---------- --------------- ---------------
42         20060503131617  hello world

Dave Thompson, May 04, 2006 - 5:49 am UTC

Thanks Tom, very useful stuff.

Dave Thompson, May 12, 2006 - 11:29 am UTC

Hi Tom,

I have created a parameterised view that has the predicate:

AND TRUNC ( JEH.DATE_UPDATED ) >= sys_context( 'my_ctx', 'p1' )
AND TRUNC ( JEH.DATE_UPDATED ) < sys_context( 'my_ctx', 'p2' )

This is the only predicate applied to the table JEH.

How will the optimiser estimate the row cardinality in this example?

May 12, 2006 - 9:22 pm UTC

ouch - that predicate totally hurts my head.

compare a date to a string.
use trunc on the date.

ouch, ouch, ouch.  using default date formats too. ouch.

believe you MEANT to code:

and date_updated >= TO_DATE( sys_context('my_ctx','p1'), 'DD-MON-YYYY' )
and date_updated  < TO_DATE( sys_context( 'my_ctx','p2'), 'DD-MON-YYYY' )

I'm assuming no time on the sys_context stuff.

if trunc(date) >= SOME_DAY then date >= SOME_DAY so trunc() is not needed.

if SOME_DAY is a "day" (with no time) then....
if trunc(date) < SOME_DAY - date < SOME_DAY as well... since SOME_DAY is the smallest "time" on that day already.... so date must have been before it "day wise"


It would use "a guess" (think 5%'ish)


ops$tkyte@ORA10GR2> /*
ops$tkyte@ORA10GR2> drop table t;
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t as select * from all_objects;
ops$tkyte@ORA10GR2> create index t_idx on t(last_ddl_time);
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt => 'for all indexed columns size 254' );
ops$tkyte@ORA10GR2> */
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select num_rows, num_rows*0.05 from user_tables where table_name = 'T';

  NUM_ROWS NUM_ROWS*0.05
---------- -------------
     50190        2509.5

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select * from t where last_ddl_time >= to_date( '01-jan-2005', 'dd-mon-yyyy' );

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 50190 |  4558K|   228   (4)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    | 50190 |  4558K|   228   (4)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("LAST_DDL_TIME">=TO_DATE('2005-01-01 00:00:00',
              'yyyy-mm-dd hh24:mi:ss'))

ops$tkyte@ORA10GR2> select * from t where last_ddl_time >= sys_context( 'my_ctx', 'p1' );

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |  2510 |   227K|    18   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |  2510 |   227K|    18   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |   452 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("LAST_DDL_TIME">=SYS_CONTEXT('my_ctx','p1'))

ops$tkyte@ORA10GR2> set autotrace off
ops$tkyte@ORA10GR2>

Breadcrumb

Question and Answer