Home » Infrastructure » Windows » handling errors in batch file
handling errors in batch file [message #353357] Mon, 13 October 2008 09:17 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
ihave a .bat file, in which i am using sqlldr to load into a staging table from csv file, and then using merge to insert / update into target table

i have 2 doubts
1) my first doubt is regarding batch script..how to handle the errorlevels which are greater than 1?
so generally speaking, how do i handle all the errors at once..and make sure the control goes to
run_sqlldr0 always? and handle the error meaningfully if its other than 0 ?

below is my batch script, and i get error
goto run_sqlldr2
the system cannot find the batch label specified - run_sqlldr2




@echo on

call oraenv.bat


if not exist %mainpath% goto mainpath_def
if not exist %scripts% goto scriptspath_def
if not exist %logs% goto logspath_def
if not exist %data% goto datapath_def

goto x1

:x1
goto run_sqlldr%ERRORLEVEL%

echo run_sqlldr%ERRORLEVEL%

if run_sqlldr%ERRORLEVEL% NEQ 0 goto run_sqlldr1
echo "entering sqlldr"

:run_sqlldr1
 echo "entered sqlldr1"
 echo "some error"


echo mergedata%ERRORLEVEL%

:run_sqlldr0
echo "entered sqlldr0"
sqlldr userid = scott/tiger@orcl control=%mainpath%\cm.ctl log=%logs%\cm.log discard=%logs%\cm.dsc bad=%logs%\cm.bad SILENT=(HEADER, FEEDBACK)
goto mergedata%ERRORLEVEL%

:mergedata1
echo "entered mergedata1"


echo  cleandata%ERRORLEVEL%

:mergedata0
 sqlplus -s scott/tiger@orcl @%scripts%\merge_stg.sql 
 
goto cleandata%ERRORLEVEL%


:cleandata1
echo "entered cleandata1"
echo "cleaning error"
goto exit

:cleandata0
sqlplus -s scott/tiger@orcl @%scripts%\clean_stg.sql
goto exit


 :mainpath_def
echo "mainpath not existing as specified %mainpath%, Please edit ORAENV with correct value"
goto exit 

:scriptspath_def
echo "scripts path not existing as specified %scripts%, Please edit ORAENV with correct value"
goto exit  

:logspath_def
echo "Logs path not existing as specified %logs%, Please edit ORAENV with correct value"
goto exit 

:datapath_def
echo "Data path not existing as specified %data%, Please edit ORAENV with correct value"
goto exit  


:exit

2) my second doubt, is this the right way of calling the stored procedures merge_stg and clean_stg?

in clean_stg, i use dynamic sql to truncate the staging table

create or replace procedure trunstg is
 lsql varchar2(2000) := 'truncate table stg_ldrtest';

begin

execute immediate lsql;

end;
/

Report message to a moderator

Re: handling errors in batch file [message #353365 is a reply to message #353357] Mon, 13 October 2008 10:48 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
i have changed the code 


@echo on

call oraenv.bat


if not exist %mainpath% goto mainpath_def
if not exist %scripts% goto scriptspath_def
if not exist %logs% goto logspath_def
if not exist %data% goto datapath_def

goto x1

:x1
goto run_sqlldr%ERRORLEVEL%

echo run_sqlldr errorlevel: %ERRORLEVEL%

if run_sqlldr%ERRORLEVEL% NEQ 0 goto run_sqlldr1
echo "entering sqlldr"

:run_sqlldr1
 echo "entered sqlldr1"
 echo "some error"

 :run_sqlldr2
 echo errorlevel: %errorlevel%



echo mergedata%ERRORLEVEL%

:run_sqlldr0
echo "entered sqlldr0"
sqlldr userid = gautam/gautam@orcl control=%mainpath%\cm.ctl log=%logs%\cm.log discard=%logs%\cm.dsc bad=%logs%\cm.bad SILENT=(HEADER, FEEDBACK)
goto mergedata%ERRORLEVEL%

:mergedata1
echo "entered mergedata1"
goto end


echo  cleandata%ERRORLEVEL%

:mergedata0
 sqlplus -s gautam/gautam@orcl @%scripts%\merge_stg.sql 
 
goto cleandata%ERRORLEVEL%


:cleandata1
echo "entered cleandata1"
echo "cleaning error"
goto end

:cleandata0
sqlplus -s gautam/gautam@orcl @%scripts%\clean_stg.sql
goto end


 :mainpath_def
echo "mainpath not existing as specified %mainpath%, Please edit ORAENV with correct value"
goto exit 

:scriptspath_def
echo "scripts path not existing as specified %scripts%, Please edit ORAENV with correct value"
goto exit  

:logspath_def
echo "Logs path not existing as specified %logs%, Please edit ORAENV with correct value"
goto exit 

:datapath_def
echo "Data path not existing as specified %data%, Please edit ORAENV with correct value"
goto exit  


:end
EXIT



so the error is ORA - 30926, unable to get a stable set of
rows in the source tables

my merge is

MERGE INTO ldrtest D
   USING (SELECT code,id,row_name,col1,col2,col3,crt_tm,md_tm,crt_user,mod_usr FROM stg_ldrtest) S
      ON (d.code = s.code and d.id = s.id and d.row_name=s.row_name )
   WHEN MATCHED THEN UPDATE SET  d.col1 = s.col1,
                                d.col2 = s.col2,
                                d.col3 = s.col3,
                                 d.crt_tm = s.crt_tm,
                                d.md_tm = s.md_tm,
                                d.crt_user = s.crt_user,
                                d.mod_usr = s.mod_usr
        WHEN NOT MATCHED THEN INSERT (d.code,d.id,d.row_name,d.col1,d.col2,d.col3,d.crt_tm,d.md_tm,d.crt_user,d.mod_usr)
     VALUES (s.code,s.id,S.row_name, s.col1,s.col2,s.col3,s.crt_tm,s.md_tm,s.crt_user,s.mod_usr);


i did not understand what this error means..

could anyone please help me out

Report message to a moderator

Re: handling errors in batch file [message #353368 is a reply to message #353357] Mon, 13 October 2008 10:59 Go to previous message
ora1980
Messages: 251
Registered: May 2008
Senior Member
ok i solved that oracle error, it was because my target table
did not have the unique indexes my source table had

but the question still remains..how to handle errors in
a batch file?

Report message to a moderator

Previous Topic: How to configure Oracle 10g for XP sample schemas
Next Topic: Disk Utilization for 0 C: D: is 646.39%, crossed warning (80) or critical (95) threshold
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 24 06:16:45 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.