Re: PL/SQL Dynamic sql

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Sat, 12 Jun 2004 15:22:20 +0100
Message-ID: <1m3mc0pcrsfktpd3dfqfrhqp2sqolmlvk4@4ax.com>

On 12 Jun 2004 04:22:06 -0700, steve.rainbird_at_mssint.com (Steve Rainbird) wrote:

>Is it possible to generate what I would call Dynamic PL/SQL?

 Dynamic PL/SQL is certainly possible with execute immediate.

>What I am trying to do is the following.
>
>I want to assign a value to a variable in PL/SQL but I don't know the
>name of the variable until run time.

 Sounds strange; since you've got to declare the variable at compile time in the first place, giving you a finite number of possible variables, and so at worst it's a large case statement.

 Or would one of the PL/SQL collection types be more appropriate, such as an index by varchar2 associative array (from 9i)?

 Putting that aside for the moment...

>I have tried the following (and many other variations) without
>success.
>
>declare
> exstring varchar2(500);
> varname varchar2(20);
> var number;
>begin
> var:=1;
> varname:='var';
> exstring:='begin :1 := 5; end;';

 You can't bind identifiers, so :1 is out of the contention here. Closer to the mark would be to just concatenate the variable name in to exstring, but I suspect that won't work either. Testing it gives:

SQL> declare

  2     exstring varchar2(500);
  3     varname varchar2(20);
  4     var number;
  5  begin
  6     var := 1;
  7     varname := 'var';
  8     exstring := 'begin ' || varname || '  := 5; end;';
  9     dbms_output.put_line(exstring);
 10     execute immediate exstring;
 11     dbms_output.put_line('var=' || var);

 12 end;
 13 /
begin var := 5; end;
declare
*
ERROR at line 1:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'VAR' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored
ORA-06512: at line 10

 Which I believe implies that anonymous PL/SQL blocks run through execute immediate aren't actually executed in the scope of the execute immediate statement, rather they are entirely separate in scope and so you have no access to anything in the outer scope. That pretty much puts the stops on this idea.

--
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk         / http://www.andyhsoftware.co.uk/space

Received on Sat Jun 12 2004 - 09:22:20 CDT