Skip to Main Content
  • Questions
  • Parameterized View & Truncation of Table

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Puneet.

Asked: February 13, 2001 - 6:26 am UTC

Last updated: July 11, 2012 - 4:56 pm UTC

Version: 8.1.5.0.0

Viewed 10K+ times! This question is

You Asked

Dear Tom
Hi!!!
My problems...
1)Why does'nt Oracle permits to create a Parameterized View in the lines of a Parameterized Cursor. I think there should not be any problem of creating a parameterized view because in case of a view only its definition is stored and only when that view is accessed, data is populated in that view.

Eg:- CREATE VIEW myview (parameter <datatype>)
AS SELECT * FROM <table_name> WHERE <table_name.column_name> = parameter.

The above view can be accessed like this,
select * from myview (parameter value);

I know there will be some problem with this feature because now a view definition resides in the shared pool, but in the above mentioned case it has to reside in the program area of each user.
But if this feature can be made available, then it would be of great help.

2)I think there is a bug in Oracle about Truncation of a table.
I tried to truncate a master table on which there are foreign key links of other tables. Naturally error occurred and the error message is "ora-02266 unique/primary keys in the table referenced by enabled foreign keys".
Then I truncated the dependent tables and again tried to truncate the master table but the same error occurred, the error message is "ora-02266 unique/primary keys in the table referenced by enabled foreign keys".
I am hundred percent sure that there is no foreign key violation in this case. Then I deleted records from the master table and it executed successfully. I am really baffled by this weird behaviour of the Truncation command.

3) It has been noticed that even when a DDL command executed on a table is unsuccessful, data gets commited in that table. Why does this happen?

and Tom said...

1) we do, it works like this:

create view myview
as select * from t where x = userenv('client_info')
/


and then:

SQL> exec dbms_application_info.set_client_info('my_data')
SQL> select * from myview;

You can also use dbms_session.set_context in 8i and up to greatly increase the namespace/number of parameters you can pass and reference via the SYS_CONTEXT() function.


2) that is not a bug -- it is the way it is designed to work. You cannot truncate a table upon which there are ACTIVE foreign keys referencing the table. You could have disabled the foreign keys, trunced the table and then reenabled them.

The truncate checks no constraints, fires no triggers, etc. It just wipes out the table. It has no way of knowing (and doesn't really care) that the child tables are empty -- there could be uncomitted rows in them it CANNOT see. If we let you trunc a table with child foreign keys enabled, you would end up with orphans at some point.

next time, just disable the foreign keys, trunc and enabled them again.

3) DDL is executed like this:

commit;
ddl
commit or rollback;

there is a commit right before and after. Consider DDL to be a commit. If that is not desired, use an automous transaction and execute the DDL in a subtransaction. See
</code> http://asktom.oracle.com/~tkyte/autonomous/index.html <code>
for details.

Rating

  (46 ratings)

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

Comments

Parameterized view

A reader, October 03, 2002 - 4:07 pm UTC

Tom,
Would using such a view benefit performance in any manner? Can you please explain.

regards

Tom Kyte
October 03, 2002 - 6:47 pm UTC

It lets you push a predicate down into the view which can increase performance in some cases, yes.

That would be one reason for using the technique.

SYS_CONTEXT and DB_LINK

Robert, December 19, 2002 - 8:02 pm UTC

Tom,
I had a param. view that uses SYS_CONTEXT like this :

SELECT .....
FROM LMSD75 <--- BIG TABLE
WHERE Call_Date BETWEEN To_Date(substr(SYS_CONTEXT ('userenv','client_info'),5,11),'dd-mon-yyyy')
......

worked great until DBA moved the big table to another
database and created db-link and synonym for this big table....that blew my app....

this WAS the plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=3 Bytes=105)
1 0 SORT (GROUP BY) (Cost=11 Card=3 Bytes=105)
2 1 FILTER
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'LMSD75' (Cost=6 Card=3 Bytes=105)
4 3 INDEX (RANGE SCAN) OF 'CL_SCR_CLDT_LMSD75INDX' (NON-UNIQUE) (Cost=4 Card =3)

this is the PLAN now:
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE

1 0
SORT (GROUP BY)

2 1
FILTER

3 2
REMOTE*
PUB_LMS1_TO_CORP.WORLD

3
SERIAL_FROM_REMOTE
SELECT "CALL_DATE","SCRIPT","CLIENT","RESPONSE" FROM "LMS"."LMS_D75" "A1" WHERE
"SCRIPT"='YD2'

============

So is it a fact that because the table was re-located to
another db, index and SYSCONTEXT no longer works together ?

Thanks




Tom Kyte
December 20, 2002 - 7:25 am UTC

they work -- what didn't work?

And why wouldn't you expect huge changes in response times by taking a really big table and simply "moving it".

Another question: when you did this in your test environment, not in production, what were the results there?

SYS_CONTEXT and DB_Link

Robert, December 20, 2002 - 10:49 am UTC

>> they work -- what didn't work?
>>And why wouldn't you expect huge changes in response times by taking a really big table and simply "moving it".

Well, the server runs the SYS_CONTEXT SQL I showed above , didn't complain...But it's apparently
full-scanning the 75-mil-row table, I never got the result
but the hour glass...

When I replaced the SYS_CONTEXT call with actual values
the query ran just fine.

>> Another question: when you did this in your test
>> environment, not in production, what were the results there?

DBA moved the table in production only and did not tell the world so I was totally baffled - Good in dev , hour glass in production - until I trace the sql and the plan does not show index access as you can see...

So Tom, can you confirm this (CBO) :
select ... from emp@RemoteDB where empno = sys_context(...)
(where sys_context(...) resolves to '12345' in the current session)
is NOT the same as
select ... from emp@RemoteDB where empno = '12345'

that only the 2nd will do a index scan ?

Also do you mean that one should expect "huge changes" in response time - either way - when a big table is moved ?

Thanks






Tom Kyte
December 20, 2002 - 11:12 am UTC

It worked -- it just ran slower.

That DBA should be fired, or at least taken out of control -- put in a subordinate position with someone in charge of them for a while. "Oh, I think I'll just move this table today" -- arg...

the sys_context is local to the session. it isn't visible on the remote site. the table comes back and then the predicate is applied.

In this case -- if the table must reside on the other system -- you will want to use a traditional BIND VARIABLE

select ... from emp@RemoteDB where empno = :N

that'll work as before. If you cannot bind, you can always use cursor_sharing=force / cursor_sharing = exact before/after the statement to avoid trashing the shared pool.

You should expect huge (possible) changes in response times whenever you make a fundemental change (like moving a huge table).... either way. for example, if the dba moves this table back -- it'll be like setting "fast=true".

An alternative.

Kashif, October 22, 2003 - 11:07 am UTC

Hi Tom,

I had a similar problem to Robert's problem above where I needed to select data from a remote database view, but needed an additional predicate evaluated before returning the results.

As an alternate to the solution you mentioned above, I set the context on the REMOTE site, and then executed the remote view in the local DB. For example:

REMOTE DB:

test_schema@REMOTEDB> create view myview
2 as select * from dual
3 where sys_context ('userenv', 'client_info') = 1
4 /

View created.

test_schema@REMOTEDB> grant select on myview to public
2 /

Grant succeeded.

On LOCAL DB:

kash@DEV> exec dbms_application_info.set_client_info@REMOTEDB(1)

PL/SQL procedure successfully completed.

kash@DEV> select DUMMY from test_schema.myview@REMOTEDB
2 /

DUMMY
-----
X

Eventually I didn't need to do this since the requirements changed, but I was wondering if the method I outlined above is a viable alternative to your solution of using bind variables on the local DB, and what the pros and cons might be. Thanks in advance.

Kashif

Tom Kyte
October 22, 2003 - 6:04 pm UTC

it is viable but a bind would require less round trips

dbms_application_info.set_client_info

A reader, May 12, 2004 - 6:10 pm UTC

