Re: Parameter to an oracle-view

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