Lexical Parameters

[Mimin]

is there any possiblity that i could use a parameter that is changed dynamically in pl-sql like lexical parameter of query?
Thanks

 

[Mimin]

does anybody had any solution for lexical parameter i am using on the following pl/sqp

i had query like this
select s, y from z
&p1
&p2;
then i had one procedure
procedure xxx (x in varchar2)
w varchar2(10);

cursor is select h,t from z
&p1

how could i use the paramters that i already used in the first query if i want to do the same in the procedure?
Thanks

 

[nagornyi]

You can do this by using SRW.DO_SQL procedure.

 

[Mimin]

Thanks for the reply
But is it possible to use srw.do_sql for creating cursor within the procedure. i want to create cursor cause the query returns more than one rows.
Thanks

 

[HimanB]

Hi,
Can you explain what are you using &p for?

You may completely base your Report on Refcursors also or may make use of Inline views.
Even you can make use of Data parameter to prepare & populate your Report from the calling form.

So, please explain your requirment in a bit detail.

Regards
Himanshu

 

[Mimin]

Thanks
i am using &p to have a parameter that is going to be changed dynamically(lexical parameter). I had main query and i want to have a formula column which gets the value from the procedure. the procedure should take the parameters that i used in the main query (i mean the lexical parameters i used in the main query). but it is not possible to used lexical parameters in pl/sql. i think i make my requirment a little bit clear.
I appreciate your assistance
Thanks

 

[nagornyi]

Using SRW.DO_SQL you can define entire procedure on the DB side, including that you can construct the variable cursor. Then you can use this procedure in the report. If you prefer to keep procedures locally in the report, look into the DBMS_SQL package:

http://www.csee.umbc.edu/help/oracle8/server.815/a68001/dbms_sql.htm