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
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>