Re: Basic question | run multiple queries without reconnecting

Anoop wrote:
> DA Morgan wrote:
> > Anoop wrote:
> > > Hi all,
> > >
> > > I have a very basic question...
> > >
> > > How can you run multiple queries w/o reconnecting to the database? I
> > > cannot use stored procedures.
> > >
> > > What I have is a set of 10-12 queries like this:
> > > audit all by <acct> by access;
> > > audit alter sequence by <indiv acct> by access;
> > > audit alter table by <acct> by access;
> > > audit comment table by <cct> by access;
> > > audit grant procedure by <acct> by access;
> > > audit grant sequence by <acct> by access;
> > > audit grant table by <acct> by access;
> > > audit grant type by <acct> by access;
> > > audit lock table by <acct> by access;
> > >
> > > But in order to run them, I do not want to establish a connection for
> > > each of the statements. I am using Oracle 9i and Java as the
> > > programming language.
> > >
> > > Thanks,
> > > Anoop
> >
> > First nothing you've written is a query. These are DCL statements and
> > the solution is as follows:
> >
> > BEGIN
> > <statement 1>;
> > <statement 2>;
> > <statement n>;
> > END;
> > /
> >
> > It is called an anonymous block.
> >
> > You can find examples in Morgan's Library at www.psoug.org.
> > Look up "ANONYMOUS BLOCKS."
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan_at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Group
> > www.psoug.org

>
>

>

So this is like a PL/SQL block right?

My problem is that we have 100's of oracle databases and we cannot implant a procedure like the above (the anonymous block) into every database. We do have a centralised app written in Java which connects to each database to run these DCL statements on user creation..

So my question is:
1. Is it necessary that this block reside on each server (so that I can use CallableStatements and call this block). If yes, then that will not work for me as we have too many databases. 2. Can I just have a text file or maybe hardcode these in a script and run it using java?

Thanks,
Anoop Received on Thu Jun 22 2006 - 13:35:42 CDT