Re: Basic question | run multiple queries without reconnecting


DA Morgan wrote:
Chuck wrote:
DA Morgan wrote:
Anoop wrote:
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@xxxxxxxxxxxxxxxx
(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
What you wrote was that you were using Java. There is no reason Java
can not be used to pass an anonymous block to an instance.

Originally didn't mention Java. There's no reason why a simple sql
script couldn't be used with sqlplus either.

IIRC he did. I snipped it as extraneous in my reply.
--
Daniel A. Morgan
University of Washington
damorgan@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thank you to all..

I have managed to get something working.... and yes - I need not
re-establish my connection after every statement - I am running them
all as multiple statements... (not as a block or anything).. and the
end I just close my connection..

But I am facing a problem still - a bit unrelated and rather basic..
But I will go ahead and ask.

From an oracle client like oracle sqldeveloper, I can run the DCL -
"audit all by anoop by access" just fine if I log in as sys as sysdba
role.

But I need to run these as another user - system; and I am getting this
error message:

java.sql.SQLException: ORA-00987: missing or invalid username(s)

I think this means system does not have privileges and I tried using
the same: ie., tried connecting as
system as role: sysdba - but then I cant even connect to the db.. the
test connection fails.

How can give additional privileges to the user "system" so that I can
run the above command?

My java code I have written does not allow me to connect as sysdba
role....

Thanks,
Anoop

.

Relevant Pages

  • Re: Basic question | run multiple queries without reconnecting
    ... How can you run multiple queries w/o reconnecting to the database? ... audit alter sequence by <indiv acct> by access; ... We do have a centralised app written in Java which connects ...
    (comp.databases.oracle.server)
  • Re: Basic question | run multiple queries without reconnecting
    ... How can you run multiple queries w/o reconnecting to the database? ... audit alter sequence by <indiv acct> by access; ... It is called an anonymous block. ...
    (comp.databases.oracle.server)
  • Re: Basic question | run multiple queries without reconnecting
    ... How can you run multiple queries w/o reconnecting to the database? ... audit alter sequence by <indiv acct> by access; ... It is called an anonymous block. ...
    (comp.databases.oracle.server)
  • Re: Concatenate and Null Values -- Features
    ... I hate to be a pest but I have been reading ACC: Database Normalization ... Data Table and linked to this one using the Audit Number. ... I wonder if storing 07-01 (probably without the dash, ... I think I know what you're saying about learning by teaching. ...
    (microsoft.public.access.reports)
  • Re: Auditting requirements
    ... To enable auditting we need to run commands like this: ... audit alter sequence by <indiv acct> by access; ... audit grant procedure by by access; ...
    (comp.databases.oracle.server)