5 Replies Latest reply: Jan 2, 2013 8:44 AM by Marwim RSS

    How to add the function in anonymous block and debugg

    959687
      Hi, friends, kindly help me out to debug this function in anonymous block for this constraint error, so i want to identofy exactly inwhich statement, i am getting the constraint error.. so i have to debug in anonymous block so i dont no how declare, and whr to include begin and end keyword, to find the problem..so plz help me

      {lineno 164768:112612  20:51:17:AQ: XOP12083212969:Failed during processing,
      check the log. SQLCode: ORA-00001: unique constraint (XXX$CMS.X2_BOP_FAILED_ORDERS)
      violatedSYS.ppr_enh_retorders_cb}


      {FUNCTION fn_get_retail_id (p_incr PLS_INTEGER DEFAULT 1) RETURN PLS_INTEGER IS
                  p_trail_id      tb_xop_orders_trail.trail_id%TYPE;
              BEGIN
                  IF p_incr >= 1 THEN
                      SELECT sq_xop_trail_id.NEXTVAL INTO p_trail_id FROM DUAL;
                      RETURN p_trail_id;
                  ELSIF p_incr = 0 THEN
                      SELECT sq_xop_trail_id.CURRVAL INTO p_trail_id FROM DUAL;
                      RETURN p_trail_id;
                  END IF;
              END;

      ----------------------------------------------------------------------------------------------------------- --
          BEGIN

      --        p_xop_log_errors('=========== cb started =========');

              pk_xop_exsoputils.register(v_program);

              IF c_trail > 0 THEN
                  v_trail_info.DELETE;
              END IF;

              -- Set the message options
              v_dqopt.WAIT := DBMS_AQ.NO_WAIT;
              v_dqopt.msgid := descr.msg_id;
              v_dqopt.consumer_name := descr.consumer_name;
              v_dqopt.dequeue_mode := DBMS_AQ.REMOVE;
              v_dqopt.navigation := DBMS_AQ.FIRST_MESSAGE;

              -- dequeue the message
              DBMS_AQ.DEQUEUE(
                  queue_name => descr.queue_name --IN
                  , dequeue_options => v_dqopt --IN
                  , message_properties => v_msgprop --OUT
                  , payload => v_message --OUT
                  , msgid => v_msgid --OUT
                  );

              IF descr.consumer_name = 'EXOP' THEN
                  IF LENGTH(v_message.text_body) = 519 OR LENGTH(v_message.text_body) = 517 THEN

                      -- Parse the message v_message.text_body for voucher num and status type
                      v_rec_message.msg_voucher_num := TRIM(SUBSTR(v_message.text_body, 211, 29));
                      v_rec_message.msg_source := SUBSTR(v_message.text_body, 1, 1);
                      v_rec_message.msg_status_type := TRIM(SUBSTR(v_message.text_body, 406, 2));
                      v_rec_message.msg_wirecall := TRIM(SUBSTR(v_message.text_body, 35, 4));
                      v_rec_message.msg_symbol := TRIM(SUBSTR(v_message.text_body, 39, 18));
                      v_rec_message.msg_ref_key := TRIM(SUBSTR(v_message.text_body, 89, 32));
                      v_rec_message.msg_seq_num := TRIM(SUBSTR(v_message.text_body, 320, 4));

                      IF SUBSTR(v_rec_message.msg_voucher_num, 8, 1) = ' '
                              OR v_rec_message.msg_status_type = 'CF'
                              OR v_rec_message.msg_status_type = 'TL' THEN
                          BEGIN
                              SELECT DISTINCT voucher_num INTO v_rec_message.msg_voucher_num
                              FROM tb_xop_order_manager_t
                              WHERE wirecall = v_rec_message.msg_wirecall
                              AND symbol = v_rec_message.msg_symbol
                              AND seq_num = v_rec_message.msg_seq_num
                              AND reference_key = v_rec_message.msg_ref_key;
                          EXCEPTION
                              WHEN OTHERS THEN
                                  RAISE;
                          END;
                      END IF;

                      v_voucher_num := v_rec_message.msg_voucher_num;
                      v_Avoucher_num := 'A' || v_rec_message.msg_voucher_num;


                      BEGIN
                          SELECT DISTINCT SSN INTO v_ssn
                          FROM tb_xop_order_manager_t
                          WHERE voucher_num = v_voucher_num;
                      EXCEPTION
                          WHEN OTHERS THEN
                              pr_xop_log_errors('Error retrieving SSN, pk_xop_aq_retrieve_orders.pr_retrieve_orders_cb');
                              RAISE;
                      END;

                      v_ssn_restrict := FALSE;
                      IF sys_context('g_cnxt_OMmulti_ssn', v_ssn) IS NOT NULL THEN
                          v_voucher_ip := sys_context('g_cnxt_OMmulti_ssn', v_ssn);

                          IF v_voucher_ip <> v_voucher_num THEN
                              v_ssn_restrict := TRUE;
                          END IF;

                      END IF;

                      IF NOT(v_ssn_restrict) THEN
                          IF v_msgprop.correlation IS NULL THEN

                              IF sys_context('g_cnxt_OMmulti', v_Avoucher_num) IS NULL THEN
                                  dbms_session.set_context('g_cnxt_OMmulti_ssn', v_ssn, v_voucher_num);
                                  dbms_session.set_context('g_cnxt_OMmulti', v_Avoucher_num,1);
                                  dbms_session.set_context('g_cnxt_OMmulti', v_voucher_num,1);
                                  v_ticket_num := 1;
                              ELSE
                                  v_ticket_num := sys_context('g_cnxt_OMmulti', v_Avoucher_num) + 1;
                                  dbms_session.set_context('g_cnxt_OMmulti', v_Avoucher_num, sys_context('g_cnxt_OMmulti', v_Avoucher_num) + 1);
                              END IF;
                              v_msgprop.correlation := TO_CHAR(v_ticket_num);

                              IF c_trail > 0 THEN
                                  v_trail_info(idx).trail_id      := fn_get_trail_id();
                                  v_trail_info(idx).voucher_num   := v_rec_message.msg_voucher_num;
                                  v_trail_info(idx).ticket_num    := 0;
                                  v_trail_info(idx).trail_text    := 'Received Message: [' || v_message.text_body || ']';
      v_trail_info(idx).run_date := SYSDATE;
      v_trail_info(idx).part_key := v_part_key;
      idx := idx + 1;
      END IF;

      ELSE
      v_ticket_num := TO_NUMBER(v_msgprop.correlation);
      END IF;

      IF c_trail > 2 THEN
      v_trail_info(idx).trail_id := fn_get_trail_id();
      v_trail_info(idx).voucher_num := v_rec_message.msg_voucher_num;
      v_trail_info(idx).ticket_num := v_ticket_num;
      v_trail_info(idx).trail_text := 'Message: [' || v_message.text_body || ']';
      v_trail_info(idx).run_date := SYSDATE;
      v_trail_info(idx).part_key := v_part_key;
      idx := idx + 1;
      END IF;


      BEGIN
      SELECT NVL(num_content, 99), NVL(char_content, '/apps/home/xopom/lib/RetrieveOrder.parms')
      INTO v_max_process, v_parmfile
      FROM tb_fc_misc_contents
      WHERE content_name = 'Orders-Max Processes';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      v_max_process := 99;
      v_parmfile := '/apps/home/xopom/lib/RetrieveOrder.parms';
      END;

      v_processes := NVL(sys_context('g_cnxt_OMmulti', 'tot_count'),0);

      IF v_processes >= v_max_process THEN
      IF c_trail > 0 THEN
      v_trail_info(idx).trail_id := fn_get_trail_id();
      v_trail_info(idx).voucher_num := v_rec_message.msg_voucher_num;
      v_trail_info(idx).ticket_num := v_ticket_num;
      v_trail_info(idx).trail_text := 'Unable to process Message: [' || v_message.text_body || '] because Max process '|| TO_CHAR(v_max_process) ||' has reached';
      v_trail_info(idx).run_date := SYSDATE;
      v_trail_info(idx).part_key := v_part_key;
      idx := idx + 1;
      END IF;
      v_max_reached := TRUE;
      v_maxreached := 1;
      END IF;

      IF sys_context('g_cnxt_OMmulti', v_voucher_num) <> v_ticket_num THEN
      v_in_process := TRUE;
      v_inprocess := 1;
      END IF;

      IF c_trail > 2 THEN
      v_trail_info(idx).trail_id := fn_get_trail_id();
      v_trail_info(idx).voucher_num := v_rec_message.msg_voucher_num;
      v_trail_info(idx).ticket_num := v_ticket_num;
      v_trail_info(idx).trail_text := 'Flags: [MaxReached,InProcess] ['
      || TO_CHAR(v_maxreached)
      || TO_CHAR(v_inprocess) || ']';
      v_trail_info(idx).run_date := SYSDATE;
      v_trail_info(idx).part_key := v_part_key;
      idx := idx + 1;
      END IF;

      IF NOT(v_max_reached) THEN
      IF NOT(v_in_process) THEN
      BEGIN
      OPEN ord_curs FOR
      SELECT voucher_num
      FROM tb_xop_failed_orders
      WHERE voucher_num = v_rec_message.msg_voucher_num
      AND process_flag in ('F', 'R'); -- F-InFailure, R- ToBeRecovered
      FETCH ord_curs BULK COLLECT INTO f_vouchernum;
      CLOSE ord_curs;

      IF f_vouchernum.FIRST IS NOT NULL THEN
      v_in_failure := TRUE;
      v_infailure := 1;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      RAISE;
      END;

      IF NOT(v_in_failure) THEN

      IF c_trail > 0 THEN
      v_trail_info(idx).trail_id := fn_get_trail_id();
      v_trail_info(idx).voucher_num := v_rec_message.msg_voucher_num;
      v_trail_info(idx).ticket_num := v_ticket_num;
      v_trail_info(idx).trail_text := 'Begin Processing Message: [' || v_message.text_body || ']';
      v_trail_info(idx).run_date := SYSDATE;
      v_trail_info(idx).part_key := v_part_key;
      idx := idx + 1;
      END IF;

      dbms_session.set_context('g_cnxt_OMmulti', 'tot_count', NVL(sys_context('g_cnxt_OMmulti', 'tot_count'),0) + 1);
      -- Parse rest of the message - v_message.text_body
      v_rec_message.msg_dest := SUBSTR(v_message.text_body, 57, 1);
      v_rec_message.msg_ret_code := SUBSTR(v_message.text_body, 324, 2);
      v_rec_message.msg_reason_desc := SUBSTR(v_message.text_body, 326, 80);
      v_rec_message.msg_activ_qty := SUBSTR(v_message.text_body, 408, 10);
      v_rec_message.msg_leaves_qty := SUBSTR(v_message.text_body, 418, 10);
      v_rec_message.msg_exec_price := SUBSTR(v_message.text_body, 428, 12);

      -- Save the message just in case.. for recovery
      v_failed := FALSE;
      INSERT INTO tb_xop_failed_orders
      (voucher_num, ticket_num, status_type, process_flag, message, ssn, run_date)
      VALUES
      (v_rec_message.msg_voucher_num, v_ticket_num
      , v_rec_message.msg_status_type, 'R', v_message.text_body, v_ssn, SYSDATE); --R Available in case of recovery
      COMMIT;
      v_dequeued := TRUE;
      v_saved := TRUE;

      -- run the procedure
      pr_validate_voucher(v_rec_message.msg_voucher_num
      , v_curr_stat
      , v_rowcount
      , v_corp_acronym
      , v_ret_status
      , v_trans_mode);
      IF v_ret_status AND v_rowcount = 1 THEN
                                              pr_xop_log_errors('Voucher num, Status type, Current Status: '|| v_rec_message.msg_voucher_num
                                                                                           || ',' || v_rec_message.msg_status_type || ',' || v_curr_stat);
      /* IF v_rec_message.msg_status_type = 'RO' AND
      v_rec_message.msg_ret_code = '05' THEN
      PG 02/27 Modified for New PCOE reader service */
      IF v_rec_message.msg_status_type = 'RO' AND ( v_rec_message.msg_ret_code = '05' OR
      ( v_rec_message.msg_ret_code = '04' AND
      TRIM(v_rec_message.msg_reason_desc) = 'PCOEFailure:AER.MT_COMM_CFG'
      ) OR
      ( v_rec_message.msg_ret_code = '99' AND
      SUBSTR(TRIM(v_rec_message.msg_reason_desc),1,26) = 'MKT CLOSED-ENTER OVERNIGHT'
      )
      ) THEN
      IF v_rec_message.msg_ret_code = '05' THEN -- Possible duplicate
      pr_exec_process_order(v_rec_message.msg_voucher_num
      , v_rec_message.msg_seq_num
      , 'Y');
      ELSIF v_rec_message.msg_ret_code = '99' AND
      SUBSTR(TRIM(v_rec_message.msg_reason_desc),1,26) = 'MKT CLOSED-ENTER OVERNIGHT' THEN
      pr_exec_process_order(v_rec_message.msg_voucher_num
      , v_rec_message.msg_seq_num
      , 'N');
      ELSIF v_rec_message.msg_ret_code = '04' AND
      TRIM(v_rec_message.msg_reason_desc) = 'PCOEFailure:AER.MT_COMM_CFG' THEN -- New return code with new reader service
      pr_xop_log_errors('Received Reject due to AER system being unavailable, VoucherNum :'
      || v_rec_message.msg_voucher_num);
      UPDATE tb_xop_order_manager_t
      SET retry_count = NVL(retry_count,0) + 1
      , retry_status = 'I'
      , current_status = 'MO'
      , status_type = 'MO'
      , q_flag = 'Y'
      WHERE voucher_num = v_rec_message.msg_voucher_num;
      END IF;
      ELSE
      IF v_rec_message.msg_status_type IN ('CO', 'RO', 'EO', 'BE',
      'BC', 'CX', 'CF', 'UR', 'TL') THEN
      IF v_curr_stat = 'PO' THEN
      IF v_rec_message.msg_status_type = 'BE' THEN
      pr_exec_retrieve_order(v_rec_message, v_ticket_num, v_parmfile, v_trans_mode, v_retcode);
      IF v_retcode <> 0 THEN
      v_failed := TRUE;
      END IF;
      ELSE
      pr_update_status_time(v_rec_message.msg_status_type
      , v_rec_message.msg_voucher_num);
      END IF;
      ELSE
      IF (v_rec_message.msg_status_type = 'CO' AND
      v_curr_stat IN ('CX', 'BC'))
      OR
      (v_rec_message.msg_status_type = 'CX' AND
      v_curr_stat IN ('BC', 'TL'))
      OR
      (v_rec_message.msg_status_type = 'BC' AND
      v_curr_stat = 'TL') THEN
      pr_update_status_time(v_rec_message.msg_status_type
      , v_rec_message.msg_voucher_num);
      ELSE
      pr_exec_retrieve_order(v_rec_message, v_ticket_num, v_parmfile, v_trans_mode, v_retcode);
      IF v_retcode <> 0 THEN
      v_failed := TRUE;
      END IF;
      END IF;
      END IF;
      ELSE
      IF v_rec_message.msg_status_type = 'ND' THEN
      -- nothing done
      pr_xop_log_errors( 'Status type NothingDone(ND) found for voucher num '
      || v_rec_message.msg_voucher_num || ' '
      || USER || '.pr_retrieve_orders_cb' );
      ELSE
      -- Invalid status code
      pr_xop_log_errors( 'Invalid status type '
      || v_rec_message.msg_status_type
      || ' found for voucher num '
      || v_rec_message.msg_voucher_num || ' '
      || USER || '.pr_retrieve_orders_cb' );
      END IF;
      END IF;
      END IF;
      ELSE
      v_failed := TRUE;
      IF v_rowcount = 0 THEN
      -- VoucherNum not found
      pr_xop_log_errors('Voucher Num ' || v_rec_message.msg_voucher_num
      || ' not found. Ignoring the message '
      || USER || '.pr_retrieve_orders_cb' );
      ELSE
      -- Multiple occurances of voucher num
      pr_xop_log_errors('Multiple occurances of voucher Num '
      || v_rec_message.msg_voucher_num
      || ' found. Ignoring the message'
      || USER || '.pr_retrieve_orders_cb' );
      END IF;
      END IF;}
        • 1. Re: How to add the function in anonymous block and debugg
          959687
          and remaining code is......

          { IF v_failed THEN
          UPDATE tb_xop_failed_orders
          SET process_flag = 'F'
          WHERE voucher_num = v_rec_message.msg_voucher_num
          AND ticket_num = v_ticket_num;
          ELSE
          DELETE FROM tb_xop_failed_orders
          WHERE voucher_num = v_rec_message.msg_voucher_num
          AND ticket_num = v_ticket_num;
          END IF;
          COMMIT;

          dbms_session.set_context('g_cnxt_OMmulti', 'tot_count', NVL(sys_context('g_cnxt_OMmulti', 'tot_count'),0) - 1);

          IF c_trail > 0 THEN
          v_trail_info(idx).trail_id := fn_get_trail_id();
          v_trail_info(idx).voucher_num := v_rec_message.msg_voucher_num;
          v_trail_info(idx).ticket_num := v_ticket_num;
          v_trail_info(idx).trail_text := 'End Processing Message: [' || v_message.text_body || ']';
          v_trail_info(idx).run_date := SYSDATE;
          v_trail_info(idx).part_key := v_part_key;
          idx := idx + 1;
          END IF;
          ELSE -- Infailure
          pr_xop_log_errors ('Ancestor is in failure status, Moving to failed orders table');
          INSERT INTO tb_xop_failed_orders
          (voucher_num, ticket_num, status_type, process_flag, message, ssn, run_date)
          VALUES
          (v_rec_message.msg_voucher_num, sq_xop_ticket_num.nextval
          , v_rec_message.msg_status_type, 'C', v_message.text_body, v_ssn, SYSDATE);
          COMMIT;
          v_dequeued := TRUE;
          v_saved := TRUE;
          END IF;

          IF sys_context('g_cnxt_OMmulti', v_Avoucher_num) = sys_context('g_cnxt_OMmulti', v_voucher_num) THEN
          dbms_session.clear_context('g_cnxt_OMmulti', NULL, v_Avoucher_num);
          dbms_session.clear_context('g_cnxt_OMmulti', NULL, v_voucher_num);
          dbms_session.clear_context('g_cnxt_OMmulti_ssn', NULL, v_ssn);
          ELSE
          dbms_session.set_context('g_cnxt_OMmulti', v_voucher_num, sys_context('g_cnxt_OMmulti', v_voucher_num) + 1);
          END IF;

          ELSE -- InProcess
          -- enqueue the message
          DBMS_AQ.ENQUEUE(
          queue_name => descr.queue_name --IN
          , enqueue_options => v_eqopt --IN
          , message_properties => v_msgprop --IN
          , payload => v_message --IN
          , msgid => v_msgid --OUT
          );
          COMMIT;
          v_dequeued := TRUE;
          v_saved := TRUE;
          END IF;
          ELSE -- Max reached
          pr_xop_log_errors ( 'More than acceptable number of processess running, Enqueing the message ');

          -- enqueue the message
          DBMS_AQ.ENQUEUE(
          queue_name => descr.queue_name --IN
          , enqueue_options => v_eqopt --IN
          , message_properties => v_msgprop --IN
          , payload => v_message --IN
          , msgid => v_msgid --OUT
          );
          COMMIT;
          v_dequeued := TRUE;
          v_saved := TRUE;
          END IF;
          ELSE --ssn_restrict is true
          -- enqueue the message
          DBMS_AQ.ENQUEUE(
          queue_name => descr.queue_name --IN
          , enqueue_options => v_eqopt --IN
          , message_properties => v_msgprop --IN
          , payload => v_message --IN
          , msgid => v_msgid --OUT
          );
          COMMIT;
          v_dequeued := TRUE;
          v_saved := TRUE;
          END IF;
          ELSE
          -- length <> 519 or 517
          pr_xop_log_errors('The message length is not equal to 519 or 517, Ignoring.. '
          || USER || '.pr_retrieve_orders_cb');
          END IF;
          ELSIF descr.consumer_name = 'RUALIVE' THEN
          -- R U ALIVE message
          IF c_trail > 0 THEN
          v_trail_info(idx).trail_id := fn_get_trail_id();
          v_trail_info(idx).voucher_num := 'RUALIVE';
          v_trail_info(idx).ticket_num := 99;
          v_trail_info(idx).trail_text := 'Received Message: [' || v_message.text_body || ']';
          v_trail_info(idx).run_date := SYSDATE;
          v_trail_info(idx).part_key := v_part_key;
          idx := idx + 1;
          END IF;
          pr_xop_log_errors('The dequeued message is R U ALIVE message, Ignoring.. '
          || USER || '.pr_retrieve_orders_cb');
          ELSIF descr.consumer_name = 'NONEXOP' THEN
          -- NON EXOP message
          IF c_trail > 0 THEN
          v_trail_info(idx).trail_id := fn_get_trail_id();
          v_trail_info(idx).voucher_num := 'NONEXOP';
          v_trail_info(idx).ticket_num := 999;
          v_trail_info(idx).trail_text := 'Received Message: [' || v_message.text_body || ']';
          v_trail_info(idx).run_date := SYSDATE;
          v_trail_info(idx).part_key := v_part_key;
          idx := idx + 1;
          END IF;
          CASE TRIM(SUBSTR(v_message.text_body, 1, 21))
          WHEN 'Clear Context - Count' THEN
          dbms_session.clear_context('g_cnxt_OMmulti', NULL,'tot_count');
          pr_xop_log_errors('Global Context g_cnxt_OMmulti tot_count cleared.. ' || USER );
          WHEN 'Clear Context' THEN
          dbms_session.clear_context('g_cnxt_OMmulti', NULL,NULL);
          pr_xop_log_errors('Global Context g_cnxt_OMmulti cleared.. ' || USER );
          WHEN 'Clear Context - SoSec' THEN
          v_ssn_ch := TRIM(SUBSTR(v_message.text_body, 23));
          dbms_session.clear_context('g_cnxt_OMmulti_ssn', NULL,v_ssn_ch);
          WHEN 'Clear Context - AllSS' THEN
          dbms_session.clear_context('g_cnxt_OMmulti_ssn', NULL,NULL);
          WHEN 'Clear Cntxt - Voucher' THEN
          v_voucher_num := TRIM(SUBSTR(v_message.text_body, 23));
          dbms_session.clear_context('g_cnxt_OMmulti', NULL, v_voucher_num);
          ELSE
          pr_xop_log_errors('The dequeued message is a NON EXOP message, Ignoring.. '
          || USER || '.pr_retrieve_orders_cb');
          END CASE;
          END IF;

          IF c_trail > 0 THEN
          IF idx > 1 THEN
          FORALL i IN v_trail_info.FIRST..v_trail_info.LAST
          INSERT INTO tb_xop_orders_trail VALUES v_trail_info (i);
          END IF;
          END IF;
          COMMIT;
          EXCEPTION
          WHEN OTHERS THEN
          pr_xop_log_errors('Fai1ed during processing, check the log. SQLCode: ' || SQLERRM
          || USER || '.pr_retrieve_orders_cb');
          ROLLBACK;
          IF NOT(v_dequeued) THEN
          pr_xop_log_errors('Fai1ed before dequeueing the message, Message will stay in queue. ' ||
          'Special processing needed.');
          ELSE
          IF NOT(v_saved) THEN -- dequeued but not saved? should not happen. Just in case..
          INSERT INTO tb_xop_failed_orders
          (voucher_num, ticket_num, status_type, process_flag, message, ssn, run_date)
          VALUES
          (v_rec_message.msg_voucher_num, v_ticket_num
          , v_rec_message.msg_status_type, 'R', v_message.text_body, v_ssn, SYSDATE);
          COMMIT;
          END IF;
          END IF;

          IF c_trail > 0 THEN
          IF idx > 1 THEN
          FORALL i IN v_trail_info.FIRST..v_trail_info.LAST
          INSERT INTO tb_xop_orders_trail VALUES v_trail_info(i);
          END IF;
          END IF;

          COMMIT;
          END fn_get_retail_id;}
          • 2. Re: How to add the function in anonymous block and debugg
            Keith Jamieson
            This is the line which is the causing the problem.
            Its the only insert statement into the table tb_xop_failed_orders.


            INSERT INTO tb_xop_failed_orders
            (voucher_num, ticket_num, status_type, process_flag, message, ssn, run_date)
            VALUES
            (v_rec_message.msg_voucher_num, v_ticket_num
            , v_rec_message.msg_status_type, 'R', v_message.text_body, v_ssn, SYSDATE); --R Available in case of recovery
            Check out how you are getting this value:

            v_rec_message.msg_voucher_num It is the variable which is responsible for the error.
            • 3. Re: How to add the function in anonymous block and debugg
              Marwim
              Why don't you continue your previous post {thread:id=2481463}. There I showed you an example: Include dbms_utility.format_error_stack and dbms_utility.format_error_backtrace instead of SQLERRM into your error logging.

              Regards
              Marcus

              BTW: Posting tons of unformatted code will not increase the chance to get helpful answers. {message:id=9360002}
              • 4. Re: How to add the function in anonymous block and debugg
                959687
                but index is {SELECT TABLE_NAME,COLUMN_NAME,INDEX_NAME FROM ALL_IND_COLUMNS WHERE INDEX_NAME='X2_XOP_FAILED_ORDERS';

                TABLE_NAME     COLUMN_NAME     INDEX_NAME

                TB_XOP_FAILED_ORDERS     SSN     X2_XOP_FAILED_ORDERS
                TB_XOP_FAILED_ORDERS     TICKET_NUM     X2_XOP_FAILED_ORDERS
                }

                So actually the constraint error will depend on SSN and ticket_num right..plz help me
                • 5. Re: How to add the function in anonymous block and debugg
                  Marwim
                  So you know the exact line of code where the error occures?
                  If yes and you need to know the values of your variables, then you can
                  - debug your program (set a breakpoint and watch the values during the progress of the program). You can do this with tools like SQL Developer (free) or most other db tools like Toad, PLSQL Developer ...
                  - instrument your code . This way you can create a log table/file that allows you to search for the point where the error occures and then read the previous entries for the necessary information

                  http://tkyte.blogspot.de/2005/06/instrumentation.html
                  http://en.wikipedia.org/wiki/Instrumentation_%28computer_programming%29
                  https://www.google.com/search?q=how+to+instrument+code

                  You have to decide what is best for you.