Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL and Batch Script

Re: PL/SQL and Batch Script

From: atta ur-rehman <atta707_at_hotmail.com>
Date: 11 Jul 2001 07:37:34 -0700
Message-ID: <6e453d75.0107110637.421543f5@posting.google.com>

"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;';

begin
EXECUTE IMMEDIATE queries;
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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US