Home » Infrastructure » Windows » batch file windows environment (oracle 10g)
batch file windows environment [message #429502] Wed, 04 November 2009 04:51 Go to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

I have searched in our forum the following but dint find the related question. If it is answered already please point me to the proper URL.

My question is,

I have simple sql, that i need to run in different databases.

I have written a code to connect to the db then run the sql file.
here it looks like (I am doing it in windows from sql*plus)

sqlplus UN/PW@DB  @aa.sql

here, aa.sql contains my sqlqquries

For example

begin
dbms_output.put_line('Hi');
end;
/


Now i am using different batch files to connect to different databases and run the same sql file like above. can you pls tell me how to connect all the database one by one and run the sql file from a single batch file.


i.e. all the connections should be in the same batch file..if i click that it should connect to each database and run the sql and reconect to diferent database and run ..


Regards,
Pointers

Re: batch file windows environment [message #429506 is a reply to message #429502] Wed, 04 November 2009 04:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a loop in your batch file for all databases.

Regards
Michel
Re: batch file windows environment [message #429542 is a reply to message #429502] Wed, 04 November 2009 08:10 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
or just brute force:
sqlplus a/b@c @aa.sql
sqlplus a/b@d @aa.sql
sqlplus a/b@e @aa.sql
etc.

Re: batch file windows environment [message #429544 is a reply to message #429502] Wed, 04 November 2009 08:28 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thanks for your reply Micheal and Joy..

@Micheal
Can you please share me an example with your logic..I tried to loop but that dint go forward......

@joy
I tried the same before posting but it was connecting to the first DB and executing the file then the command is not going to the second db. I am simply getting sql prompt..

However, i did this task like this....
my batch file connects to first DB then executes a file called 'aa'
so the 'aa' file content is like..

set serveroutput on

begin

dbms_output.put_line('1 here we go );

end;
/

connect gapc/s0lut_1on@apchcp1_cihcispdb019
set serveroutput on
begin
dbms_output.put_line('2 here we go ahah');
end;
/



so in that way i am connecting the new db in the script file rather in the batch file.

any other way please...

Regards,
Pointers
Re: batch file windows environment [message #429557 is a reply to message #429544] Wed, 04 November 2009 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried the same before posting but it was connecting to the first DB and executing the file then the command is not going to the second db.

Just add "exit" at your script file.

Regards
Michel

[Updated on: Wed, 04 November 2009 09:03]

Report message to a moderator

Re: batch file windows environment [message #429558 is a reply to message #429544] Wed, 04 November 2009 09:03 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
You have a typo (missing ')
and to end your aa.sql and go back to the prompt, you have to add "EXIT;":
set serveroutput on

begin
  dbms_output.put_line('1 here we go');
end;
/

connect a/b@c
set serveroutput on
begin
  dbms_output.put_line('2 here we go ahah');
end;
/
exit;
Re: batch file windows environment [message #429560 is a reply to message #429544] Wed, 04 November 2009 09:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
sqlplus is an O/S command

connect is a sqlplus command

which one you use will depend on if you have an EXIT on your script or not. If you exit out of sqlplus at the end of your script then you need to log back in again from the O/S. If you do not exit sqlplus once your script is done then you use CONNECT to switch to the next database. The difference looks like this:

with exit in your script

sqlplus user/pass@db1 @script.sql
sqlplus user/pass@db2 @script.sql
sqlplus user/pass@db3 @script.sql

without exit in your script

sqlplus user/pass@db1 @script.sql
connect user/pass@db2
@script.sql
connect user/pass@db3
@script.sql
exit

Is there an exit in your script somewhere?

Kevin
Re: batch file windows environment [message #429562 is a reply to message #429502] Wed, 04 November 2009 09:11 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
My bad i forgot to mention that in my earlier post..

What happend was if i use 'exit'..it totally closed my session. my window got closed totally...

Regards,
Pointers.
Re: batch file windows environment [message #429563 is a reply to message #429502] Wed, 04 November 2009 09:13 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Let me try your points Kevin...
Re: batch file windows environment [message #429565 is a reply to message #429502] Wed, 04 November 2009 09:20 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thank you all..

Thanks Kevin, your explanation is nice.....You made me clear on my basic doubts on scripting...will remember you....

Regards,
Pointers
Re: batch file windows environment [message #429571 is a reply to message #429502] Wed, 04 November 2009 10:24 Go to previous messageGo to next message
pointers
Messages: 451
Registered: May 2008
Senior Member
Hi,

I tried both given by kevin

sqlplus un/pw@db1 @file.sql
sqlplus un/pw@db2 @file.sql
sqlplus un/pw@db3 @file.sql

worked fine with 'exit'..

but the below failed to go forward
it connected to first db and then prompted to sql, not moving forward to connect db2...
sqlplus un/pw@db1 @file.sql
connect un/pw@db2
@file.sql
connect un/pw@db3



but if i add 'connect un/pw@db2' in the script file i.e. @file.sql
then, it is able to connect to 'db2'....

But why not from the batch file directly..

Regards,
Pointers
Re: batch file windows environment [message #429577 is a reply to message #429571] Wed, 04 November 2009 11:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
sqlplus is an O/S command.

connect inside sqlplus is a sqlplus command.

connect only works inside of a sqlplus session. Thus if your script exits back to the O/S, connect cannot work.

Of course you found this out yourself when you figured out that you needed to put the connects in a certain place so that the connect command was executed in the context of a sqlplus session and not an O/S session.

Good work, Kevin

[Updated on: Wed, 04 November 2009 11:07]

Report message to a moderator

Re: batch file windows environment [message #429650 is a reply to message #429502] Thu, 05 November 2009 00:40 Go to previous message
pointers
Messages: 451
Registered: May 2008
Senior Member
Thanks Kevin......
Previous Topic: Oracle 11g Installation Blues
Next Topic: ORA-12637
Goto Forum:
  


Current Time: Fri Mar 29 09:43:11 CDT 2024