OraFAQ Forum: Windows » SQL Statement inside DOS Bat File

Home » Infrastructure » Windows » SQL Statement inside DOS Bat File

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Statement inside DOS Bat File [message #396360]

Mon, 06 April 2009 05:13

bholeuday
Messages: 28
Registered: April 2009
Location: Talavali

Junior Member

Hi,

My Batch File is like Below

set echo off
set path=%path%; P:\061008\Release_Management_Process\06_04_09;
sqlplus cfgcent/cfgcent_3@dcs1d @P:\061008\Release_Management_Process\06_04_09\get_env.sql
disc
sqlplus invctl_doc/babel_3@dcs1d @P:\061008\Release_Management_Process\06_04_09\run.sql
exit;

Where as get_env.sql contains :-
col ts new_value ts
col ss new_value ss
prompt Please Enter Environment Details Options are
prompt ricdb
prompt cfgcent
prompt cmd
prompt wld
select tab_tablespace ts, IND_TABLESPACE ss from settings_ud where env_name = '&Env';
disc;

And Run.sql contains :-
SPOOL P:\061008\Release_Management_Process\06_04_09\ttt.log;
@P:\061008\Release_Management_Process\06_04_09\ttt.sql;
SPOOL OFF;
SPOOL P:\061008\Release_Management_Process\06_04_09\ind_ttt.log;
@P:\061008\Release_Management_Process\06_04_09\ind_ttt.sql;
SPOOL OFF;
QUIT;

My requirement is after running get_env, bat should ask value for the Environment on which deployment is to be done i.e, run to be executed, suppose options are ...
1 ricdb
2 cmd
3 wld

If the User has given cmd then logic should be

if (option ==2) then
sqlplus org/****@fgb1d
\Release_Management_Process\06_04_09\run.sql
exit;

elsif (option ==2) then
sqlplus org/****@fgb1d
\Release_Management_Process\06_04_09\run.sql
exit;

Likewise.....

Please guide me over this as i havent created bat file before this.

Thanks in advance

Report message to a moderator

Re: SQL Statement inside DOS Bat File [message #396362 is a reply to message #396360]

Mon, 06 April 2009 05:29

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This question bears no connection with Oracle.
Please find a more appropriate forum like a Windows one.
The topic is locked.

Regards
Michel

Report message to a moderator

Re: SQL Statement inside DOS Bat File [message #396365 is a reply to message #396362]

Mon, 06 April 2009 05:53

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

In my opinion, it's just the opposite - as it talks about "SQLPLUS", "SPOOL", "SELECT" etc., it has something to do with Oracle. True, it could have been something else instead of invoking SQL*Plus and answer could have been provided in one of MS Windows forums, but - I believe that it deserves a chance of answering here as well. Therefore, I have unlocked it.

Report message to a moderator

Re: SQL Statement inside DOS Bat File [message #396372 is a reply to message #396365]

Mon, 06 April 2009 06:14

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The question has nothing to do with Oracle, it is how to make branch in a DOS shell script.
It is clearly said that SPOOL and SELECT are in a SQL file and is not part of the question.
SQLPLUS could be DEL, MOVE, TYPE or any DOS command or program, even ECHO.

If OP removes all the irrelevant stuff from his question, it becomes:
How could I echo something if user chooses option 1 and other thing if he chooses option 2 and so on?
which has really nothing with Oracle and should be better answered in a Windows forum.

Regards
Michel

Report message to a moderator

Re: SQL Statement inside DOS Bat File [message #396378 is a reply to message #396372]

Mon, 06 April 2009 06:50

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I agree with everything you've said, except the part that the topic should be locked. It should not, because - once the OP's question is answered - he'll be able to do his job which involves Oracle.

In a case of doubt, we have agreed to leave such questions open. So yes (as I've said, and you've said that too): it could have been MOVE, TYPE, DEL or any other operating system commands. But it is not - it is SQLPLUS. SQLPLUS invokes command line Oracle tool, his script does something with the Oracle database and - in my opinion - should be left alone and, possibly, answered.

I, unfortunately, wouldn't know the answer without studying DOS batch scripting.

Report message to a moderator