Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL and Batch Script
"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:<3B4BDC2F.C3D2616_at_exesolutions.com>...
> atta ur-rehman wrote:
>
> > Dear all,
> >
> > in SQL*Plus we are able to copy-paste a script like:
> >
> > insert into table1 values(1)
> > /
> > insert into table1 values(2)
> > /
> > .
> > .
> > .
> > /
> >
> > and all the statements are process in turn until list is exhuasted.
> >
> > my question is: is it possible to do the same, or is there an
> > equvilant way to do it, in PL/SQL? Basically I want to send a varchar2
> > parameter, consisting of an arbitrary number and types of SQL DMLs,
> > and want to be able to submit it to the Oracle for processing them
> > all. e.g:
> >
> > queries varchar2 := 'insert into table1 values(1); insert into tablex
> > values('x)';
> > EXECUTE IMMEDIATE queries;
> >
> > currently that doesn't work, of course. is there a way to achieve this
> > behavior in a PL/SQL block. We're on Oracle 8.1.6, Windows NT.
> >
> > Thanks in advance for help.
> >
> > regards,
> >
> > :) ATTA
>
> I'm not sure what you mean by "currently that doesn't work". Have you
> looked at coding within an anonymous block or stored procedure with a
> loop?
>
> Daniel A. Morgan
Thanks Daniel for your reply,
Tokenizing the string and running the individual queries in a loop is something i really wanted to avoid. So even though a reall possiblity, i want to avoid taking that route.
for the anonymous block i tried the trick, also suggested by Mark on this thread and, wow, that workd!
declare
queries varchar2(200) :=
'BEGIN insert into table1 values(1); insert into table1 values(2); END;';
I think this would do the trick for me.
Thank you both of you, Mark and Daniel.
Regards,
:) ATTA Received on Wed Jul 11 2001 - 09:37:34 CDT