hi tom
the above procedure (and the dbms_session.set_context()
both take only varchar2 values - it seems. Also
they truncate the value at 64 characters as per
the docs.

Dont you think these limit the use of parameterized
views quite a bit?

Thank you!


Tom Kyte
May 13, 2004 - 9:44 am UTC

set client info is limited to 64 --
set context is 256.

It is not the input type -- but the return type that you should be concerned with and for that you simply use to_number() and to_date when appropriate.


so, dbms_session.set_context( 'myctx','dt', to_char(sysdate,'yyyymmddhh24miss') )

to set a date and to_date( sys_context( 'myctx','dt'), 'yyyymmddhh24miss' ) to reference it.

thanx Tom!

A reader, May 13, 2004 - 12:20 pm UTC

Just being a devil's advocate - I am thinking of using
this feature...
An issue that bugs me is that a parameterized
view can be effectively accessed only by one package (the
one that has the privilege to set the context in which
bind variables are stored.) That bothers me since it
makes the view less "reusable" and very package specific.

Am I missing something here do you consider
that as a limitation as well?

Thanx!



Tom Kyte
May 13, 2004 - 3:04 pm UTC

no, the package must be used to set the value but everyone accessing the view will see the values set therein.

FGAC would be unusable otherwise!


so, package sets value.
value is visible to everyone -- even just sqlplus querying the view

for the review just above

A reader, May 13, 2004 - 12:25 pm UTC

On second thoughts my comment is wrong since
you need to just have a procedure in a package
that can set the context value. Other packages
can invoke this packaged procedure and
then use the view, correct?


Tom Kyte
May 13, 2004 - 3:04 pm UTC

correct, yes.

considerations while creating a context

A reader, July 20, 2004 - 2:13 pm UTC

Hi tom
are there any considerations for creating a context?
say an application has different modules - would
you have any thoughts on creating multiple context
objects per module or just one context for the whole app?
I want to create a context for using parameterized
views for my app and was wondering whether I should
create a new context for this....userenv as I understand
is not depcrecated...

Thank you for a wonderful site!





Tom Kyte
July 20, 2004 - 8:43 pm UTC

consider a context like a global variable. how many people do you want stomping on your globals? I would be miserly with my contexts - just to protect myself.

thanx!

A reader, July 22, 2004 - 9:09 pm UTC

"consider a context like a global variable. how many people do you want stomping
on your globals? I would be miserly with my contexts - just to protect myself.
"
do you mean that you would hesitate in creating
multiple contexts per module or in creating
one global context for all modules to share?

Also, I ran into one limitation with parameterized
views..

you can not do something like (pseudo code)
select * from
parameterized_view1( param1) , parameterized_view1( param2);

since at a time the sys_context can take only one
value... Do you have any suggestions?

Thanx!

Tom Kyte
July 23, 2004 - 8:26 am UTC

I would use a context by as little code as possible.


and I would not call that a "limitation" but an obvious outcome of this implementation? You would need two views.

thank you!

A reader, July 23, 2004 - 12:08 pm UTC

"I would use a context by as little code as possible."

I guess that translates to "one context should be used
by as little code as possible", correct? In which case in the above scenario you would create multiple contexts
(per module)?

"and I would not call that a "limitation" but an obvious outcome of this
implementation? You would need two views.
"
well, that would be like creating two procedures
just because their parameters are different, no?

thank you!


Tom Kyte
July 23, 2004 - 4:33 pm UTC

backwards in response.

it is an obvious outcome of this implementation -- i mean -- how could it *be any other way* given the implementation. You are in effect pushing the parameters onto the call stack and having both procedures use the same call stack. that is the implementation.


and yes, you would isolate as little code to use a single context as possible -- treat it like it is -- a global. treat it with fear, caution, and trepidation. Use with care.

thank you

A reader, July 23, 2004 - 4:36 pm UTC

I think I know where i am getting confused..
"and I would not call that a "limitation" but an obvious outcome of this implementation"
When you said implementation I thought you meant
my requirement itself (the fact that i need to join
same view with different parameters.) I think you
mean the implementation of parameterized view
itself...

anyways - i know now that i have to create two views
in the above case...

thanx!

Tom Kyte
July 23, 2004 - 5:19 pm UTC

correct, the implementation of the parameterized view.

parameterized views

A reader, August 13, 2004 - 1:05 pm UTC

if the value of the parameterized views is a list
of values (stored in a collection) - then I suppose
we can not use the sys_context method above? You would
directly join with the collection in such cases...If you wanted the same view to work with one value and a list of
values in a collection (the values being passed using
sys_context), what would be your solution?

thanx!

Tom Kyte
August 13, 2004 - 6:00 pm UTC

at that point, i might be thinking about using a global temporary table and just inserting the values.

(eg: a subquery)

you can only take this implementation "so far"

thanx!

A reader, August 13, 2004 - 6:35 pm UTC


parameterized views

A reader, September 22, 2004 - 8:59 pm UTC

I have used parameterized views recently and wanted
to post my opinion on what I learnt.
Following are the advantages of parameterized views.

1. The biggest advantage is that it allows you to
join the view with other tables/views thus
allowing you to write set based code. An approach
using procedure that returns a ref cursor does not
allow this and I found that to be a major limitation.
This of course assumes that you want to write the
view with supplying the values at run time (i.e.
write a parameterized view.)
2. In corner cases, it may help performance as you
mentioned above (in rare cases when optimizer is
not able to merge.)

Cons:
1. It is a painful thing to set the context values
each time before testing. If your views were not
parameterized you can run simple selects from the
views (e.g. select * from v1) which of course
parameterized views dont allow untill you run
the context setting code.

Overall, I felt that in general, one is better off not
using them - use straight forward views. In case of
performance issues, try the param views out. But dont
start out with it in the first place if your needs
are fulfilled without them (Which would be the case
most of the time.)
Thanx for a brilliant site!



Tom Kyte
September 23, 2004 - 4:10 pm UTC

well, the cons really are just "binding in disguise :)"


but your analysis is correct, use them when you must - when you must push the predicate down manually (usually it means the answer changes -- that is why you "must")


hmm...

A reader, September 23, 2004 - 6:53 pm UTC

"well, the cons really are just "binding in disguise :)"

almost...
basically if you simplify your views then you dont
need to "bind" inside the views. You can do that
outside when you invoke the view. That is the approach
one should take I think. If then there are exceptional
cases where it helps to push predicates using parameterized
views, go for it.
Since you dont need to bind variables in simpler
views, you dont have the hassel of views that dont
work without some extra procedures being executed.


Diplomacy

Yuan, January 19, 2005 - 3:51 pm UTC

'and I would not call that a "limitation" but an obvious outcome of this
implementation? You would need two views.'

I didn't know you were so diplomatic, Tom. So any idea if there are plans to improve upon this implementation and remove this obvious outcome?

Tom Kyte
January 20, 2005 - 10:04 am UTC

tell us how you would approach it, look at the problem - understand the semantics and tell us how it might work......

that is the problem with obvious outcomes.

Parameterized View Implementation

Yuan, January 21, 2005 - 8:44 am UTC

I think the original author of this question has a fine implementation.

> Eg:- CREATE VIEW myview (parameter <datatype>)
> AS SELECT * FROM <table_name> WHERE <table_name.column_name> > = parameter.

> The above view can be accessed like this,
> select * from myview (parameter value);

My reason for wanting a parameterized view may disappear when we finally move our production environment to 9i. In 8i, certain complex queries involving views seem to confuse the optimizer and make it resolve the view first, resulting in unnecessary full table scans. To explain my point, if you were to

select * from myview where column_name >= parameter

in the original poster's example, the optimizer would use an index on column_name. Our query is a bit more complex involving joins and group by and though we provide a filter on an indexed column on myview's base table, the explain plan shows a full table scan on that base table in 8i. Explaining the identical query in a 9i environment shows that the optimizer uses the index.

Still if a scenario like this ever came up again in 9i or above where the optimizer seems to get confused, I would think an explicitly parameterized view would clear things up for it.

Tom Kyte
January 21, 2005 - 9:57 am UTC

metalink.oracle.com .... only way to get your ideas into the system.

Metalink

Yuan, March 15, 2005 - 12:50 pm UTC

