oops, that was rather a senior moment wasn't it. sorry. (though I still
can't imagine why you would use execute immediate to run pl/sql).
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message
news:b1tuul$4hl$1_at_babylon.agtel.net...
> This is normal, but not due to reason Niall gave: it's perfectly valid
> to execute either SQL or PL/SQL with execute immediate. The
> problem is
>
> > > variable a number;
>
> which is SQL*Plus host variable definition, not PL/SQL declaration.
> Statement like this:
>
> DECLARE
> a number;
> begin
> execute immediate 'begin :a := MyFunction(NULL); end;'
> USING OUT a;
> end;
>
> will work:
>
> SQL> declare
> 2 a number;
> 3 begin
> 4 execute immediate
> 5 'begin :a := MyFunction(NULL); end;'
> 6 using out a;
> 7 end;
> 8 /
>
> PL/SQL procedure successfully completed.
>
> (I've created a dummy MyFunction for this demo run.)
>
> --
> Vladimir Zakharychev (bob_at_dpsp-yes.com)
http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
applications.
> All opinions are mine and do not necessarily go in line with those of my
employer.
>
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3e427bbb$0$245$ed9e5944_at_reading.news.pipex.net...
> > "Adrien" <Adrien_at_nospam.com> wrote in message
> > news:200326-12118-911965_at_foorum.com...
> > >
> > > Hello,
> > >
> > > If I execute a stored function in SQLPlus :
> > >
> > > variable a number;
> > > BEGIN
> > > :a := MyFunction ( NULL );
> > > END;
> > > /
> > >
> > > It works.
> > >
> > > But the same statement in an 'EXECUTE IMMEDIATE' statement in PL/SQL :
> > >
> > > EXECUTE IMMEDIATE 'variable a number; BEGIN :a := AlimGCE_CONT (
NULL );
> > > END;';
> > >
> > > doesn't work =>
> > > ORA-00900: invalid SQL statement
> > >
> >
> > Yes. execute immediate is a way of dynamically executing SQL not PL/SQL
just
> > use the PLSQL in the normal way.
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> >
> >
>
Received on Thu Feb 06 2003 - 10:08:07 CST
