Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Variable Names
Dynamic Variable Names [message #257611] Wed, 08 August 2007 14:50 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 25033
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

Re: Dynamic Variable Names [message #257616 is a reply to message #257613] Wed, 08 August 2007 15:23 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Are you saying that there is no way to avoid ORA-01007, in case whereby number of output variables are not known using dbms_sql.
Re: Dynamic Variable Names [message #257620 is a reply to message #257611] Wed, 08 August 2007 15:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do that.

Regards
Michel
Re: Dynamic Variable Names [message #257621 is a reply to message #257611] Wed, 08 August 2007 15:34 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>in case whereby number of output variables are not known using dbms_sql.
From my experience I say this situation results directly from a seriously flawed design.

The PL/SQL compiler needs to know the number of output variables so it have places in memory to deposit their values.
Re: Dynamic Variable Names [message #257623 is a reply to message #257621] Wed, 08 August 2007 15:55 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
thank you...

regards,
Previous Topic: Pivot and Crosstab Queries
Next Topic: Insert into table in PL/SQL
Goto Forum:
  


Current Time: Sat Dec 03 07:52:43 CST 2016

Total time taken to generate the page: 0.09558 seconds