I created a job sequences that fire off a SQL script.
Set CURRDATE=%TEMP%\CURRDATE.TMP
DATE /T > %CURRDATE%
Set PARSEARG="eol=; tokens=1,2,3,4* delims=/, "
For /F %PARSEARG% %%i in (%CURRDATE%) Do SET YYYYMMDD=%%l%%j%%k
CD D:\DATAST~1\DEV\SHELL\LANDSO~1
SQLPLUS LANDSO_MART/LANDSO_MART@EDWDEV1.INAC.GC.CA @"SUB_Activity Breakout RUN Script" > %YYYYMMDD%_Breakout.0.log
EXIT
When it returns from the execution, I get
TESTSCRIPT..JobControl (@LANDSO_SQL_SCRIPTS): Executed: D:\DataSt~1\DEV\Shell\LANDSO~1\LANDSO.bat
Reply=1
Output from command ====>
Error 45 initializing SQL*Plus
Internal error
The script ran fine but the return error cause the sequence to abort. To fool the job sequence, I set the Condition to fail in order to run the subsequent job. I would rather have the SQL return 0.
Could someone please provide some assistance.
Thanks
Job Sequence - Script Initialization Error
Moderators: chulett, rschirm, roy
Job Sequence - Script Initialization Error
Jim Stewart
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Given that the error message came from the BAT file itself, you really should be looking for the "culprit" there. For example are there any exit statements that generate an ERRORLEVEL value?
One workaround would be to call this BAT file from another, which intercepts the non-zero exit status and exits with a zero error code where relevant to do so.
But, if sqlplus could not be initialized, how can you say that the script ran successfully?!!
One workaround would be to call this BAT file from another, which intercepts the non-zero exit status and exits with a zero error code where relevant to do so.
But, if sqlplus could not be initialized, how can you say that the script ran successfully?!!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I generate an Output report that show the statistics from the SQL. It returned with everything running normally but then throws an exception. I insert an EXIT in the SQL thinking that it wasn't closing SQLPLUS before returning back to DS. It did not work. I googled the error with limited results.
I thought that if I used the UnConditional Statement, the job would continue. The exception seems to be more related to the BAT file and DS doesn't like it.
I was hoping someone else may of had this problem.
I thought that if I used the UnConditional Statement, the job would continue. The exception seems to be more related to the BAT file and DS doesn't like it.
I was hoping someone else may of had this problem.
Jim Stewart
I added the following command to the end of my SQL Script.
IF %ERRORLEVEL% == 0 goto EXIT
:EXIT
and I got a returm code of 0.
It solved my problem.
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 26 10:08:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
'INSERTPLANNINGPAGESECTION'
----------------------------
INSERT PLANNING PAGE SECTION
50 rows deleted.
Commit complete.
12 rows created.
'INSERTMONITORINGPAGESECTION'
------------------------------
INSERT MONITORING PAGE SECTION
26 rows created.
'INSERTREPORTINGPAGESECTION'
-----------------------------
INSERT REPORTING PAGE SECTION
12 rows created.
'PLANNINGSECTION
----------------
PLANNING SECTION
'DELETERECORDSPLANNINGSECTION'
-------------------------------
DELETE RECORDS PLANNING SECTION
48 rows deleted.
Commit complete.
'INSERTSTRATEGICRECORDSPLANNINGSECTION'
-----------------------------------------
INSERT STRATEGIC RECORDS PLANNING SECTION
12 rows created.
'INSERTREPORTONPLANSRECORDSPLANNINGSECTION'
-----------------------------------------------
INSERT REPORT ON PLANS RECORDS PLANNING SECTION
12 rows created.
'INSERTBUDGETRECORDSPLANNINGSECTION'
--------------------------------------
INSERT BUDGET RECORDS PLANNING SECTION
12 rows created.
'INSERTREGIONALPLANSRECORDSPLANNINGSECTION'
----------------------------------------------
INSERT REGIONAL PLANS RECORDS PLANNING SECTION
12 rows created.
'DROPANDCREATETABLEPLAN_PRIO'
-------------------------------
DROP AND CREATE TABLE PLAN_PRIO
12 rows deleted.
'INSERTINTOTABLEPLAN_PRIO'
---------------------------
INSERT INTO TABLE PLAN_PRIO
12 rows created.
'DROPANDCREATETABLEPLAN_RPP'
------------------------------
DROP AND CREATE TABLE PLAN_RPP
24 rows deleted.
Commit complete.
'INSERTINTOTABLEPLAN_RPP'
--------------------------
INSERT INTO TABLE PLAN_RPP
24 rows created.
'DROPANDCREATETABLEPLAN_BUD'
------------------------------
DROP AND CREATE TABLE PLAN_BUD
12 rows deleted.
Commit complete.
'INSERTINTOTABLEPLAN_RPP'
--------------------------
INSERT INTO TABLE PLAN_RPP
12 rows created.
'DROPANDCREATETABLEREG_PLAN'
------------------------------
DROP AND CREATE TABLE REG_PLAN
12 rows deleted.
Commit complete.
12 rows created.
'DROPANDCREATETABLEMONITORING'
--------------------------------
DROP AND CREATE TABLE MONITORING
62 rows deleted.
Commit complete.
'INSERTINTOTABLEMONITORING'
----------------------------
INSERT INTO TABLE MONITORING
26 rows created.
12 rows created.
12 rows created.
12 rows created.
'DROPANDCREATETABLEAUDIT_EVAL'
--------------------------------
DROP AND CREATE TABLE Audit_Eval
24 rows deleted.
Commit complete.
'INSERTINTOTABLEAUDIT_EVAL'
----------------------------
INSERT INTO TABLE AUDIT_EVAL
12 rows created.
12 rows created.
'DROPANDCREATETABLEREPORTING'
-------------------------------
DROP AND CREATE TABLE REPORTING
36 rows deleted.
Commit complete.
'INSERTINTOTABLEREPORTING'
---------------------------
INSERT INTO TABLE REPORTING
12 rows created.
12 rows created.
12 rows created.
'DROPANDCREATETABLEREGION_REPORT'
-----------------------------------
DROP AND CREATE TABLE REGION_REPORT
12 rows deleted.
Commit complete.
'INSERTINTOTABLEREGIONAL_REPORT'
---------------------------------
INSERT INTO TABLE REGIONAL_REPORT
12 rows created.
'DROPANDCREATETABLEFINANCIAL'
-------------------------------
DROP AND CREATE TABLE FINANCIAL
12 rows deleted.
Commit complete.
'INSERTINTOTABLEFINANCIAL'
---------------------------
INSERT INTO TABLE FINANCIAL
12 rows created.
Commit complete.
SQL>
IF %ERRORLEVEL% == 0 goto EXIT
:EXIT
and I got a returm code of 0.
It solved my problem.
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 26 10:08:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
'INSERTPLANNINGPAGESECTION'
----------------------------
INSERT PLANNING PAGE SECTION
50 rows deleted.
Commit complete.
12 rows created.
'INSERTMONITORINGPAGESECTION'
------------------------------
INSERT MONITORING PAGE SECTION
26 rows created.
'INSERTREPORTINGPAGESECTION'
-----------------------------
INSERT REPORTING PAGE SECTION
12 rows created.
'PLANNINGSECTION
----------------
PLANNING SECTION
'DELETERECORDSPLANNINGSECTION'
-------------------------------
DELETE RECORDS PLANNING SECTION
48 rows deleted.
Commit complete.
'INSERTSTRATEGICRECORDSPLANNINGSECTION'
-----------------------------------------
INSERT STRATEGIC RECORDS PLANNING SECTION
12 rows created.
'INSERTREPORTONPLANSRECORDSPLANNINGSECTION'
-----------------------------------------------
INSERT REPORT ON PLANS RECORDS PLANNING SECTION
12 rows created.
'INSERTBUDGETRECORDSPLANNINGSECTION'
--------------------------------------
INSERT BUDGET RECORDS PLANNING SECTION
12 rows created.
'INSERTREGIONALPLANSRECORDSPLANNINGSECTION'
----------------------------------------------
INSERT REGIONAL PLANS RECORDS PLANNING SECTION
12 rows created.
'DROPANDCREATETABLEPLAN_PRIO'
-------------------------------
DROP AND CREATE TABLE PLAN_PRIO
12 rows deleted.
'INSERTINTOTABLEPLAN_PRIO'
---------------------------
INSERT INTO TABLE PLAN_PRIO
12 rows created.
'DROPANDCREATETABLEPLAN_RPP'
------------------------------
DROP AND CREATE TABLE PLAN_RPP
24 rows deleted.
Commit complete.
'INSERTINTOTABLEPLAN_RPP'
--------------------------
INSERT INTO TABLE PLAN_RPP
24 rows created.
'DROPANDCREATETABLEPLAN_BUD'
------------------------------
DROP AND CREATE TABLE PLAN_BUD
12 rows deleted.
Commit complete.
'INSERTINTOTABLEPLAN_RPP'
--------------------------
INSERT INTO TABLE PLAN_RPP
12 rows created.
'DROPANDCREATETABLEREG_PLAN'
------------------------------
DROP AND CREATE TABLE REG_PLAN
12 rows deleted.
Commit complete.
12 rows created.
'DROPANDCREATETABLEMONITORING'
--------------------------------
DROP AND CREATE TABLE MONITORING
62 rows deleted.
Commit complete.
'INSERTINTOTABLEMONITORING'
----------------------------
INSERT INTO TABLE MONITORING
26 rows created.
12 rows created.
12 rows created.
12 rows created.
'DROPANDCREATETABLEAUDIT_EVAL'
--------------------------------
DROP AND CREATE TABLE Audit_Eval
24 rows deleted.
Commit complete.
'INSERTINTOTABLEAUDIT_EVAL'
----------------------------
INSERT INTO TABLE AUDIT_EVAL
12 rows created.
12 rows created.
'DROPANDCREATETABLEREPORTING'
-------------------------------
DROP AND CREATE TABLE REPORTING
36 rows deleted.
Commit complete.
'INSERTINTOTABLEREPORTING'
---------------------------
INSERT INTO TABLE REPORTING
12 rows created.
12 rows created.
12 rows created.
'DROPANDCREATETABLEREGION_REPORT'
-----------------------------------
DROP AND CREATE TABLE REGION_REPORT
12 rows deleted.
Commit complete.
'INSERTINTOTABLEREGIONAL_REPORT'
---------------------------------
INSERT INTO TABLE REGIONAL_REPORT
12 rows created.
'DROPANDCREATETABLEFINANCIAL'
-------------------------------
DROP AND CREATE TABLE FINANCIAL
12 rows deleted.
Commit complete.
'INSERTINTOTABLEFINANCIAL'
---------------------------
INSERT INTO TABLE FINANCIAL
12 rows created.
Commit complete.
SQL>
Jim Stewart