Calling an oracle script from within batch file and vice versa

Hi All,

Well cmd commands, sql scripts and task scheduler on windows are great tools when used with sharp thinking to automate literally anything that you perform manually in any project .

Recently i was automating a process on windows operating system, and created a bat file start.bat containing the code:

    sqlplus
    sysadm/sysadm@mydb
    @d:\script.sql
    f:
    cd f:\afolder
    second.bat

When i run the batch file, it executed the first two lines and stuck there . It never run the sql script and the other bat file .I wondered what went wrong .

After a min of brainstorming i could find the reason. cmd and sqlplus are two different programs. after two lines i was connected to database with sqlplus, control was with sqlplus program not with cmd.exe. And the other lines were supposed to be run by the cmd program  since bat file was given to it to execute. Therefore until sqlplus does not return the control to cmd, it will not go further two lines and wait for you to enter any sql command on sqlplus . If we enter exit, that program terminates and control is returned  to cmd and it treats the third line as cmd command and opens the file in notepad .

So if we are to run a script from within a bat file, it has to be passed to sqlplus as parameter on single line like :

    sqlplus sysadm/sysadm@mydb @d:\script.sql
    f:
    cd f:\afolder
    second.bat

and make sure the sql script has an exit command so that after the script is run, control is returned to cmd to execute rest of the code it was given in bat file .


*************
if you are to run a cmd command in a sql script , you can use the host/ho command before the command. Its treated like cmd command if preceded by host command. like in a sql script you might have :

    insert into ...
    ho d:
    ho cd d:\afolder
    ho cd start.bat
    update table ...

and have an exit written at the end of bat to return the control to sqlplus to further execute the script lines.