Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    2

    Unanswered: PL/SQL SP throws ORA-00904

    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...
    Attached Thumbnails Attached Thumbnails err1.JPG   err2.JPG   err3.JPG   err4.JPG  
    Last edited by swiftconnect; 10-10-07 at 03:10.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  3. #3
    Join Date
    Oct 2007
    Posts
    2
    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...

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •