Re: Execute immediate

Niall Litchfield wrote:
>
> 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
> > >
> > >
> >

Unknown number of parameters until run time is one reason, although this is commonly implemented with a number of 'default' parameters

hth
connor Received on Thu Feb 06 2003 - 22:03:35 CST