Re: Execute immediate

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Thu, 6 Feb 2003 18:26:54 +0300
Message-ID: <b1tuul$4hl$1@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@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 - 09:26:54 CST