Dynamic Variable Names [message #257611] |
Wed, 08 August 2007 14:50 |
yerics
Messages: 89 Registered: August 2006
|
Member |
|
|
In PLSQL can the variable name be dynamic?
I had a execute immediate stmt but I dont know the number of INTO variables till runtime. If I gave more than the required no of variables, then i got ORA-01007 - variable not in select list error. To get around that I use DBMS_SQL. But I am stuck up at a point whereby I want to assign the values to the variable name and I dont know how. A small snippet of what I am looking for is attached.
for i in 1 .. l_colcnt
loop
dbms_sql.define_column(l_sqlcursor,i,l_columnvalue,4000);
end loop;
l_status := dbms_sql.execute(l_sqlcursor);
while (dbms_sql.fetch_rows(l_sqlcursor) > 0 )
loop
for i in 1 .. l_colcnt
loop
dbms_sql.column_value(l_sqlcursor,i,l_columnvalue);
l_userformat||i := l_columnvalue; -- <== How to assign it to a dynamic variable name.end loop;
end loop;
Regards.
|
|
|
Re: Dynamic Variable Names [message #257613 is a reply to message #257611] |
Wed, 08 August 2007 15:10 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
I'm curious by what mechanism would code "know/choose" which "dynamic variable name" to use?
Looks like you are going to have to write PL/SQL to write PL/SQL.
A shovel is a great tool for making a hole in the ground;
but only when the "correct end" of the shovel comes into contact with the Earth.
I suspect that you are using the "wrong end" of PL/SQL, but you are free to keep digging the hole in which you now find yourself.
[Updated on: Wed, 08 August 2007 15:22] by Moderator Report message to a moderator
|
|
|
|
|
|
|