1

I have a scheduled task with a .bat file that downloads some files from a web server every day by the morning then process the data and UPDATES a database. Then it triggers another .bat file to SELECT data and EXPORT to a .xls file.

The second .bat file is like this:

set a=%date:/=-%
del /q F:\file_path\file1_%a%.xls
del /q F:\file_path\file2_%a%.xls
echo %time%_%date%

cd /D D:\oracle\product\10.2.0\db_1\BIN
sqlplus usrname/psswd@ORCL @F:\select_path\select1.sql
timeout /t 30 /nobreak > nul
ren F:\file_path\file1.xls file1_%a%.xls

sqlplus usrname/psswd@ORCL @F:\select_path\select2.sql
timeout /t 30 /nobreak > nul
ren F:\file_path\file2.xls file2_%a%.xls


cd /D F:\KMB-SP\TI\Scripts\script_select

::Command to send file1 and file2 via e-mail.

But when I arrive at the office and check the progress, only the first .xls is done. So I have to run the second .bat manually and it runs perfectly.

What could be causing this?

Notes:

  1. I put the timeout between the two SELECTs because, in the past, the code was stopping after the INSERT and didn't trigger the second .bat . My colleague said it could be execution exception. Puting a timeout would give time to end the INSERT properly.
  2. Before, it used to make both SELECTs and then rename both files. Doing so, sometimes it worked, sometimes not, then I tried to change the order: select1, rename1, select2, rename2.
  3. As we download files everyday, we concatenate the data on a single file called DT-date. The first code goes like this:
rem The data is downloaded and the files are organized in their files
if exist F:\path\DT-date (
    Data_consolidation.exe
    timeout /t 300 /nobreak > nul
    F:\path\second_bat.bat
) else (exit)
  1. As @William Robertson said, I tried echo exit right after the first SELECT, but again, it only extracted the first file and not the second one.
Luis Fernando
  • 25
  • 6
  • 1
    Presumably the `.sql` scripts are spooling the output, and end with `spool off` if you can see it. But do they have `exit` a the end? If not SQL\*Plus will just wait for input, unless killed. – Alex Poole Jan 24 '20 at 15:06
  • Instead of `D:` and `cd D:\...` you could simply use `cd /D D:\...`... – aschipfl Jan 24 '20 at 15:29
  • And just to help clarify the above, instead of `F:` then `cd F:\…` you could use `CD /D F:\…`, open up a Command Prompt window and enter `cd /?` or `help cd` to read the usage information for the `CD` command. BTW, you said that it triggers another batch file, what does and when? we probably need a better understanding of the process. If the other bat is run somewhere in between the two selects, then it follows that that isn't being called and/or is exited to prevent returning to the next portion of your script. We really do need to know what happens, and how, in the bit in between selects! – Compo Jan 24 '20 at 16:10
  • How does the first .bat file script "trigger" the second .bat file script? Can you show that code? – lit Jan 24 '20 at 17:05
  • If the issue is that SQL*Plus needs to exit after running a script, you could try `echo exit | sqlplus un/pw@sid @script` – William Robertson Jan 24 '20 at 22:57
  • @LuisFernando - As you can see, putting code in a comment does not work well. Please edit the question and add the code. Thanks. – lit Jan 27 '20 at 16:08
  • @lit here it goes. Thank you for the advice. – Luis Fernando Jan 27 '20 at 16:45
  • @WilliamRobertson I made a new edit. – Luis Fernando Jan 28 '20 at 12:30
  • I’m not sure what you mean by *"I tried `echo exit` right after the first SELECT"*. My suggestion is to place `echo exit |` right before `sqlplus`. – William Robertson Jan 28 '20 at 12:35
  • @WilliamRobertson The code makes two `sqlplus`, so I let `echo exit |` after the first one and before the second one. But I'll write it befor both of the `sqlplus`. – Luis Fernando Jan 29 '20 at 16:49
  • @LuisFernando Did that work? The idea is to pipe the word `exit` into the SQL\*Plus session as described in [this answer](https://stackoverflow.com/a/54168172/230471). – William Robertson Feb 02 '20 at 12:44
  • @WilliamRobertson, yes, it worked! I was waiting a few more days to confirm, and the .xls files are being created automatically! – Luis Fernando Feb 04 '20 at 13:52
  • Also you could use host command ( feature of sqlplus ) inside sql files itself and call windows commands or batch file itself from there – Nizam - Madurai Feb 06 '20 at 09:56

1 Answers1

0

As @WilliamRobertson suggested, writing echo exit | before the sqlplus commands solved the problem.

Luis Fernando
  • 25
  • 6