Re: Parameter to an oracle-view

For a little more flexibility, you could use a custom defined 'context'. Then you use the SYS_CONTEXT function to get values in your view. Take a look at CREATE CONTEXT and the SYS_CONTEXT function.

Richard

Thomas Kyte wrote:
>
> In article <a23mbc$h48$00$1_at_news.t-online.com>, "Florian says...
> >
> >Hi,
> >
> >kann ich - beispielsweise von einem Front-End aus - an einen Oracle-view
> >einen Parameter senden, auf den dann im where-statement zugegriffen wird?
> >
> >Gruß
> >
> >
> >Florian
> >
> >can I - for example from any front-end - send a parameter to an oracle view,
> >and this parameter will processed in the where-statement of the view?
> >
> >
> >Regards
> >
> >
> >Florian
> >
> >
>
> you can use dbms_application_info for something like this:
>
> scott_at_ORA817DEV.US.ORACLE.COM> create or replace view v
> 2 as
> 3 select ename, empno
> 4 from emp
> 5 where ename like userenv('client_info' );
>
> View created.
>
> scott_at_ORA817DEV.US.ORACLE.COM>
> scott_at_ORA817DEV.US.ORACLE.COM> exec dbms_application_info.set_client_info( '%A%'
> );
>
> PL/SQL procedure successfully completed.
>
> scott_at_ORA817DEV.US.ORACLE.COM> select * from v;
>
> ENAME EMPNO
> ---------- ----------
> ALLEN 7499
> WARD 7521
> MARTIN 7654
> BLAKE 7698
> CLARK 7782
> ADAMS 7876
> JAMES 7900
>
> 7 rows selected.
>
> scott_at_ORA817DEV.US.ORACLE.COM> exec dbms_application_info.set_client_info( '%B%'
> );
>
> PL/SQL procedure successfully completed.
>
> scott_at_ORA817DEV.US.ORACLE.COM> select * from v;
>
> ENAME EMPNO
> ---------- ----------
> BLAKE 7698
>
> scott_at_ORA817DEV.US.ORACLE.COM>
>
> the client front end needs to be able to execute a stored procedure call is all
> to "set" the parameter.
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Wed Jan 16 2002 - 13:03:21 CST