how to display all the exceptions using a procedure

Question

I am writing procedure where i am passing input values. I need to validate against the metadata, if not valid raise exception.using oracle exception i have validated condition then raised the exception. I have copied the code i have used for validation.

IF V_BUSN_APPLC_NM_COUNT = 0 THEN
        RAISE INVALID_SYSTEM_IDENTIFER;
    END IF;

    IF V_PROCES_TYPE_COUNT = 0 THEN
        RAISE INVALID_PROCES_TYPE;
    END IF;

as per my logic it should display both the values but displaying me only one message. how to display both the messages.

Does both IF's will execute? if so why it is not printing the both values.

in java we have take error as an array, then add to array and display list of array elements. do we have any thing like this in oracle.

score 1 · Accepted Answer · edited Jun 24 '13 at 14:28

1

If you want to keep it in an array you can do it this way:

/* package with exceptions */
CREATE OR REPLACE PACKAGE exception_pkg AS
    composite_exception EXCEPTION;
END exception_pkg;

/* procedure */
DECLARE
    TYPE errors_ntt IS TABLE OF VARCHAR2(100);
    l_errors  errors_ntt := errors_ntt();
BEGIN
    DECLARE
        /* set variables to get errors */
        V_BUSN_APPLC_NM_COUNT NUMBER := 0;
        V_PROCES_TYPE_COUNT   NUMBER := 0;
    BEGIN
        IF V_BUSN_APPLC_NM_COUNT = 0 THEN
            /* add error to array */
            l_errors.EXTEND;
            l_errors(l_errors.LAST) := 'INVALID_SYSTEM_IDENTIFER';
        END IF;

        IF V_PROCES_TYPE_COUNT = 0 THEN
            l_errors.EXTEND;
            l_errors(l_errors.LAST) := 'INVALID_PROCES_TYPE';
        END IF;

        IF l_errors.COUNT > 0 THEN
            RAISE exception_pkg.composite_exception;
        END IF;
    END;
EXCEPTION
    WHEN exception_pkg.composite_exception THEN
        FOR indx IN 1..l_errors.COUNT LOOP
            /* print results */
            DBMS_OUTPUT.PUT_LINE(l_errors(indx));
        END LOOP;
        RAISE;
END;
/*
INVALID_SYSTEM_IDENTIFER
INVALID_PROCES_TYPE
*/

edited Jun 24 '13 at 14:28

APC

144,005
19
170
281

answered Jun 24 '13 at 08:38

the_slk

2,172
1
11
10

thank you, I was looking for this kind of solution. I have tried ur method but didn't work for me. it didn't throw any error message. i still working on it. once it is working in my local i will make this as answer. – user1268890 Jun 24 '13 at 09:56
CAN U TELL ME WHAT THIS DOES. exception_pkg.composite_exception i have place out line there but it is not printing. – user1268890 Jun 24 '13 at 11:31
Sorry for late response. You have to enable printing. In SQL*PLUS: set serveroutput on or in SQL Developer by enabling Dbms_output. I do not know how to enable it in Java. – the_slk Jun 24 '13 at 13:04
The exception handler clause should RAISE an exception. Otherwise the program will continue processing despite errors being found. Anybody can ignore DBMS_OUTPUT messages. So not raising is a **bug** . – APC Jun 24 '13 at 14:27

Sven · Answer 2 · 2013-06-24T06:45:07.377

0

The first raise of an exception ends the execution of the procedure, so that further code won't be executed.

If you need to display all values, you have to use a procedure with OUT-parameters (without exception) or raise one exception with both values in the error-message.

edited Jun 24 '13 at 06:45

answered Jun 24 '13 at 06:36

Sven

1
1

Hi Sven if you have some time Can u give me example what u are saying it will help me understand better. – user1268890 Jun 26 '13 at 03:53
1. out-paramter, for example: http://psoug.org/definition/OUT.htm 2. For one exception you need user-defined Exceptions, see http://stackoverflow.com/questions/6020450/oracle-pl-sql-raise-user-defined-exception-with-custom-sqlerrm – Sven Jun 28 '13 at 21:23

how to display all the exceptions using a procedure

2 Answers2