I finally got my registration approved by our site admin. Where do I go to put in such a suggestion? Forums?

Tom Kyte
March 15, 2005 - 9:07 pm UTC

you open a tar with support.

restricting date range in inline view

Milind, June 15, 2005 - 4:55 pm UTC

Hi Tom,

I have these tables in Dimensional database as follows

SQL> desc ADV_RCVRY_TXN_DIM       
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ADV_RCVRY_TXN_DIM_KEY                     NOT NULL NUMBER
 SVC_SYS_IND_DIMKEY                                 NUMBER
 ADV_TXN_ID                                         NUMBER(12)
 RCVRY_TXN_ID                                       NUMBER(12)
 ADV_CNCL_TXN_ID                                    NUMBER(12)
 RCVRY_RVRSL_TXN_ID                                 NUMBER(12)
 CREATE_PROG_NAME                          NOT NULL VARCHAR2(30)
 CHG_PROG_NAME                             NOT NULL VARCHAR2(30)
 CHG_LST_DATE                              NOT NULL DATE

SQL> desc ADV_RCVRY_BRIDGE
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ADV_RCVRY_TXN_DIM_KEY                     NOT NULL NUMBER
 PARENT_ADV_RCVRY_TXN_DIM_KEY                       NUMBER
 CREATE_PROG_NAME                          NOT NULL VARCHAR2(30)
 CHG_PROG_NAME                             NOT NULL VARCHAR2(30)
 CHG_LST_DATE                              NOT NULL DATE

SQL> desc ADV_RCVRY_TXN_FACT
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ADV_RCVRY_TXN_DIM_KEY                     NOT NULL NUMBER
 TXN_EFF_DATE_DIM_KEY                      NOT NULL NUMBER
 SYS_TXN_CRT_DATE_DIM_KEY                  NOT NULL NUMBER
 LOAN_DIM_KEY                              NOT NULL NUMBER
 ADV_TYPE_DIM_KEY                                   NUMBER
 RCVRY_TYPE_DIM_KEY                                 NUMBER
 TXN_AMOUNT                                         NUMBER(15,2)
 CREATE_PROG_NAME                          NOT NULL VARCHAR2(30)
 CHG_PROG_NAME                             NOT NULL VARCHAR2(30)
 CHG_LST_DATE                              NOT NULL DATE
 POOL_DIM_KEY                                       NUMBER(10)
 LNDR_DIM_KEY                                       NUMBER(10)

SQL> desc ADVANCE_TYPE_DIM        
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 ADV_TYPE_DIM_KEY                          NOT NULL NUMBER
 ADV_TYPE                                           VARCHAR2(20)
 CREATE_PROG_NAME                          NOT NULL VARCHAR2(30)
 CHG_PROG_NAME                             NOT NULL VARCHAR2(30)
 CHG_LST_DATE                              NOT NULL DATE

SQL> desc RECOVERY_TYPE_DIM
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 RCVRY_TYPE_DIM_KEY                        NOT NULL NUMBER
 RCVRY_TYPE                                         VARCHAR2(20)
 CREATE_PROG_NAME                          NOT NULL VARCHAR2(30)
 CHG_PROG_NAME                             NOT NULL VARCHAR2(30)
 CHG_LST_DATE                              NOT NULL DATE

SQL> desc SERVICING_SYSTEM_IND_DIM
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 SVC_SYS_IND_DIMKEY                        NOT NULL NUMBER
 SVC_SYS_IND                                        VARCHAR2(2)
 SVC_SYS_DSCR                                       VARCHAR2(10)
 CREATE_PROG_NAME                          NOT NULL VARCHAR2(30)
 CHG_PROG_NAME                             NOT NULL VARCHAR2(30)
 CHG_LST_DATE                              NOT NULL DATE
 

