Home » SQL & PL/SQL » SQL & PL/SQL » How to extract parameter value whose name is in variable?
How to extract parameter value whose name is in variable? [message #221253] Mon, 26 February 2007 00:20 Go to next message
alokm
Messages: 5
Registered: February 2007
Junior Member
Please help
My parameter name is in variable and I want to extract its value
I have given an example here
I have to use this in procedure

declare
val1 varchar2(20);
val2 varchar2(20);
val3 varchar2(20);

begin
val1:='val2';
val2:='abc';
execute immediate 'select :' ||val1||' into val3 from dual ;';
end;

Its not working
Please give some solution its urgent.

thanks in advance
alok

[Updated on: Mon, 26 February 2007 00:53]

Report message to a moderator

Re: How to extract parameter value whose name is in variable? [message #221261 is a reply to message #221253] Mon, 26 February 2007 00:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The manual contains a number of examples of Dynamic SQL.

Ross Leishman
Re: How to extract parameter value whose name is in variable? [message #221281 is a reply to message #221261] Mon, 26 February 2007 03:11 Go to previous messageGo to next message
alokm
Messages: 5
Registered: February 2007
Junior Member
Thanks for reply. But I am unable to get any solution.
Can you please provide the solution in this case
thanks
alok
Re: How to extract parameter value whose name is in variable? [message #221308 is a reply to message #221281] Mon, 26 February 2007 04:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is merely impossible to do in PL/SQL without some major (and error-prone) dynamic sql.
Could you describe your actual problem?
Re: How to extract parameter value whose name is in variable? [message #221318 is a reply to message #221308] Mon, 26 February 2007 04:44 Go to previous messageGo to next message
alokm
Messages: 5
Registered: February 2007
Junior Member


There are 70 to 80 parameters in my stored procedure. Names of these parameters are also stored in another table suppose table "temp".
Now I have to insert the value of these parameters in another table say table "Y". For each parameter I have to insert a new record in the table(this is the only case). So I have fetched all the parameters name from "temp" table into a cursor and inserting it into table "Y".
This I have written in cursor for loop.
Can you please provide any other solution if possible
Thanks
Alok
Re: How to extract parameter value whose name is in variable? [message #221342 is a reply to message #221318] Mon, 26 February 2007 07:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I am not really sure if I get what you want, but did you take a look at plsql-tables? (index by varchar2, the value in temp)
Re: How to extract parameter value whose name is in variable? [message #221348 is a reply to message #221342] Mon, 26 February 2007 07:40 Go to previous messageGo to next message
alokm
Messages: 5
Registered: February 2007
Junior Member
The temp table only contains name of parameters and not
the values.But I have to insert values coming in these parameters in table Y.
My assumption is like this

create or replace procedure ak(p1 varchar2
..
p80 varchar2)
Is
cursor cur select * from temp;

begin
for i in cur;
execute immediate 'select '||cur.parameter_name||' into val1 from dual;';
/*This will repeat 80 times ie no. of parameters*/
insert into y values(val1,val2,val3);
end loop;
end;

Here parameter_name is attribute name in "temp" table storing parameters name of stored procedure P1....P80 are parameters name. Val2, Val3 are other values which I am inserting.That is not our concern
Hope You understand my problem

Thanks
Alok

[Updated on: Mon, 26 February 2007 07:50]

Report message to a moderator

Re: How to extract parameter value whose name is in variable? [message #221361 is a reply to message #221348] Mon, 26 February 2007 08:07 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you would replace your 80 parameters with one plsql-table, indexed by the values in temp ('p1' - 'p80'), this will be much easier.
Then you can use p_table(l_fetched_value_from_temp)
Look in the manuals for collection types if you don't know plsql tables.
Previous Topic: sql help
Next Topic: Why optimizer select plan with higher cost?
Goto Forum:
  


Current Time: Thu Dec 08 02:32:19 CST 2016

Total time taken to generate the page: 0.10887 seconds