I have a 3rd party application that is passing over data. The app comes in as XML messages which the 3rd party translates into SQL statements to put the data into tables on my db. All's fine up to this point. I have a trigger on the input table that watches the messages as they come in and assigns a processing status to each dependent on field values. I am collecting employee login/logout information. The 3rd Party App passes in the Employee ID in a generic form field and labels it as text. I have to convert it to a number for processing on my side. But I ran across a message today that I would appreciate some help with. The Employee ID was '3/84'. Now my trigger immediately threw up because I can't process this garbage. I can see that there is a predefined PL/SQL error message called INVALID_NUMBER which might be able to help me out, but I'm not sure how to use it. What I want is simply to replace any non-numeric with a generic 99999 that can be processed normally and manually fixed later by data entry personnel. Can anyone help me develop a quick IF/THEN that will test for an invalid value? The exception looks like it will kick me out of my normal processing loop, and I don't want that.
What I am after is something like this
new_empid := :new.FORM_FIELD2;
IF TO_NUMBER(new_empid) < 99999 THEN
DBMS_OUTPUT.PUT_LINE(new_empid || ' is valid.');
ELSE
DBMS_OUTPUT.PUT_LINE(new_empid || ' is not valid. Changing to 99999.');
new.emp_id := 99999;
EXCEPTION
WHEN INVALID_NUMBER THEN
DBMS_OUTPUT.PUT_LINE(new_empid || ' is not valid. Changing to 99999.');
new.emp_id := 99999;
END IF;
but the compiler doesn't like the Exception buried in the IF statement.