4 Replies Latest reply: Oct 8, 2012 7:44 AM by Billy~Verreynne RSS

    Exception Handling in procedures

    947561
      Hi,

      Below is the procedure I am using for writing text files data to database. The problem here is let us assume there are 6 records in text file and if there is a problem at 2nd record, the later records are not getting inserted.
      CREATE OR REPLACE PROCEDURE PROC1
      IS
      temp varchar2(500);
      tmp_name varchar2(5);
      tmp_no varchar2(4);
      .....
      
      BEGIN
      ...
      
      WHILE NOT end_of_file
      LOOP
      .....
      
      IF i = 18 THEN
      tmp_no := temp;
      END IF;
      
      IF i = 21 THEN
      tmp_name := temp;
      END IF;
      
      END LOOP;
      END;
      /
      If i=18 and temp = '12345' here, then
      tmp_no := temp;
      won't work (tmp_no varchar2(4);)
      Similarly, If i=21 and temp = 'ABCDEFG' here, then
      tmp_name := temp;
      won't work (tmp_name varchar2(5));
      Please let me know how to hadle this through EXCEPTIONS so that even if there is a problem with 1 record, while loop remain working for further records..
        • 1. Re: Exception Handling in procedures
          jeneesh
          FAQ::{message:id=9360013}
          • 2. Re: Exception Handling in procedures
            Billy~Verreynne
            It is not as much an issue of exception handling, but correctly modularising code.

            For example, processing a text file where a field is expected to contain only characters (no numeric or special characters) and a minimum of 20 and maximum of 60 characters. The test for that field needs to be implemented as a distinct code unit (e.g. function or procedure).

            One then need to decide how this code unit needs to deal with data from a field that does not confirm to the rules for that field. E.g. raise an application error to indicate a failure? Or return a null value to indicate that the field cannot be parsed to a valid database value.

            A simplistic parser results - with reusable modularised code units that can be called to parse the text input into valid data.
            • 3. Re: Exception Handling in procedures
              947561
              Hey Jeneesh this works great.. I used the below code..
              CREATE OR REPLACE PROCEDURE PROC1
              IS
              temp varchar2(500);
              tmp_name varchar2(5);
              tmp_no varchar2(4);
              .....
               
              BEGIN
              ...
               
              WHILE NOT end_of_file
              LOOP
              .....
               
              BEGIN
              IF i = 18 THEN
              tmp_no := temp;
              END IF;
               
              IF i = 21 THEN
              tmp_name := temp;
              END IF;
              EXCEPTION WHEN OTHERS THEN ERROR1 := 'Error in field: ' || temp;
              END
               
              END LOOP;
              END;
              /
              Just a small problem here. It is returning me two errors for record no 4:
              004. Error in field: ABCDEFG
              004. ORA-00947: not enough values
              No I do not want to display this system generated error (ORA-00947) and display only the first one.
              Please let me know how to do this?

              Edited by: 944558 on 8 Oct, 2012 5:00 AM
              • 4. Re: Exception Handling in procedures
                Billy~Verreynne
                944558 wrote:

                Just a small problem here. It is returning me two errors for record no 4:
                004. Error in field: ABCDEFG
                004. ORA-00947: not enough values
                No I do not want to display this system generated error (ORA-00947) and display only the first one.
                It returns an error stack - not a single error. And yes, you want to see the FULL error stack in order to determine just what happened and what went wrong.

                If you want to render a user-friendly error instead of the error stack, then it is a client rendering issue - the client gets the full error stack from the Oracle Client driver. It then needs to decide just what to use from that error stack and what to display.

                No, this is not relevant to clients like SQL*Plus, TOAD and SQL-Developer as these are NOT end-user interfaces and does NOT support a post processing rendering interface as Java, .Net and other custom application front-ends do.