| batch file windows environment [message #429502] |
Wed, 04 November 2009 04:51  |
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)
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 #429544 is a reply to message #429502] |
Wed, 04 November 2009 08:28  |
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 #429558 is a reply to message #429544] |
Wed, 04 November 2009 09:03 |
_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 #429571 is a reply to message #429502] |
Wed, 04 November 2009 10:24 |
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 |
 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
