Re: Basic question | run multiple queries without reconnecting

"Anoop" <anoopkumarv_at_gmail.com> wrote in message news:1151001342.044181.305150_at_c74g2000cwc.googlegroups.com...
:
: 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
: >
: >
: > Thank you - I think I need to read up more before I ask questions.
: >
: > Best regards,
: > Anoop
:
:
: 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
:

'anonymous block' impies that it is not a stored procedure -- it is submitted like any other SQL statement

putting multple statements in an anonymous block allows them to be submitted as a single statement -- and also allows all other PL/SQL constructs

however, AUDIT statements cannot be directly included in PL/SQL -- you would need to use EXECUTE IMMEDIATE for each command

additionally, there should be no reason to open and close your apps connection for each statement -- simple establish the connection, issue each command, then close the connection

is there any reason why it seems to be necessary to disconnect and reconnect between statements?

++ mcs Received on Thu Jun 22 2006 - 14:12:51 CDT