[swiftconnect]

We have the following PL/SQL SP

CREATE OR REPLACE PROCEDURE Outgoingbackupweekly_Sub (pn_tid IN NUMBER, pn_error_code OUT NUMBER)
AS
CURSOR cur1 IS
SELECT SC_OMH_M_AUDIT.*
FROM SC_OMH_M_AUDIT, SC_OMH_M
WHERE SC_OMH_M.SC_OMH_TRANS_SNO = SC_OMH_M_AUDIT.SC_OMH_TRANS_SNO
AND SC_OMH_M.SC_OMH_TRANS_SNO = pn_tid
AND ((SC_OMH_M.OMH_MSG_STATUS='T' AND SC_OMH_M.OMH_ACKNAK_CODE = 0) OR (SC_OMH_M.OMH_MSG_STATUS='D' AND SC_OMH_M.OMH_STATUS='D'));

CURSOR cur IS
SELECT * FROM SC_OMH_M
WHERE SC_OMH_TRANS_SNO = pn_tid
AND ((omh_msg_status='T' AND OMH_ACKNAK_CODE = 0) OR (omh_msg_status='D' AND omh_status='D'));

PROCEDURE INSERT_STEP_SUB(STEP_DESC IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO BACKUP_STEPS_LOG VALUES('Outgoingbackupweekly_sub '||STEP_DESC, SYSDATE);
COMMIT;
END INSERT_STEP_SUB;
BEGIN
pn_error_code := -1;

INSERT_STEP_SUB('before first loop-->'||pn_tid);
FOR cur_omh_audit IN cur1
LOOP
INSERT INTO SC_OMH_M_AUDIT_OLD VALUES(
cur_omh_audit.OMH_TRANS_NO,
cur_omh_audit.OMH_SYSTEM_ID,
cur_omh_audit.OMH_CNT_NO,
cur_omh_audit.OMH_SEQ_NO,
cur_omh_audit.OMH_MSG_TYPE,
cur_omh_audit.OMH_SENT_BIC,
cur_omh_audit.OMH_RECV_BIC,
cur_omh_audit.OMH_REF_NO,
cur_omh_audit.OMH_STATUS,
cur_omh_audit.OMH_USER_ID,
cur_omh_audit.OMH_AUTH,
cur_omh_audit.OMH_TRANS_DT,
cur_omh_audit.OMH_MSG_STATUS,
cur_omh_audit.OMH_MSG_GEN,
cur_omh_audit.OMH_MSG_GEN_DT,
cur_omh_audit.OMH_MSG_TRM_DT,
cur_omh_audit.OMH_ACKNAK_DT,
cur_omh_audit.OMH_ACKNAK_TM,
cur_omh_audit.OMH_ACKNAK_CODE,
cur_omh_audit.OMH_BR_CODE,
cur_omh_audit.SC_OMH_TRANS_SNO,
cur_omh_audit.OMH_INIT_USER,
cur_omh_audit.OMH_MOD_NO);

DELETE SC_OMH_M_AUDIT WHERE SC_OMH_TRANS_SNO = pn_tid;
END LOOP;

INSERT_STEP_SUB('after first loop-->'||pn_tid);

INSERT_STEP_SUB('before second loop-->'||pn_tid);
FOR cur_sc_omh_m IN cur
LOOP
INSERT_STEP_SUB('Step 1-->'||pn_tid);
INSERT INTO SC_OMH_M_OLD VALUES(cur_sc_omh_m.OMH_TRANS_NO,
cur_sc_omh_m.OMH_SYSTEM_ID,
cur_sc_omh_m.OMH_CNT_NO,
cur_sc_omh_m.OMH_SEQ_NO,
cur_sc_omh_m.OMH_MSG_TYPE,
cur_sc_omh_m.OMH_SENT_BIC,
cur_sc_omh_m.OMH_RECV_BIC,
cur_sc_omh_m.OMH_REF_NO,
cur_sc_omh_m.OMH_STATUS,
cur_sc_omh_m.OMH_USER_ID,
cur_sc_omh_m.OMH_AUTH,
cur_sc_omh_m.OMH_TRANS_DT,
cur_sc_omh_m.OMH_MSG_STATUS,
cur_sc_omh_m.OMH_MSG_GEN,
cur_sc_omh_m.OMH_MSG_GEN_DT,
cur_sc_omh_m.OMH_MSG_TRM_DT,
cur_sc_omh_m.OMH_ACKNAK_DT,
cur_sc_omh_m.OMH_ACKNAK_TM,
cur_sc_omh_m.OMH_ACKNAK_CODE,
cur_sc_omh_m.OMH_BR_CODE,
cur_sc_omh_m.SC_OMH_TRANS_SNO,
cur_sc_omh_m.OMH_INIT_USER,
cur_sc_omh_m.OMH_MOD_NO,
cur_sc_omh_m.MAP_SCH_SNO,
cur_sc_omh_m.OMH_PARENT_TRANS_SNO,
cur_sc_omh_m.OMH_UPDATE_MODE);

UPDATE SC_OMH_M_OLD SET OMH_MSG_GEN = Test_Longtovarchar(cur_sc_omh_m.SC_OMH_TRANS_SNO) WHERE SC_OMH_TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;

INSERT_STEP_SUB('Step 2-->'||pn_tid);
INSERT INTO SC_OMD_T_OLD SELECT SC_OMD_T.* FROM SC_OMD_T WHERE SC_OMD_T.OMD_TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;

INSERT_STEP_SUB('Step 2A-->'||pn_tid);
INSERT INTO SC_OMD_AUDIT_T_OLD SELECT SC_OMD_AUDIT_T.* FROM SC_OMD_AUDIT_T WHERE SC_OMD_AUDIT_T.OMD_TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;

INSERT_STEP_SUB('Step 3-->'||pn_tid);
INSERT INTO SC_AUTH_T_OLD SELECT SC_AUTH_T.* FROM SC_AUTH_T WHERE SC_AUTH_T.TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;

INSERT_STEP_SUB('Step 4-->'||pn_tid);
INSERT INTO SC_FLD_NON_MOD_OLD SELECT SC_FLD_NON_MOD.* FROM SC_FLD_NON_MOD WHERE TO_CHAR(SC_FLD_NON_MOD.TRANS_SNO) = cur_sc_omh_m.SC_OMH_TRANS_SNO;

INSERT_STEP_SUB('Step 5-->'||pn_tid);
INSERT INTO SC_TRANS_TRIGGER_STATUS_OLD SELECT SC_TRANS_TRIGGER_STATUS.* FROM SC_TRANS_TRIGGER_STATUS WHERE SC_TRANS_TRIGGER_STATUS.SC_TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;

INSERT INTO SC_IMH_M_OLD SELECT SC_IMH_M.* FROM SC_IMH_M WHERE SC_IMH_M.IMH_OMH_TRANS_NO = cur_sc_omh_m.OMH_REF_NO;
INSERT INTO SC_IN_BLOCK_T_OLD SELECT SC_IN_BLOCK_T .* FROM SC_IN_BLOCK_T WHERE SC_IN_BLOCK_T.BLK_VALUE = cur_sc_omh_m.OMH_REF_NO;

INSERT_STEP_SUB('Step 8-->'||pn_tid);
DELETE FROM SC_OMD_AUDIT_T WHERE SC_OMD_AUDIT_T.OMD_TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;

INSERT_STEP_SUB('Step 9-->'||pn_tid);
DELETE SC_AUTH_T WHERE SC_AUTH_T.TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;

INSERT_STEP_SUB('Step 10-->'||pn_tid);
DELETE SC_FLD_NON_MOD WHERE SC_FLD_NON_MOD.TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;

INSERT_STEP_SUB('Step 11-->'||pn_tid);
DELETE SC_TRANS_TRIGGER_STATUS WHERE SC_TRANS_TRIGGER_STATUS.SC_TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;

DELETE FROM SC_IN_BLOCK_T WHERE SC_IN_BLOCK_T.BLK_VALUE IN
(SELECT SC_IN_BLOCK_T.BLK_VALUE FROM SC_IN_BLOCK_T WHERE SC_IN_BLOCK_T.BLK_VALUE = cur_sc_omh_m.OMH_REF_NO);

DELETE FROM SC_IMH_M WHERE SC_IMH_M.IMH_OMH_TRANS_NO IN
(SELECT SC_IMH_M.IMH_OMH_TRANS_NO FROM SC_IMH_M WHERE SC_IMH_M.IMH_OMH_TRANS_NO = cur_sc_omh_m.OMH_REF_NO);
INSERT_STEP_SUB('Step 14-->'||pn_tid);

DELETE FROM SC_OMH_M WHERE SC_OMH_M.SC_OMH_TRANS_SNO = cur_sc_omh_m.SC_OMH_TRANS_SNO;
END LOOP;
INSERT_STEP_SUB('after second loop-->'||pn_tid);

COMMIT;
INSERT_STEP_SUB('after commit-->'||pn_tid);
pn_error_code := 0; --Successfully commited one transaction block

EXCEPTION WHEN OTHERS THEN
INSERT_STEP_SUB('EXCEPTION:'||SQLCODE||'-->'||pn_tid);
ROLLBACK;
INSERT_STEP_SUB('after rollback-->'||pn_tid);
pn_error_code := SQLCODE;
END;
/

This compiles fine in our env but throws compilation errors in the production env. We are using Oracle 9i and Toad 7.3

Any help is appreciated...

[Littlefoot]

Which error is it? I do not want to download and open a DOC file from an unknown source. You'd rather copy and paste SQL*Plus session.

[swiftconnect]

We have attached the JPG files for the individual errors. We had requested the client to excute the script in SQL Plus also and send us the results. Awaiting the reply from client...

[Littlefoot]

Well, viewing screenshots and the procedure code, perhaps it is TOAD 7.3 (a rather old version) to blame. I wrote something like your procedure (a simple example based on Scott's schema), executed it through TOAD 8.6 and it worked correctly.

What you might try to do is to move the 'insert_step_sub' procedure OUT of the 'outgoingbackupweekly_sub' and create it as a standalone procedure (or even comment those lines just to see how it behaves). If you ask "why" to do that - the third screenshot "told" me so (which, of course, doesn't have to mean anything).