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.
--
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
Received on Thu Jun 22 2006 - 15:43:11 CDT
