Re: PL/SQL Dynamic sql

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<cafbcp$lv3$1_at_titan.btinternet.com>...
> 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
> >
> >

#

Jonathan

Thanks for that you may just have saved me writing a huge case statement!

Steve Received on Sun Jun 13 2004 - 02:20:18 CDT