Now its a parent child relationship .. an Advance can have many recoveries and and advance can have one to one
cancellation and recovery can have one to one calculation .. I have created a view to get the total balance and used
inline views .. now the outer view can restrict the data on a given data range but its not true for inline views
is there anyway I can rewrite the query (corelated) where I can restict the date rannge even for the data that's
in the inline views ? (I thought about using SYS_CONTEX and pass parameter to the views but this view will
be used by Business object so .. here is the query

CREATE OR REPLACE VIEW AGE_REP_VW AS
SELECT  Z.TXN_EFF_DATE,  
        Z.ADV_TXN_RVRSL_DIM_KEY ADV_RCVRY_TXN_DIM_KEY, 
        Q.SVC_SYS_IND_DIMKEY, 
        P.POOL_DIM_KEY , 
        P.LNDR_DIM_KEY, 
        P.LOAN_DIM_KEY,
        DECODE(R.ADV_TYPE,'P&I',DECODE(S.SVC_SYS_IND,'PS',(Z.TXN_AMOUNT- Z.ADV_CNCL_AMOUNT - Z.TOTAL_RECOVERY),NULL),NULL) AS PS_PI_ADVANCE_AMT, 
        DECODE(R.ADV_TYPE,'P&I',DECODE(S.SVC_SYS_IND,'MS',(Z.TXN_AMOUNT- Z.ADV_CNCL_AMOUNT - Z.TOTAL_RECOVERY),NULL),NULL) AS MS_PI_ADVANCE_AMT, 
        DECODE(R.ADV_TYPE,'T&I',DECODE(S.SVC_SYS_IND,'PS',(Z.TXN_AMOUNT- Z.ADV_CNCL_AMOUNT - Z.TOTAL_RECOVERY),NULL),NULL) AS PS_TI_ADVANCE_AMT, 
        DECODE(R.ADV_TYPE,'T&I',DECODE(S.SVC_SYS_IND,'MS',(Z.TXN_AMOUNT- Z.ADV_CNCL_AMOUNT - Z.TOTAL_RECOVERY),NULL),NULL) AS MS_TI_ADVANCE_AMT, 
        DECODE(R.ADV_TYPE,'CORP',DECODE(S.SVC_SYS_IND,'PS',(Z.TXN_AMOUNT- Z.ADV_CNCL_AMOUNT - Z.TOTAL_RECOVERY),NULL),NULL) AS PS_CORP_ADVANCE_AMT, 
        DECODE(R.ADV_TYPE,'CORP',DECODE(S.SVC_SYS_IND,'MS',(Z.TXN_AMOUNT- Z.ADV_CNCL_AMOUNT - Z.TOTAL_RECOVERY),NULL),NULL) AS MS_CORP_ADVANCE_AMT
FROM  ADVANCE_TYPE_DIM  R,
      SERVICING_SYSTEM_IND_DIM S,
      ADV_RCVRY_TXN_DIM Q,
      ADV_RCVRY_TXN_FACT P,
     (SELECT Y.ADV_TXN_RVRSL_DIM_KEY, TXN_EFF_DATE,  Y.TXN_AMOUNT , Y.ADV_CNCL_AMOUNT , SUM(Y.RCVRY_AMT - Y.RVRSL_AMT) TOTAL_RECOVERY
        FROM (SELECT  X.ADV_TXN_RVRSL_DIM_KEY, X.ADV_RCVRY_TXN_DIM_KEY,  TXN_EFF_DATE,  X.TXN_AMOUNT, 0 AS ADV_CNCL_AMOUNT, SUM(RCVRY_AMT) RCVRY_AMT, SUM(X.RVRSL_AMT) RVRSL_AMT
                FROM (SELECT  F.ADV_RCVRY_TXN_DIM_KEY ADV_TXN_RVRSL_DIM_KEY, RV.ADV_RCVRY_TXN_DIM_KEY, TRUNC(G.CALENDERDATE,'DD') TXN_EFF_DATE,  F.TXN_AMOUNT, 0 AS ADV_CNCL_AMOUNT, 0 AS RCVRY_AMT, SUM(RV.RVRSL_AMT) RVRSL_AMT
                        FROM    ADV_RCVRY_TXN_FACT F,
                                ADV_RCVRY_BRIDGE E,
                                DATE_DIM G,
                                (SELECT  D.ADV_RCVRY_TXN_DIM_KEY, D.TXN_AMOUNT RCVRY_AMT, RVRSL.RR_TXN_AMT RVRSL_AMT
                                    FROM  ADV_RCVRY_TXN_FACT D,
                                        (SELECT A.PARENT_ADV_RCVRY_TXN_DIM_KEY, RR_TXN_AMT  
                                             FROM ADV_RCVRY_BRIDGE A,    
                                                (SELECT A.ADV_RCVRY_TXN_DIM_KEY, A.TXN_AMOUNT AS RR_TXN_AMT
                                                          FROM ADV_RCVRY_TXN_FACT A,
                                                              RECOVERY_TYPE_DIM B
                                                       WHERE B.RCVRY_TYPE = 'RECOVERY REVERSAL'
                                                       AND A.RCVRY_TYPE_DIM_KEY= B.RCVRY_TYPE_DIM_KEY) RR
                                           WHERE  RR.ADV_RCVRY_TXN_DIM_KEY = A.ADV_RCVRY_TXN_DIM_KEY) RVRSL
                                       WHERE  RVRSL.PARENT_ADV_RCVRY_TXN_DIM_KEY = D.ADV_RCVRY_TXN_DIM_KEY ) RV
                          WHERE (E.PARENT_ADV_RCVRY_TXN_DIM_KEY = F.ADV_RCVRY_TXN_DIM_KEY AND   E.ADV_RCVRY_TXN_DIM_KEY = RV.ADV_RCVRY_TXN_DIM_KEY)
                        AND    F.TXN_EFF_DATE_DIM_KEY = G.DATE_DIM_KEY
                        GROUP BY F.ADV_RCVRY_TXN_DIM_KEY,RV.ADV_RCVRY_TXN_DIM_KEY, TRUNC(G.CALENDERDATE,'DD'), F.TXN_AMOUNT, 0, 0 
                        UNION
                        SELECT  F.ADV_RCVRY_TXN_DIM_KEY ADV_TXN_RVRSL_DIM_KEY, NULL AS RCVRY_TXN_DIM_KEY, TRUNC(G.CALENDERDATE,'DD') TXN_EFF_DATE, F.TXN_AMOUNT, AC.ADV_CNCL_AMOUNT,  0 AS RCVRY_AMT, 0
                        FROM    ADV_RCVRY_TXN_FACT F,
                                ADV_RCVRY_BRIDGE E,
                                DATE_DIM G,
                                (SELECT    A.ADV_RCVRY_TXN_DIM_KEY, A.TXN_EFF_DATE_DIM_KEY, A.TXN_AMOUNT, A.TXN_AMOUNT ADV_CNCL_AMOUNT
                                      FROM    ADV_RCVRY_TXN_FACT A,
                                              ADVANCE_TYPE_DIM B
                                   WHERE   B.ADV_TYPE = 'ADVANCE CANCELLATION'
                                     AND   B.ADV_TYPE_DIM_KEY = A.ADV_TYPE_DIM_KEY) AC
                        WHERE E.PARENT_ADV_RCVRY_TXN_DIM_KEY = F.ADV_RCVRY_TXN_DIM_KEY AND   E.ADV_RCVRY_TXN_DIM_KEY = AC.ADV_RCVRY_TXN_DIM_KEY
                        AND   F.TXN_EFF_DATE_DIM_KEY = G.DATE_DIM_KEY 
                        UNION
                        SELECT  F.ADV_RCVRY_TXN_DIM_KEY ADV_TXN_RVRSL_DIM_KEY, RCVRY_TXN_DIM_KEY, TRUNC(G.CALENDERDATE,'DD') TXN_EFF_DATE, F.TXN_AMOUNT, 0,  RC.RCVRY_AMT, 0
                        FROM    ADV_RCVRY_TXN_FACT F,
                                ADV_RCVRY_BRIDGE E,
                                DATE_DIM G,
                                (SELECT    A.ADV_RCVRY_TXN_DIM_KEY AS RCVRY_TXN_DIM_KEY, A.TXN_EFF_DATE_DIM_KEY, A.TXN_AMOUNT RCVRY_AMT, 0
                                       FROM    ADV_RCVRY_TXN_FACT A,
                                               RECOVERY_TYPE_DIM B
                                      WHERE   B.RCVRY_TYPE <> 'RECOVERY REVERSAL'
                                    AND     A.RCVRY_TYPE_DIM_KEY= B.RCVRY_TYPE_DIM_KEY) RC
                        WHERE E.PARENT_ADV_RCVRY_TXN_DIM_KEY = F.ADV_RCVRY_TXN_DIM_KEY AND   E.ADV_RCVRY_TXN_DIM_KEY = RC.RCVRY_TXN_DIM_KEY
                        AND   F.TXN_EFF_DATE_DIM_KEY = G.DATE_DIM_KEY) X
      GROUP BY X.ADV_TXN_RVRSL_DIM_KEY , X.ADV_RCVRY_TXN_DIM_KEY, TXN_EFF_DATE,  X.TXN_AMOUNT, 0) Y
GROUP BY ADV_TXN_RVRSL_DIM_KEY, TXN_EFF_DATE,  TXN_AMOUNT , ADV_CNCL_AMOUNT) Z
WHERE P.ADV_RCVRY_TXN_DIM_KEY = Z.ADV_TXN_RVRSL_DIM_KEY
AND   P.ADV_RCVRY_TXN_DIM_KEY= Q.ADV_RCVRY_TXN_DIM_KEY
AND   P.ADV_TYPE_DIM_KEY = R.ADV_TYPE_DIM_KEY
AND   Q.SVC_SYS_IND_DIMKEY = S.SVC_SYS_IND_DIMKEY
/
 

Parameterized views with nested queries

Sreenath, July 10, 2005 - 11:21 am UTC

This thread has been one of the most useful links in your site...
I've a doubt regarding the parameterized views
1) whether a view which is built on a set of nested queries can be parameterized or not ?
2) If yes then upto which level(in nested queries) can i use the parameter set using the "userenv" ?

Thanks in advance

Tom Kyte
July 10, 2005 - 12:04 pm UTC

1) sure
2) as many as you like

also see CREATE CONTEXT, if you have "lots to stuff in there", it'll be very useful.

How about this alternative

Asim Naveed, October 05, 2005 - 3:49 pm UTC

How about creating a parameter table along with
the view.
for e.g. to create a parameterized view
CREATE VIEW v1 AS SELECT * FROM t1 WHERE c1 = parameter.

First I will create a Single row table.
CREATE TABLE param_v1 (param1 VARCHAR2(40));
Then
CREATE VIEW v1 AS SELECT t1.* FROM t1, param_v1
WHERE t1.c1 = param_v1.param1;

When ever I want to query the view v1 I will first

DELETE FROM param_v1; -- make sure table is empty.
INSERT INTO param_v1 VALUES('MYVALUE'); -- insert parameter.

Then

SELECT * FROM v1;

(remember that param_v1 is a single record table).
After getting the results of query I can

DELETE FROM param_v1;

Tom and all of you, please comment on this.

Tom Kyte
October 05, 2005 - 8:19 pm UTC

well, if you are going to use a table, don't delete from it, use a global temporary table.

but if you have just but one parameter value row per parameter name - why wouldn't you simply use a context?

Global Temp Table was a success

Gregory Sill, October 19, 2005 - 4:14 pm UTC

This thread has been very helpful. We are implementing the use of the global temporary table as in your last suggestion so the view can be controlled within a session. (the actual view is more complicated)

CREATE GLOBAL TEMPORARY TABLE HCIUSER.GLOBALTEMP_TEST
( VAL VARCHAR2(50 BYTE))
ON COMMIT DELETE ROWS;

CREATE OR REPLACE VIEW TEST
(CODE)
AS
select "CODE" from allcodes, globaltemp_test gl
where code = gl.val;

and then the application uses the following statements to retrieve the data....

insert into hciuser.globaltemp_test (val) values ('123');
select * from hciuser.test;
commit;

Thanks for the help.


Please give a working example

Sal, March 03, 2006 - 11:19 am UTC

Tom,
I am referring to this from the begining of this thread:

create view myview
as select * from t where x = userenv('client_info')
/


