Windows 7 Batch File will not execute multiple moves

[Lisa Brooks]

Please Help! I have a batch file that performs the following tasks:
1) renames a counter field
2) creates two variables that will be used later to rename two separate files
3) Opens SQL*Plus and starts an Oracle Script which starts two separate stored procedures
4) Moves and renames the subsequent spooled files from both procedures

The first spooled file moves and renames with no problem. The second one (which has a job that is more labor intensive) does not move or rename. I have tried adding a TIMEOUT and making sure that the folders have the right permissions. Still nothing. My code for the batch file and the Oracle script are attached below.

Batch File
------------------------------------------------
@echo off
setlocal enabledelayedexpansion
set filename="E:\Transfers\XXX\prod_directory\pullsched_seq2.dat"
if exist "%filename%" del "%filename%"
for /f %%A in (E:\Transfers\XXX\prod_directory\pullsched_seq.dat) do (
set num=%%A
set /a num+=1
echo !num!>>"%filename%"
)
set newnum=!num!
set newproc=schedule%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%%TIME:~0,2%%TIME:~3,2%.dat
del "E:\Transfers\XXX\prod_directory\pullsched_seq.dat"
rename "E:\Transfers\XXX\prod_directory\pullsched_seq2.dat" "pullsched_seq.dat"
rem start Oracle Script
E:\oracle\product\10.2.0\db_1\BIN\SQLPLUSW.EXE
logon/password @database @C:\Schedule\pullschedule0325.sql
move C:\Schedule\schedule_today.txt E:\Transfers\ftpadmin\archive\schedule\%newnum%
(this move works)
move C:\Schedule\schedule_today2.txt E:\Transfers\XXX\output_directory\%newproc%
(this one does not)


Oracle Script (@C:\Schedule\pullsched0325.sql)
-------------------------------------------------------------------
rem @C:\Schedule\pullsched0325.sql
set term off
set feedback off
set verify off
set echo off
set heading off
set serveroutput on size 1000000
set linesize 174
spool C:\Schedule\schedule_today.txt
execute SBT_SCHEDULE_LOAD_DATA
spool C:\Schedule\schedule_today2.txt
execute SBT_SCHEDULE_LOAD_SCHEDULES
exit
/
------------------------------------------

 

[ChatGPT]

Based on your description, the issue seems to be related to the second spooled file not being moved or renamed after running the Oracle script using the batch file. Here are a few troubleshooting steps and suggestions to address this problem:

1. Check File Paths:
   • Ensure that the output file "schedule_today2.txt" is generated in the correct directory specified in the Oracle script.
   • Make sure that the Oracle script is successfully generating and closing the spooled files before attempting to move them.
   []Explicit Paths in Batch File:
   • Consider using full paths for the move command within the batch file to ensure there are no path-related issues.
   • Modify the batch file commands for moving the files to specify the exact paths for both files.
   []Delay Before File Operation:
   • Introduce a slight delay after executing the SQL script to allow enough time for the spooled file creation to complete before attempting to move or rename it. You can include a timeout command in the batch file to introduce a delay.
   []Logging and Error Handling:
   • Add logging statements in your batch file to track the progress and identify potential issues.
   • Include error handling mechanisms to capture any errors that might be occurring during the move operation.
   []Batch File Closing After SQL*Plus:
   • Ensure that the batch file doesn't close immediately after launching SQLPlus. You might need to wait until SQLPlus completes the script execution before proceeding with the file operations. By incorporating these steps and ensuring that the paths, timing, and execution flow are accurate, you should be able to troubleshoot why the second spooled file is not being moved or renamed successfully as part of your batch file operation.