Re: PL/SQL Dynamic sql

Andy,
A small change to your approach to use
globally packaged variables solves the
problem of scope:

create or replace package indirect as
 global_n1 number(38);
 procedure demo;
end;
/

create or replace package body indirect as procedure demo is
 local_ptr varchar2(32);
begin
 local_ptr := 'indirect.global_n1';
 indirect.global_n1 := 10;

 execute immediate
  'begin ' || local_ptr || ' := 4; end;'  ;

 dbms_output.put_line(indirect.global_n1); end;
end;
/

execute indirect.demo

• gets the output 4, not 10.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"Andy Hassall" <andy_at_andyh.co.uk> wrote in message
news:1m3mc0pcrsfktpd3dfqfrhqp2sqolmlvk4_at_4ax.com...


>

> 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

>

>