1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to Raise exceptions using SQL%BULK_EXCEPTIONS

Discussion in 'SQL PL/SQL' started by Moe Jalali, Sep 10, 2014.

  1. Moe Jalali

    Moe Jalali Guest

    I am using “Save Exceptions” construct to skip certain exceptions (ORA-00001) and continue processing. But I want to raise all other exceptions. How do I get other exceptions (names) when iterating thru exceptions using SQL%BULK_EXCEPTIONS(i) ? Raise command takes exception as a parameter but Oracle does Not compile the line: Raise SQL%BULK_EXCEPTIONS(i);

    Code (SQL):
       
     OPEN RQST_M;
     LOOP
     
       FETCH RQST_M BULK COLLECT INTO m_data LIMIT 200;
       BEGIN
            DBMS_OUTPUT.PUT_LINE('Request rows ' || m_data.COUNT);
            FORALL i IN 1..m_data.COUNT SAVE EXCEPTIONS
                INSERT INTO m VALUES m_data(i);

    EXCEPTION
              WHEN dml_errors THEN -- Now we figure out what failed and why.
                    errorCnt := SQL%BULK_EXCEPTIONS.COUNT;
                    errString := 'Number of statements that failed: ' || TO_CHAR(errorCnt);
                    DBMS_LOB.WRITEAPPEND(errCLOB, LENGTH(errString), errString);                                
     
                    FOR i IN 1..errorCnt LOOP
                        IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE = 1
                        THEN
                            errString := CHR(10) ||  'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
                                DBMS_LOB.WRITEAPPEND(errCLOB, LENGTH(errString), errString);
                        ELSE
                            RAISE SQL%BULK_EXCEPTIONS(i); -- Does NOT compile
                        END IF;
                    END LOOP;
           
       END;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    Raising Exceptions Explicitly

    1) raise
    2) raise_application_error


    Simple example 1:
    Code (SQL):


    DROP TABLE test_exception purge;
    CREATE TABLE test_exception (id  INT PRIMARY KEY);
    DECLARE
        dml_errors exception;  
        no_my_errors exception;  
        tab sys.odcinumberlist := sys.odcinumberlist (1,2,3,NULL,3);    
        i INT;    
    pragma exception_init(dml_errors, -24381);
    BEGIN
        forall z IN 1 .. tab.COUNT SAVE EXCEPTIONS
            INSERT INTO test_exception VALUES(tab(z));
    exception
    WHEN dml_errors THEN
        FOR z IN 1 ..  sql%bulk_exceptions.COUNT  loop
            IF sql%bulk_exceptions(z).error_code = 1
        THEN
            dbms_output.put_line('My errors :'||SQL%BULK_EXCEPTIONS(z).error_index);
        ELSE
             i:= sql%bulk_exceptions(z).error_code;
             raise no_my_errors; -- only by name
        END IF;
        END loop;            
    END;
    /

     

    simple example 2:
    Code (SQL):

    DROP TABLE test_exception purge;
    CREATE TABLE test_exception (id  INT PRIMARY KEY);
    DECLARE
        dml_errors exception;  
        tab sys.odcinumberlist := sys.odcinumberlist (1,2,3,NULL,3);    
        i INT;    
    pragma exception_init(dml_errors, -24381);
        no_my_errors exception;  
    BEGIN
        forall z IN 1 .. tab.COUNT SAVE EXCEPTIONS
            INSERT INTO test_exception VALUES(tab(z));
    exception
       
    WHEN dml_errors THEN
        FOR z IN 1 ..  sql%bulk_exceptions.COUNT  loop
            IF sql%bulk_exceptions(z).error_code = 1
        THEN
            dbms_output.put_line('My errors :'||SQL%BULK_EXCEPTIONS(z).error_index);
        ELSE
    raise_application_error(-20000,'No my error: by row '|| SQL%BULK_EXCEPTIONS(z).error_index ||'  by error_code '||SQL%BULK_EXCEPTIONS(z).error_code);
        END IF;
        END loop;    
    END;
    /


     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You are trying to use RAISE improperly. RAISE takes an exception name, not an error code and SQL%BULK_EXCEPTIONS doesn't provide an exception name. SQL%BULK_EXCEPTIONS provides the error code and the record number that failed, nothing more. Modifying your code a bit you can see which rows generated DML failures and the error message for the failure:


    Code (SQL):

    SQL> DECLARE
      2     TYPE emp_tbl IS TABLE OF emp%rowtype;
      3     emp_data emp_tbl;
      4     cursor EMPINFO IS
      5     SELECT * FROM emp;
      6     errorCnt     NUMBER;
      7     errString    varchar2(4000);
      8     errCode      NUMBER;
      9     dml_errors   exception;
     10     pragma exception_init(dml_errors, -24381);
     11
     12
     13  BEGIN
     14  OPEN EMPINFO;
     15  LOOP
     16
     17     FETCH EMPINFO BULK COLLECT INTO emp_data LIMIT 200;
     18     EXIT WHEN emp_data.COUNT = 0;
     19     BEGIN
     20          DBMS_OUTPUT.PUT_LINE('Request rows ' || emp_data.COUNT);
     21          FORALL i IN 1..emp_data.COUNT SAVE EXCEPTIONS
     22              INSERT INTO m VALUES emp_data(i);
     23
     24     EXCEPTION
     25            WHEN dml_errors THEN -- Now we figure out what failed and why.
     26                  errorCnt := SQL%BULK_EXCEPTIONS.COUNT;
     27                  errString := 'Number of statements that failed: ' || TO_CHAR(errorCnt);
     28                  dbms_output.put_line(errString);
     29
     30                  FOR i IN 1..errorCnt LOOP
     31                      IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE > 0
     32                      THEN
     33                          errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
     34                          dbms_output.put_line(errString);
     35                      ELSE
     36                          errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
     37                          dbms_output.put_line(errString);
     38                          RAISE;
     39                      END IF;
     40                  END LOOP;
     41     END;
     42
     43  END LOOP;
     44  END;
     45  /

    Request ROWS 18
    NUMBER OF statements that failed: 18


    Error #1
    Error message IS ORA-01722: invalid NUMBER


    Error #2
    Error message IS ORA-01722: invalid NUMBER


    Error #3
    Error message IS ORA-01722: invalid NUMBER


    Error #4
    Error message IS ORA-01722: invalid NUMBER


    Error #5
    Error message IS ORA-01722: invalid NUMBER


    Error #6
    Error message IS ORA-01722: invalid NUMBER


    Error #7
    Error message IS ORA-01722: invalid NUMBER


    Error #8
    Error message IS ORA-01722: invalid NUMBER


    Error #9
    Error message IS ORA-01722: invalid NUMBER


    Error #10
    Error message IS ORA-01722: invalid NUMBER


    Error #11
    Error message IS ORA-01722: invalid NUMBER


    Error #12
    Error message IS ORA-01722: invalid NUMBER


    Error #13
    Error message IS ORA-01722: invalid NUMBER


    Error #14
    Error message IS ORA-01722: invalid NUMBER


    Error #15
    Error message IS ORA-01722: invalid NUMBER


    Error #16
    Error message IS ORA-01722: invalid NUMBER


    Error #17
    Error message IS ORA-01722: invalid NUMBER


    Error #18
    Error message IS ORA-01722: invalid NUMBER


    PL/SQL PROCEDURE successfully completed.


    SQL>
     

    My table M had the same columns as EMP but in a different order, generating the DML errors displayed.