and then:

SQL> exec dbms_application_info.set_client_info('my_data')
SQL> select * from myview;

You can also use dbms_session.set_context in 8i and up to greatly increase the 
namespace/number of parameters you can pass and reference via the SYS_CONTEXT() 
function.

Can you please show a working example using set_context. I will really appreciate it.

Thanks

Sal 

context vs global variable

A reader, October 24, 2007 - 8:18 am UTC

<quote>consider a context like a global variable</quote>

Thanks for the wonderful site Tom.
We have a few complex views in our application and we want to "push" a few predicates into these views. What would you suggest be the best option for this ? Session context or global variables ?
Currently our plan is to declare a package with a global variable, set the predicate to this variable and get the variable in the view. Do you think context would be more useful here or makes no difference using either one ?
Tom Kyte
October 24, 2007 - 9:22 am UTC

global variables will not work with views, so.... application context.

how would you get the global variable "into the view", views cannot see plsql package variables.

Re: context vs global variable

A reader, October 24, 2007 - 9:49 am UTC

Thanks for the quick response Tom. Did not express myself clearly there.

Ours is a web-app with java as the front-end talking to Oracle. So, right now, the plan is to create a package with "setters" & "getters". The "setter" takes an IN parameter & assigns this IN value to the package's global variable. We set the value to this "setter" in java using prepared statement. Next step would be to call the view using prepared statement which calls the "getter" inside it. The "getter" returns this global variable.

Hope I am making sense here. Thanks again for your help.
Tom Kyte
October 24, 2007 - 12:21 pm UTC

do not call plsql from sql unless you have to.

your setter - should call the set context api.
your getter - is just sys_context

do not call plsql unless you have to and you very much do not have to here.

JDBC Connection Pooling and DBMS_SESSION

Ravi, July 02, 2008 - 2:24 am UTC

Hi Tom,

Talking about the DBMS_SESSION.SET_CONTEXT I have a problem with this when working with JDBC Connection pools.

1. I have a view that reads from SYS_CONTEXT

create or replace view TableSummaryView as
select a.*,
row_number() over (partition by
a.VProduct_VName,
a.PriceListType_VName,
a.PriceGroup_VName,
a.VSalesOrg_VName,
a.VChannel_VName,
a.VPricingCurrency_VISOCode,
a.PriceRecordPriceListType_VName,
a.AttributesKey order by a.vvalidity_from, a.created desc) ViewRN
from Table a
where ParentVID in ( select * from THE (select cast(in_list(sys_context('params','epsrlist')) as mytableType) from dual));

2. But before that I set it from Java layer by calling a stored procedure which calls the DBMS_SESSION.SET_CONTEXT.

Connection connection = JDBCHelper.getConnection();
CallableStatement stmt = null;
try {
stmt = connection.prepareCall("{call setepsrlist.epsrlist(?)}");
stmt.setString(1, epsrList);
stmt.executeUpdate();
}

create package setepsrlist is
procedure epsrlist(epsrstring in varchar2);
end setepsrlist;
/

create package body setepsrlist is
procedure epsrlist(epsrstring in varchar2) is
begin
dbms_session.set_context(namespace => 'params', attribute => 'epsrlist', value => epsrstring);
end epsrlist;
end setepsrlist;
/

3. When I execute the query on the View which happens on another Connection and it does not see the value that I have set on the session, since this Connection can potentially be pointing a new Database Session.
Tom Kyte
July 06, 2008 - 7:43 pm UTC

but, I'm confused...

why would you think a context value set in Oracle session 1 would be or should be visible in Oracle session 2?

To Ravi

A reader, July 07, 2008 - 4:53 am UTC

If you are interested in Global context, please read Tom's another followup :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1578344046713#10171639368004

HDH

Parameterized View using Oracle Connection Pools

Ravi, July 07, 2008 - 8:07 pm UTC

What I meant is

a) Get a connection
b) Set the context
c) Get a connection(this can be a totally different connection)
d) get the value from the context and it will not be there.

So how can we be sure that an operation that involves multiple database JDBC connections see the value that is set.
Tom Kyte
July 07, 2008 - 9:10 pm UTC

then obviously you cannot rely on that.

why would you

grab
set
grab YET ANOTHER
then try to use

why do java programmers do things like that - to generate a page, you should just

a) grab
b) generate page
c) release

why grab/ungrab over and over and over (this happens all of the time, just to make things *really* hard and confusing)

Parameterized view doesn't work

Nassima, February 24, 2009 - 3:21 pm UTC

Hi Tom,
1. I create my view like this :
create view myview
as select * from patient where num_patient = userenv('client_info')
2. I write this in forms (in the when-new-form-instance-trigger) :
dbms_application_info.set_client_info('98785')
3. In the form, i have block based on myview
At runtime, the block listed all records in patient table.
Why ?
thanks

Tom Kyte
February 24, 2009 - 5:21 pm UTC

because you did something wrong.

do this in sqlplus

go into sqlplus
exec dbms_application_info.set_client_info('98785')
select * from myview;


see what you see, and then figure out what you did wrong in your form - perhaps you based the block on PATIENT and not MYVIEW

A reader, February 25, 2009 - 8:56 am UTC

Thank you Tom for your reply,
my block is based on myview not on patient, and in sql plus that works fine.
It seems to me that forms and the view run in 2 different sessions !
Is it possible ?

Tom Kyte
February 25, 2009 - 5:56 pm UTC

forms and view run in 2 different sessions???? what does that even mean. A view is a schema object, it does not run in a session. A forms application connects to a database - creates a session and then queries schema objects.

You have made a mistake, your form is NOT based on myview, not the myview you are describing here. It is quite simply "not possible", please go back to your form and double, triple check everything, you have made a mistake somewhere in the form and you are querying something OTHER than myview.


Remtote Table View

Frank, November 09, 2009 - 3:40 pm UTC

Excellent (and long-lived) thread.

I have a similar problem, but I'm still confused about the best answer.
Many of the above posts have different situations and I think the answer depends upon the conditions.

I have a query which needs to join on a remote, 2TB, table.
Pulling ALL these rows across means I'll be dead before the query completes so I have to limit the rows retrieved.

This "Archive" table also joins with the local table containing the most recent data.

If I test with a hardcoded view like

create or replace view PM_RCNT_ARCH_PV as
select * from PM_RECENT rcnt where rcnt.log_date between to_date('01-JAN-09') and to_date('01-MAR-09')
union all
select * from PM_ARCHIVE@remote_db arch where arch.log_date between to_date('01-JAN-09') and to_date('01-MAR-09') ;

then the query completes in a few seconds. Great!

Replacing the dates with reverences to the (local) application context never completes (as you explained above).

Somehow I need to push the date parameters to the remote system.

I tried to create a view with a bind variable but Oracle just complains saying "ORA-01027: bind variables not allowed for data definition operations".

It is ESSENTIAL that the solution support many concurrent Java application threads, so each concurrent query needs its own parameters.

The idea of using a remote table is interesting, but I don't see how I can get each query to use the correct parametes for its thread.

What is the concensus on the best options for doing this?



Tom Kyte
November 11, 2009 - 2:48 pm UTC

... Many of the above posts have different situations and I think the answer
depends upon the conditions.
...

that is true of everything :)




can you create a view on the remote system? it would do the reference to the sys_context - locally.

you would invoke dbms_application_info@remote_site to set it.

can you give that a try?


Remtote Table View

Frank, November 10, 2009 - 1:09 pm UTC

I should add that I may need to pass a fair amount of parameter data (e.g. a list of several hundred key values).

I tried the set_client_info@remotedb approach and it seems to fit the bill except for the limitation of 64 byts.
Tom Kyte
November 11, 2009 - 3:54 pm UTC

can you use a table over there to pump the values into - the remote view would use that table to constrain the returned data and it would be evaluated at the remote site (the view would be, since it lives there)

Context Variables As Formal View Parameters

Jon Blake, December 13, 2009 - 9:40 pm UTC

