Re: How to pass bind variable value into a view

: RDBMS : 9.2.0.8

: I have the following query:

: select * from v1 where v1_col1=:BIND_VAR

: v1 is a view:

: create view v1
: (v1_col1, v1_col2)
: as
: select t1.col1, t2.col2 from t1,t2
: where t1.col1=t2.col2

: t1.col1 - unique index,
: t2.col2 - non unique index.

: Noticed that:

: select * from v1 where v1.col1=?

: takes about 60sec to execute.

: however,

: select * from ( select t1.col1, t2.col2 from t1,t2
: where t1.col1=t2.col2
: and t1.col1=:BIND_VAR)

: takes 1 sec to execute.

: mainly here rowsource is reduced when t1.col1 is taken alongwith inner
: query.

: question:

: a) Is there any way we can pass bind variable to the inner query with
: using view v1 ( i know BIND
: var cannot be passed to DDL)but any other way you would suggest to
: achieve the same result.

Not as a bind variable.

The SYS_CONTEXT function is often used for this purpose. In conjunction with this you must create a context and a package to update the context with what ever values the queries will need. (And you have to do the setting of the value before calling the query of course). Google etc etc.

You can also create a package and use it to store variables (which must be accessed by functions from within the query).

• untested code create or replace package MY_QUERY_VARS as procedure set_the_value( p number); function the_value return number; end ; create or replace package body MY_QUERY_VARS as l_the_value number; procedure set_the_value( p number) is begin l_the_value := p; end; function the_value return number is begin return l_the_value; end; end ;

create or replace view MY_VIEW as
select * from my_table where something = MY_QUERY_VARS.the_value; Received on Thu Oct 08 2009 - 15:15:44 CDT