CODEWITHSUNDEEP
oracle-databaseerror-handlingplsqloracle11g
x.509
x.509

Reputation: 2235

Unable to raise an application error message

For debugging purposes, I need to raise an application level message based on given flag. Here is exception code

  EXCEPTION
    WHEN dml_errors THEN
      l_errors := SQL%BULK_EXCEPTIONS.COUNT;
      S_Publish('I', 'Number of statements that failed: ' || l_errors);
      FOR i IN 1..l_errors LOOP
         S_Publish('I', 'Error #' || TO_CHAR(i) || ' occurred during '|| 'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
         S_Publish('I', 'Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
         S_Publish('I', 'Failing Record ID is ' || sap_tbl_ins(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX).DEVICE_PIN);
      END LOOP;
      IF g_app_error_flag THEN
        raise_application_error(-20707, 'Fatal Error: Replication script exceptions', TRUE);
      END IF;

    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error on record:' || l_zzman);
      S_Publish('I', 'SAP_EQUI Update: Failure processing record ' || l_zzman);
      S_Publish ('F');
      IF g_app_error_flag THEN
        raise_application_error(-20708, 'Fatal Error: Replication script exceptions', TRUE);
      END IF;

now given this code, the raise_application_error should raise an whenever the **g_app_error_flag is true. However, in all cases (i mean with testing environment where i am failing the script intentionally by adding some constraint on the table), all i see is DML error i.e.

ORA-01403: no data found 
ORA-06512: at "DBNAME.PRODUCT_COPY_PACKAGE", line 1808 
ORA-24381: error(s) in array DML 
ORA-06512: at "DBNAME.PRODUCT_COPY_PACKAGE", line 84 
ORA-06512: at line 3

could you guys suggest whats going on?

P.S:

here is the definition for S_Publish. There is no "raise" in the S_Publish.

  PROCEDURE S_Publish (i_type IN VARCHAR2, 
                         i_msg IN VARCHAR2 DEFAULT NULL) IS
  BEGIN

    CASE

       WHEN i_type = 'G' THEN
         IF g_debug_flag
         THEN
           INSERT INTO logtable (tstamp,line) values (SYSDATE, i_msg);
           COMMIT;
           g_counter := 0;
         END IF;

       WHEN i_type = 'F' THEN
         g_err_code  := SQLCODE;
         g_err_msg   := TO_CHAR(g_err_code)||' '||SUBSTR(SQLERRM, 1, 100);
         INSERT INTO logtable (tstamp,line) values (SYSDATE, g_err_msg);
         COMMIT;

       WHEN i_type = 'I' THEN
         INSERT INTO logtable (tstamp,line) values (SYSDATE, i_msg);
         COMMIT;
         g_counter := 0;

    END CASE;


  END S_Publish;

After little debugging thats what i have figured out so far. I don't know whats ther eaosn behind it

So the problem is that the program exits no sooner it ends printing errors i.e. the FOR i IN 1..l_errors LOOP. The program exits at END LOOP. Why anything after that isn't being executed?

Upvotes: 3

Views: 1017

Answers (3)

x.509
x.509

Reputation: 2235

So the problem was in S_Publish. the table it is referring to i.e. sap_tbl_ins is a 0 based. The SQL%BULK_EXCEPTIONS(i).ERROR_INDEX is basically the iteration number when this failed. So referring a 0 based index with a number is raising "array out of bound" exception. I just figured it out and changed the formula to 

S_Publish('I', 'Failing Record ID is ' || sap_tbl_ins((SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)-1).DEVICE_PIN);

and its working fine.

Upvotes: 2

Michael Broughton
Michael Broughton

Reputation: 4055

OK, questions:

1) What is the code at/around line 1808 in PRODUCT_COPY_PKG. This is where the first error is being raised from.

2) Are the inserts into logtable happening? (i.e. - is this exception block really executing?)

3) Is that error stack output comming from your dbms_output statements? Or is that the stack dump from Oracle at runtime?

Style note: I hope that S_PUBLISH is defined with PRAGMA AUTONOMOUS_TRANSACTION otherwise your COMMIT can be committing a partially completed transaction if your code was in the midst of doing stuff when it errored out..

Upvotes: 0

user890904
user890904

Reputation:

Since we don't see all the code, we have to rely on you that when you do the failing of the script intentionally by adding some constraint on the table, then this Exception block that you have posted is the one that is active. So if this block is coming into action, a reason that you don't see your "raise_application_error(-20707, ....." or the second one with -20708 could be that the procedure "S_Publish" is throwing an exception itself. That won't be caught in the same exception block where they are running unless you surround them with additional one. to test this theory, try to remove the calls to it temporarily and see if you get your raised error.

Upvotes: 0

Related Questions