This discussion is archived
5 Replies Latest reply: Jan 2, 2013 6:44 AM by Marwim RSS

How to add the function in anonymous block and debugg

959687 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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
    KeithJamieson Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points