Hi Tom,
The use of DBMS_SESSION.SET_CONTEXT and SYS_CONTEXT to define and retrieve parameter values to be passed to a view definition at run-time is, in my opinion, a work-around for the lack of formal view parameters. This approach works at the SQL command prompt, where I can call my user-defined package to assign a context value to be passed to a view before I select from the view. But when creating a user-defined drill-down report in SQL Developer that is implemented by selecting from a view, no facility exists to assign context values before the report runs. Formal parameters on view definitions would overcome this problem.

While contexts can be used to emulate passing parameter values to views in certain situations, they are not a replacement for formal view parameters. We don't need to use contexts when passing parameters to procedures and functions. Why should we have to use contexts to pass parameters to views?

Regards,
Jon
Tom Kyte
December 14, 2009 - 9:26 am UTC

On one hand, I would say:

I guess because a view isn't a procedure or function - and hence they work differently.

On the other, I agree this would be a neat proprietary extension to SQL - the standard has no support for anything like this.

Calling Different Views within a View based on Condition ?

Ramchandra Joshi, May 06, 2010 - 9:20 am UTC

Hi Tom,

Is it possible to call two different views from within a single view based on the condition set by context ?

E.g I want to create something like :

Create or replace my_view as
when TO_DATE(SYS_CONTEXT('userenv','client_info')) = busines_date
then
select * from current_view
wehn TO_DATE(SYS_CONTEXT('userenv','client_info')) <> business_date
then
select * from Historical_view

Here the current_view and historical_view has same column structure.

Is there a way wherein we can achieve this ?

Thanks in advance ,
Ram.
Tom Kyte
May 06, 2010 - 2:38 pm UTC

what is business date?? where did it magically appear from?

conditions

A reader, May 07, 2010 - 8:41 am UTC

WITH
current10 AS (
SELECT level as lvl
  FROM DUAL
 CONNECT BY LEVEL < 11
)
,
historical20 AS (
SELECT level as lvl
  FROM DUAL
 CONNECT BY LEVEL < 21
)
select count(*)
  from (
select * from current10
where rownum = CASE WHEN SYS_CONTEXT('userenv','client_info') = '10'
                      OR SYS_CONTEXT('userenv','client_info') IS NULL
                    then rownum
                    else 0
                end
UNION ALL
select * from historical20
where rownum = CASE WHEN SYS_CONTEXT('userenv','client_info') = '20'
                      OR SYS_CONTEXT('userenv','client_info') IS NULL
                    then rownum
                    else 0
                end
)
SQL> /

            COUNT(*)
--------------------
                  30

Elapsed: 00:00:00.01
SQL> exec dbms_application_info.set_client_info('10');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> /

            COUNT(*)
--------------------
                  10

Elapsed: 00:00:00.01
SQL> exec dbms_application_info.set_client_info('20');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> /

            COUNT(*)
--------------------
                  20

Tom Kyte
May 07, 2010 - 8:53 am UTC

why do it that convoluted way?

select * from t1 where (nvl(sys_context(...),'10') = '10')
union all
select * from t2 where (nvl(sys_context(...),'20') = '20')

you'll find that a tad more efficient as well as the where clause could be used to actually prevent us from having to scan t1 or t2 - whereas that case could not.

de-convoluted and standardized?

A reader, May 07, 2010 - 4:26 pm UTC

WITH
current10 AS (
SELECT level as lvl
FROM DUAL
CONNECT BY LEVEL < 11
)
,
historical20 AS (
SELECT level as lvl
FROM DUAL
CONNECT BY LEVEL < 21
)
select count(*)
from (
select * from current10
where COALESCE(SYS_CONTEXT('userenv','client_info'),'10') = '10'
UNION ALL
select * from historical20
where COALESCE(SYS_CONTEXT('userenv','client_info'),'20') = '20'
)
Tom Kyte
May 08, 2010 - 7:33 am UTC

yes, consider (used dummy to force IO against dual)

ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace view v( cnt )
ops$tkyte%ORA11GR2> as
ops$tkyte%ORA11GR2> WITH
ops$tkyte%ORA11GR2> current10 AS (
ops$tkyte%ORA11GR2> SELECT dummy, level as lvl
ops$tkyte%ORA11GR2>   FROM DUAL
ops$tkyte%ORA11GR2>  CONNECT BY LEVEL < 11
ops$tkyte%ORA11GR2> )
ops$tkyte%ORA11GR2> ,
ops$tkyte%ORA11GR2> historical20 AS (
ops$tkyte%ORA11GR2> SELECT dummy, level as lvl
ops$tkyte%ORA11GR2>   FROM DUAL
ops$tkyte%ORA11GR2>  CONNECT BY LEVEL < 21
ops$tkyte%ORA11GR2> )
ops$tkyte%ORA11GR2> select count(dummy)
ops$tkyte%ORA11GR2>   from (
ops$tkyte%ORA11GR2> select * from current10
ops$tkyte%ORA11GR2> where COALESCE(SYS_CONTEXT('userenv','client_info'),'10') = '10'
ops$tkyte%ORA11GR2> UNION ALL
ops$tkyte%ORA11GR2> select * from historical20
ops$tkyte%ORA11GR2> where COALESCE(SYS_CONTEXT('userenv','client_info'),'20') = '20'
ops$tkyte%ORA11GR2> )
ops$tkyte%ORA11GR2> /
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> set linesize 1000
ops$tkyte%ORA11GR2> select * from v;

Execution Plan
----------------------------------------------------------
Plan hash value: 1341760672

-------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |     1 |    13 |     4   (0)| 00:00:01 |
|   1 |  VIEW                              | V    |     1 |    13 |     4   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE                   |      |     1 |     2 |            |          |
|   3 |    VIEW                            |      |     2 |     4 |     4   (0)| 00:00:01 |
|   4 |     UNION-ALL                      |      |       |       |            |          |
|*  5 |      FILTER                        |      |       |       |            |          |
|   6 |       VIEW                         |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  7 |        CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|   8 |         TABLE ACCESS FULL          | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|*  9 |      FILTER                        |      |       |       |            |          |
|  10 |       VIEW                         |      |     1 |     2 |     2   (0)| 00:00:01 |
|* 11 |        CONNECT BY WITHOUT FILTERING|      |       |       |            |          |
|  12 |         TABLE ACCESS FULL          | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   5 - filter(COALESCE(SYS_CONTEXT('userenv','client_info'),'10')='10')
   7 - filter(LEVEL<11)
   9 - filter(COALESCE(SYS_CONTEXT('userenv','client_info'),'20')='20')
  11 - filter(LEVEL<21)

<b>see steps 5 and 9 - they cause the query to execute different branches, like a short circuit</b>


ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info(null);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from v was_null;

       CNT
----------
        30

ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info('10');

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from v was_10;

       CNT
----------
        10

ops$tkyte%ORA11GR2> exec dbms_application_info.set_client_info('20');

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from v was_20;

       CNT
----------
        20


the tkprof shows:

select * from v was_null

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          6          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  VIEW  V (cr=6 pr=0 pw=0 time=0 us cost=4 size=13 card=1)
      1   SORT AGGREGATE (cr=6 pr=0 pw=0 time=0 us)
     30    VIEW  (cr=6 pr=0 pw=0 time=1044 us cost=4 size=4 card=2)
     30     UNION-ALL  (cr=6 pr=0 pw=0 time=1015 us)
     10      FILTER  (cr=3 pr=0 pw=0 time=270 us)
     10       VIEW  (cr=3 pr=0 pw=0 time=261 us cost=2 size=2 card=1)
     10        CONNECT BY WITHOUT FILTERING (cr=3 pr=0 pw=0 time=234 us)
      1         TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us cost=2 size=2 card=1)
     20      FILTER  (cr=3 pr=0 pw=0 time=152 us)
     20       VIEW  (cr=3 pr=0 pw=0 time=114 us cost=2 size=2 card=1)
     20        CONNECT BY WITHOUT FILTERING (cr=3 pr=0 pw=0 time=76 us)
      1         TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us cost=2 size=2 card=1)

<b>set to null, both branches fire</b>

