Skip to main content

Search

Welcome to Tibero GTS!

INTERVAL Execution Omission Issue When Using DBMS_JOB.SUBMIT

Document Type | Troubleshooting

Category | Administration

Applicable Product Versions | 6FS06, 6FS07, 7FS02

Document Number | TADTS030

 

Issue

When executing a job with an INTERVAL using DBMS_JOB.SUBMIT, the job appears as SUCCESS in DBA_JOB_HISTORY, but in reality, it is not applied.
(On the other hand, when applied directly through DBMS_JOB.RUN, it executes normally.)
 
 
 
 

Cause

When in the WTHR state waiting for job assignment, if signals such as STMT_CANCEL or SESS_KILL for the CHK_SESSKILL condition are sent, there is no logic to separately initialize the error stack, so the error remains continuously.
 

 
 

Solutions

1. Apply the patch. (Applied patch: 297861a)

Note
Apply the patch through technical support provided by TmaxTibero.
 

2. You can attempt to resolve the error by setting a workaround.

  • Workaround: It is possible to temporarily fix the issue by artificially leaving an error code to initialize the error stack.
 
Below is the procedure to apply the workaround and resolve the issue.

1) Modify the contents within PSM

RAISE_APPLICATION_ERROR(-20000, 'CLEAR ERROR STACK'); The error code between -20000 and -20999 is a user-defined error code, and any value within this range can be used.

CREATE OR REPLACE PROCEDURE TIBERO."JOB_TEST" 
IS 
errCode            VARCHAR2 (20); 
errMsg             VARCHAR2 (500); 
BEGIN 
RAISE_APPLICATION_ERROR(-20000, 'CLEAR ERROR STACK'); 
    DELETE FROM TIBERO.BOOKS_LINK; 
    INSERT INTO TIBERO.BOOKS_LINK 
( 
 CTRT_ACCT_BOOK_MNG_NO 
 , FIS_YEAR 
 , EXP_FG 
โ€ฆ(omitted)
 

2) Execution Result

tbSQL 6

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

SQL> select * from dba_job_history where job=16;
 

JOB     SCHEMA_USER    START_DATE               END_DATE          RESULT     COMMENTS 
------  -----------    --------------------- -------------------- ---------  -------------------- 
16           18         20230428 14:38:30    20230428 12:02:45    FAIL       ERROR CODE= -20000 
16           18         20230428 14:40:08    20230428 14:42:50    SUCCESS
 

3) How to comment out after execution

After running the job once including RAISE_APPLICATION_ERROR, comment it out.

CREATE OR REPLACE PROCEDURE TIBERO."JOB_TEST" 
IS 
errCode            VARCHAR2 (20); 
errMsg             VARCHAR2 (500); 
BEGIN 
--RAISE_APPLICATION_ERROR(-20000, 'CLEAR ERROR STACK'); 
    DELETE FROM TIBERO.BOOKS_LINK; 
    INSERT INTO TIBERO.BOOKS_LINK 
( 
 CTRT_ACCT_BOOK_MNG_NO 
 , FIS_YEAR 
 , EXP_FG 
โ€ฆ(omitted) 

 

tbSQL 6

TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.

SQL> select * from dba_job_history where job=16;
 

JOB     SCHEMA_USER    START_DATE               END_DATE          RESULT     COMMENTS 
------  -----------    --------------------- -------------------- ---------  -------------------- 
16           18         20230428 14:38:30    20230428 12:02:45    SUCCESS 
16           18         20230428 14:40:08    20230428 14:42:50    SUCCESS
 
 
 
 
 
 
 
 
 

Related to