This discussion is archived
4 Replies Latest reply: Oct 8, 2012 5:44 AM by BillyVerreynne RSS

Exception Handling in procedures

947561 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    FAQ::{message:id=9360013}
  • 2. Re: Exception Handling in procedures
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.

Legend

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