| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> 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_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
> >> 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.
>
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
Received on Fri Jun 23 2006 - 16:22:18 CDT
 |
 |