| Dynamic SQL with dynamic variable names [message #193200] |
Fri, 15 September 2006 04:44  |
guntbhar
Messages: 2
Registered: September 2006
|
Junior Member |
|
|
Hi, I want to use variables in PL/SQL code such that the variable names are retrieved dynamically from a database table. The variable should not be seen anywhere else in the code apart from the declaration section.
Ex:
I declare a variable x like
x varchar2(5);
Now suppose , there is a table PARAMETERS with column VARIABLE_NAME.
VARIABLE_NAME will contain string 'x'. My code will retrieve it and store it in y, like
select variable_name into y from parameters;
Now if I want to assign any value to x, I should be able to do through y.
I want to build a dynamic SQL such that
sql_stmt := 'Select total into '||y||' from summary';
Now the dynamically formed sql_stmt is 'Select total into x from summary'
I want to excute this using dynamic SQL , so that value of total is selected into x.
Similarly for accessing x
sql_stmt := 'Update summary set total := '||y;
sql_stmt forms as 'Update summary set total := x' . I want to execute this such that value of x goes to total.
But when I do this with 'execute immediate', I'm getting an error 'missing expression' or 'variable not defined'.
I even tried by declaring the variable at package level. Still the problem persists.
Can anyone help?
Thanks
|
|
|
|
| Re: Dynamic SQL with dynamic variable names [message #193274 is a reply to message #193200] |
Fri, 15 September 2006 09:35  |
shoblock
Messages: 325
Registered: April 2004
|
Senior Member |
|
|
you can't do that. the dynamic sql is for SQL, not VARIABLES. the statement passed to EXECUTE IMMEDIATE is SQL ONLY. EXEC IMMED does NOT suppport the INTO clause. INTO is a PL/SQL construct. the proper syntax is:
EXECUTE IMMEDIATE 'select total from summary' INTO x;
exec immed only sees the part between the quotes, and the INTO clause is not allowed there, so it cannot be made dynamic.
perhaps if you explain why you want the variable to be dynamic (most people want the tables or columns to be dynamic), someone can offer an alternative solution
|
|
|
|
| Re: Dynamic SQL with dynamic variable names [message #193376 is a reply to message #193274] |
Fri, 15 September 2006 22:48 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
To control your dynamic SQL to that degree, you will need to use the DBMS_SQL package instead of EXECUTE IMMEDIATE. Note that DBMS_SQL is much more difficult to use than EXECUTE IMMEDIATE.
Ross Leishman
|
|
|
|
|
|
| Re: Dynamic SQL with dynamic variable names [message #381532 is a reply to message #193505] |
Sat, 17 January 2009 15:49 |
KentHaidl
Messages: 2
Registered: January 2009
|
Junior Member |
|
|
create table parameters
(
variable_name varchar2(30)
)
/
create table summary
(
total number
)
/
insert into parameters values( 'x' )
/
insert into summary values( 123 )
/
create /*or replace*/ package pkg
is
x summary.total%type;
procedure proc;
end;
/
create /*or replace*/ package body pkg
is
procedure proc
is
y parameters.variable_name%type;
begin
select variable_name into y from parameters;
execute immediate 'begin select total into pkg.' || y || ' from summary; end;';
x := x + 1; -- just to demo select and update work
execute immediate 'begin update summary set total = pkg.' || y || '; end;';
end;
end;
/
-- test
begin
pkg.proc;
end;
/
select * from summary
/
|
|
|
|
|
|
| Re: Dynamic SQL with dynamic variable names [message #381786 is a reply to message #381533] |
Mon, 19 January 2009 19:03 |
KentHaidl
Messages: 2
Registered: January 2009
|
Junior Member |
|
|
With regards to replying to a rather old post, I got to this post from the page 1 results of a google search which led to several dead ends. When I arrived at the solution, I decided to reply despite the age of the post since the technique is still relevant to current versions of Oracle and others might google their way to the same dead end post.
With regards to dynamic SQL not scaling well, your point is well taken, however, sometimes the need for the flexibility of data driven code outweighs the scaling limitation. In my project's case, the technique will save future maintenance on an infrequently called piece of code.
Thanks for the Posting Guidelines. The code with the proper surrounding tags follows:
create table parameters
(
variable_name varchar2(30)
)
/
create table summary
(
total number
)
/
insert into parameters values( 'x' )
/
insert into summary values( 123 )
/
create /*or replace*/ package pkg
is
x summary.total%type;
procedure proc;
end;
/
create /*or replace*/ package body pkg
is
procedure proc
is
y parameters.variable_name%type;
begin
select variable_name into y from parameters;
execute immediate 'begin select total into pkg.' || y || ' from summary; end;';
x := x + 1; -- just to demo select and update work
execute immediate 'begin update summary set total = pkg.' || y || '; end;';
end;
end;
/
-- test
begin
pkg.proc;
end;
/
select * from summary
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