********************************************************************************
select * from v was_10

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  VIEW  V (cr=3 pr=0 pw=0 time=0 us cost=4 size=13 card=1)
      1   SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
     10    VIEW  (cr=3 pr=0 pw=0 time=162 us cost=4 size=4 card=2)
     10     UNION-ALL  (cr=3 pr=0 pw=0 time=153 us)
     10      FILTER  (cr=3 pr=0 pw=0 time=117 us)
     10       VIEW  (cr=3 pr=0 pw=0 time=99 us cost=2 size=2 card=1)
     10        CONNECT BY WITHOUT FILTERING (cr=3 pr=0 pw=0 time=72 us)
      1         TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us cost=2 size=2 card=1)
      0      FILTER  (cr=0 pr=0 pw=0 time=0 us)
      0       VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=2 card=1)
      0        CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=2 card=1)

<b>set to 10, just the first branch goes - the second view, no IO, no work</b>
********************************************************************************
select * from v was_20

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  VIEW  V (cr=3 pr=0 pw=0 time=0 us cost=4 size=13 card=1)
      1   SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
     20    VIEW  (cr=3 pr=0 pw=0 time=494 us cost=4 size=4 card=2)
     20     UNION-ALL  (cr=3 pr=0 pw=0 time=456 us)
      0      FILTER  (cr=0 pr=0 pw=0 time=0 us)
      0       VIEW  (cr=0 pr=0 pw=0 time=0 us cost=2 size=2 card=1)
      0        CONNECT BY WITHOUT FILTERING (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL DUAL (cr=0 pr=0 pw=0 time=0 us cost=2 size=2 card=1)
     20      FILTER  (cr=3 pr=0 pw=0 time=361 us)
     20       VIEW  (cr=3 pr=0 pw=0 time=323 us cost=2 size=2 card=1)
     20        CONNECT BY WITHOUT FILTERING (cr=3 pr=0 pw=0 time=285 us)
      1         TABLE ACCESS FULL DUAL (cr=3 pr=0 pw=0 time=0 us cost=2 size=2 card=1)
<b>and so on</b>





de-plan! de-plan! and de-actuals

A reader, May 08, 2010 - 1:35 pm UTC

Yes, I can see the difference in the "A-rows"... thanks!

SQL> set serveroutput off
SQL> select /*+ gather_plan_statistics */ * from v;

                 CNT
--------------------
                  30

Elapsed: 00:00:00.04
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------
select /*+ gather_plan_statistics */ * from v

------------------------------------------------------------------------------
| Id  | Operation                          | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |      1 |        |      1 |
|   1 |  VIEW                              | V    |      1 |      1 |      1 |
|   2 |   SORT AGGREGATE                   |      |      1 |      1 |      1 |
|   3 |    VIEW                            |      |      1 |      2 |     30 |
|   4 |     UNION-ALL                      |      |      1 |        |     30 |
|*  5 |      FILTER                        |      |      1 |        |     10 |
|   6 |       VIEW                         |      |      1 |      1 |     10 |
|   7 |        CONNECT BY WITHOUT FILTERING|      |      1 |        |     10 |
|   8 |         TABLE ACCESS FULL          | DUAL |      1 |      1 |      1 |
|*  9 |      FILTER                        |      |      1 |        |     20 |
|  10 |       VIEW                         |      |      1 |      1 |     20 |
|  11 |        CONNECT BY WITHOUT FILTERING|      |      1 |        |     20 |
|  12 |         TABLE ACCESS FULL          | DUAL |      1 |      1 |      1 |
------------------------------------------------------------------------------

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

   5 - filter(COALESCE(SYS_CONTEXT('userenv','client_info'),'10')='10')
   9 - filter(COALESCE(SYS_CONTEXT('userenv','client_info'),'20')='20')


30 rows selected.

Elapsed: 00:00:00.14
SQL> exec dbms_application_info.set_client_info('10');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
SQL> select /*+ gather_plan_statistics */ * from v;

                 CNT
--------------------
                  10

Elapsed: 00:00:00.06
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

------------------------------------------------------------------------------
| Id  | Operation                          | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |      1 |        |      1 |
|   1 |  VIEW                              | V    |      1 |      1 |      1 |
|   2 |   SORT AGGREGATE                   |      |      1 |      1 |      1 |
|   3 |    VIEW                            |      |      1 |      2 |     10 |
|   4 |     UNION-ALL                      |      |      1 |        |     10 |
|*  5 |      FILTER                        |      |      1 |        |     10 |
|   6 |       VIEW                         |      |      1 |      1 |     10 |
|   7 |        CONNECT BY WITHOUT FILTERING|      |      1 |        |     10 |
|   8 |         TABLE ACCESS FULL          | DUAL |      1 |      1 |      1 |
|*  9 |      FILTER                        |      |      1 |        |      0 |
|  10 |       VIEW                         |      |      0 |      1 |      0 |
|  11 |        CONNECT BY WITHOUT FILTERING|      |      0 |        |      0 |
|  12 |         TABLE ACCESS FULL          | DUAL |      0 |      1 |      0 |
------------------------------------------------------------------------------

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

   5 - filter(COALESCE(SYS_CONTEXT('userenv','client_info'),'10')='10')
   9 - filter(COALESCE(SYS_CONTEXT('userenv','client_info'),'20')='20')


30 rows selected.

Elapsed: 00:00:00.23

Tom Kyte
May 10, 2010 - 6:24 pm UTC

well, truth be told, you want to use tkprof for this one - as you want to verify that the IO did not take place.

We *know* zero rows will be returned, we need to see cr=0 to see NO IO was done to find those zero rows...

de-old way ... ug

A reader, May 08, 2010 - 1:40 pm UTC

I did check out the plan for my original post.  You're right; not efficient: looks at all 30 rows even when returning only 10...

SQL> ed
Wrote file afiedt.buf

  1  create or replace view v as
  2  WITH
  3  current10 AS (
  4  SELECT level as lvl
  5    FROM DUAL
  6   CONNECT BY LEVEL < 11
  7  )
  8  ,
  9  historical20 AS (
 10  SELECT level as lvl
 11    FROM DUAL
 12   CONNECT BY LEVEL < 21
 13  )
 14  select count(*) cnt
 15    from (
 16  select * from current10
 17  where rownum = CASE WHEN SYS_CONTEXT('userenv','client_info') = '10'
 18                        OR SYS_CONTEXT('userenv','client_info') IS NULL
 19                      then rownum
 20                      else 0
 21                  end
 22  UNION ALL
 23  select * from historical20
 24  where rownum = CASE WHEN SYS_CONTEXT('userenv','client_info') = '20'
 25                        OR SYS_CONTEXT('userenv','client_info') IS NULL
 26                      then rownum
 27                      else 0
 28                  end
 29* )
SQL> /

View created.

Elapsed: 00:00:00.29
SQL> select /*+ gather_plan_statistics */ * from v;

                 CNT
--------------------
                  10

Elapsed: 00:00:00.06
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
--
SQL_ID  1y5nm8rdxtrgr, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from v

Plan hash value: 1165542615

-------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |      1 |
|   1 |  VIEW                               | V    |      1 |      1 |      1 |
|   2 |   SORT AGGREGATE                    |      |      1 |      1 |      1 |
|   3 |    VIEW                             |      |      1 |      2 |     10 |
|   4 |     UNION-ALL                       |      |      1 |        |     10 |
|   5 |      COUNT                          |      |      1 |        |     10 |
|*  6 |       FILTER                        |      |      1 |        |     10 |
|   7 |        VIEW                         |      |      1 |      1 |     10 |
|   8 |         CONNECT BY WITHOUT FILTERING|      |      1 |        |     10 |
|   9 |          FAST DUAL                  |      |      1 |      1 |      1 |
|  10 |      COUNT                          |      |      1 |        |      0 |
|* 11 |       FILTER                        |      |      1 |        |      0 |
|  12 |        VIEW                         |      |      1 |      1 |     20 |
|  13 |         CONNECT BY WITHOUT FILTERING|      |      1 |        |     20 |
|  14 |          FAST DUAL                  |      |      1 |      1 |      1 |
-------------------------------------------------------------------------------

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

   6 - filter(ROWNUM=CASE  WHEN ((SYS_CONTEXT('userenv','client_info')='10') OR
              (SYS_CONTEXT('userenv','client_info') IS NULL)) THEN ROWNUM ELSE 0 END )
  11 - filter(ROWNUM=CASE  WHEN ((SYS_CONTEXT('userenv','client_info')='20') OR
              (SYS_CONTEXT('userenv','client_info') IS NULL)) THEN ROWNUM ELSE 0 END )


34 rows selected.

Ramchandra Joshi, May 10, 2010 - 7:39 am UTC

"what is business date?? where did it magically appear from?
"

Well the business_date is nothing but a date coming from a table which stores today's date , prev bussness date etc.

I just want to know if there is a way by which i can call two different views from within a single view via SELECT statement "based on a condition" and the condition is

TO_DATE(SYS_CONTEXT('userenv','client_info')) = busines_date

So the idea is When I set the context for "Today's DAte ( Which is business date ) Then my view should return output from current_view
and when I set the context for any other date then the view should return output from Historical_view.

So I want to create a view which is some thing Like :

Create or replace my_view as
when TO_DATE(SYS_CONTEXT('userenv','client_info')) = busines_date
then
select * from current_view
wehn TO_DATE(SYS_CONTEXT('userenv','client_info')) <> business_date
then
select * from Historical_view


is it possible to do so ? Or is there a workaround for this ?

I hope I did not confuse you :).

