Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL with dynamic variable names
Dynamic SQL with dynamic variable names [message #193200] Fri, 15 September 2006 04:44 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #193505 is a reply to message #193274] Sun, 17 September 2006 23:58 Go to previous messageGo to next message
guntbhar
Messages: 2
Registered: September 2006
Junior Member
Hi, Here I'm explaining my core purpose. Any possible solutions are most welcome.

In the attachment, I’ve mentioned the prototype structure of the table from which the variable name will be taken, say table PARAMETERS, example records in that table and how the SELECTs and UPDATEs should occur based on those variables.



  • Attachment: example.doc
    (Size: 41.50KB, Downloaded 704 times)
Re: Dynamic SQL with dynamic variable names [message #381532 is a reply to message #193505] Sat, 17 January 2009 15:49 Go to previous messageGo to next message
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 #381533 is a reply to message #193200] Sat, 17 January 2009 16:24 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
Hmmm...
Responding to a 2.25 year old post might be of little value to OP.
Dynamic SQL scales as well as a lamb can fly.


KentHaidl,
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: Dynamic SQL with dynamic variable names [message #381786 is a reply to message #381533] Mon, 19 January 2009 19:03 Go to previous message
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
/

Previous Topic: Update attributes, ORA-01733
Next Topic: procedure in another procedure
Goto Forum:
  


Current Time: Wed Dec 07 16:19:48 CST 2016

Total time taken to generate the page: 0.10781 seconds