Need help with batch script with Oracle

[jlakhani]

My Batch script keeps on dying and I cant figure out where

load_sop_data.cmd
goto GET_DATE

:environment
Rem Environment setup
set ORACLE_SID=qbip

echo "Oracle_sid is"
echo %oracle_sid% >> SOPLog.txt
echo " Checking for SOP csv file " >> SOPLog.txt
echo " Setting ORACLE_SID " >> SOPLog.txt
echo "Check for SOPEMPLOYEES file"

if not exist c:\jigar\sopemployees.csv
echo "File SOPEMPLOYEES not available in defined directory"
GOTO END
else
REM: Do the following

echo "Connect to database"
sqlplus -s internal @truncatesop.sql
sqlplus -s internal @loadsopdata.sql
exit;

:GET_DATE
sqlplus -s internal @getdate.sql
goto Environment

:END
echo "no files"
exit

getdate.sql
spool SOPLOG.txt
select sysdate from dual;


truncatesop.sql
spool c:\jigar\truncatesop.txt
conn sop\sop;
set serveroutput on
truncate sop_employees;
select count(*) from sop_employees
/
commit;
exit

loadsopdata.sql
spool c:\jigar\loadsop.txt
exec load_sop_data
select count(*) from sop_employees
/
commit;
exit

can you tell me what i am doing wrong?

[stecal]

This part isn't wrong, but it is redundant:

truncatesop.sql
spool c:\jigar\truncatesop.txt
conn sop\sop;
set serveroutput on
truncate sop_employees;
select count(*) from sop_employees
/
commit;


If you truncate a table, what do you expect to have left in it for data? So why bother counting how many rows are in it? Truncate also does an implicit commit, so why are you committing?

[davey23uk]

one thing I notices was this bit

echo "Connect to database"
sqlplus -s internal @truncatesop.sql
sqlplus -s internal @loadsopdata.sql
exit;

You exit out of nothing because your loadsopdata.sql does thr exit from sqlplus for you

Not sure if this will cause it to die though

You will also need a table keyword in the truncate bit (you currectly have truncate sop_employees instead of truncate table sop_employees)

[stecal]

When do you exit out of sqlplus in get_date?

[stecal]

Originally posted by davey23uk

You will also need a table keyword in the truncate bit (you currectly have truncate sop_employees instead of truncate table sop_employees)

Good catch on that one too.