Regards,
Ram.

P.S - I was getting a tablespace full error message from AskTOm throughout the day today .Looks like you are flooded with too many responses :)

Tom Kyte
May 10, 2010 - 7:09 pm UTC

... Well the business_date is nothing but a date coming from a table which stores
today's date , prev bussness date etc.
...

well, your example sure didn't make that clear or say it in any way shape for form. did it??


and we demonstrated how to accomplish this a couple of times right above with a union all view.

to: Ramchandra Joshi

A reader, May 10, 2010 - 12:33 pm UTC

Tom gave the answer above, starting here:
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1448404423206#tom2637000200346101791

It's just like this example.

SQL> variable business_date VARCHAR2(30);
SQL> exec :business_date := '200905';

PL/SQL procedure successfully completed.

SQL> exec dbms_application_info.set_client_info('200905');

PL/SQL procedure successfully completed.

WITH
current_VIEW AS (
SELECT level as lvl, dummy
  FROM DUAL
 CONNECT BY LEVEL < 11
)
,
historical_view AS (
SELECT level as lvl, dummy
  FROM DUAL
 CONNECT BY LEVEL < 21
)
select count(*)
  from (
select * from current_VIEW
where TO_DATE(SYS_CONTEXT('userenv','client_info'),'YYYYMM') = TO_DATE(:business_date,'YYYYMM')
UNION ALL
select * from historical_view
where TO_DATE(SYS_CONTEXT('userenv','client_info'),'YYYYMM') <> TO_DATE(:business_date,'YYYYMM')
)
/
COUNT(*)
--------
      10 -- just the data from the "current view"

SQL> exec dbms_application_info.set_client_info('200904');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> /

            COUNT(*)
--------------------
                  20 -- just the data from the "historical view"

To implement it, you need to figure out how to get the "business_date" into the view from your table "which stores today's date, prev bussness date etc."

It would also be advisable to explicitly specify the date format for the TO_DATE function.

Suggestion to use pipelined table functions.

Casey, May 18, 2010 - 2:12 am UTC

I was reading the thread and it got me thinking. I had done something similar in the past but instead of using parameterized views (just because I never knew about it before this post :D), why not use pipelined table functions instead? Google for it, there are lots of posts on the topic.

I understand that parameterized views have their places, if the client is only able to perform SQL queries. However, since the poster had said he/she is running from web app and calling SQL, why not just call PL/SQL to do the trick?

Also, I had a technique where I would pass in a chunk of string, either XML or delimiter-separated values. Then I'll perform the tokenization within PL/SQL or SQL to get a table, which can then be used as a filter.

The logic goes as follows:
WITH dummy_data AS
(SELECT 'A;BB;C;D;E;FFF;' AS str
FROM DUAL
UNION ALL
SELECT 'USER_TABLES;USER_INDEXES;ALL_OBJECTS' AS str
FROM DUAL)
,d AS
(SELECT str
, LENGTH(str) - LENGTH(REPLACE(str, ';', '') ) AS len
FROM (SELECT ';' || str || ';' AS str
FROM dummy_data) )
,max_d AS
(SELECT MAX(len) AS len
FROM d)
,dummy_rows AS
(SELECT ROWNUM AS occurrence
FROM DUAL
,max_d
CONNECT BY ROWNUM <= max_d.len)
,cartesian_prod AS
(SELECT *
FROM d JOIN dummy_rows r ON(r.occurrence <= d.len) )
SELECT *
FROM (SELECT str
,occurrence
,SUBSTR(str, INSTR(str, ';', 1, occurrence) + 1
, INSTR(str, ';', 1, occurrence + 1)
- INSTR(str, ';', 1, occurrence)
- 1) AS token
FROM cartesian_prod)
WHERE token IS NOT NULL
ORDER BY str
,occurrence;

This is actually unpivoting a "pivot table". So if you have Oracle 10g, you can use its function. However I'm working on 9i, hence have to come up with such a convoluted method.

RE: Pipelined table functions?

A reader, May 19, 2010 - 9:42 am UTC

Casey-- Why use a procedural language (like PL/SQL) when SQL does the job faster and better? Are you thinking of a different problem like this?
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:210612357425

parameterized view examples in your site

santosh, July 21, 2010 - 12:30 am UTC

Hi Tom,
I am looking for a view where the select query has multiple conditions. So,the paramterized view will
need to be set multiple values for multiple parameters.
As per this page,it should be possible through dbms_session.set_context and reference later using sys_context . Can you please provide me an example ?

Tom Kyte
July 23, 2010 - 8:20 am UTC

just reference sys_context as many times as you would like.


create context foo and then set in foo the values v1, v2, v3 (or whatever) and reference

where x = sys_context( 'foo', 'v1' ) and y = sys_context( 'foo', 'v2' ) ...

Parametererized Views

Steve, September 02, 2011 - 6:00 am UTC

Hello Tom,

Based on your examples of a parameterized views, I have a small requirement where I am selecting data from a partitioned table and I need to be able to dynamically replace the partition name in the partition clause of the query. Example below. Hope you have a solution for me. Keep up the good work and you have a great site.

CREATE OR REPLACE VIEW vw_acc_data
AS
WITH acc_data AS
(
SELECT col1, col2 , col3
FROM account PARTITION(<dynamic value here>)

-- rest of the view query
---
---
--;

I would like to dynamically change the partition name in the view. How can I do it? Is it possible in the first place.
Thanks for your help.


Tom Kyte
September 02, 2011 - 8:44 am UTC

nope, you would add a predicate that queries the right partition for you.


you cannot replace IDENTIFIERS (objects) in a query at runtime, you would have to build a specific query for that - the plans would be different, you need an entirely separate query for each and every partition if you wanted to do this.


Parameterized view

Juli, July 10, 2012 - 7:46 am UTC

Hi Tom,

you have mentioned the method with setting the parameter 'client_info' to parameterize a view. Your example was:

create view myview
as select * from t where x = userenv('client_info')
/
and then:
SQL> exec dbms_application_info.set_client_info('my_data')
SQL> select * from myview;

That works very good, but what should I do, if I want to select something like that:

create view myview
as select * from t where x in userenv('client_info')

I tried setting the parameter with something like that:
exec dbms_application_info.set_client_info('(''abc'',''xyz'')');

But that doesn't work.

What the best way to parameterized a range?



remote view

Fede, July 11, 2012 - 4:08 pm UTC

I see several examples mixing remote views and parameters, and I have a doubt:

If the remote view was created to resolve an Insert...Into in the remote server, it's mandatory to use parameters?

I mean, in this example, it's the query solved local or remote?:

insert into local_table (col1, col2)
select col1, col2
from remote_view
where col3 = :myvar;



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library