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.
Received on Thu Jun 22 2006 - 16:01:23 CDT
