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.
IIRC he did. I snipped it as extraneous in my reply.
--
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 - 16:03:08 CDT
