Re: NUMBER JUSTIFICATION

fgreene_at_hayes.com writes:
|> TRIGGER: KEY-NXTFLD
|>
|> BEGIN
|> :XNUMBER := TO_NUMBER(:XCHAR);
|> :XCHAR := LPAD(:XCHAR, 10);
|> NEXT_FIELD;
|> EXCEPTION
|> WHEN VALUE_ERROR THEN
|> MESSAGE('***ERROR -- invalid numeric value');
|> RAISE FORM_TRIGGER_FAILURE;
|> END;
|>
|> The EXCEPTION covers the case of attempting to enter a non-numeric value
|> in the character field. Logic would say that the INVALID_NUMBER exception
|> should be used but, in experiments, the system returned the VALUE_ERROR
|> exception. Go figure.

Just a tidbit.

That behavior seems to be due to the difference between the PL/SQL and the RDBMS implementation of the TO_NUMBER() function.

SELECT TO_NUMBER('abcd') INTO :var FROM dual; returns INVALID_NUMBER :var := TO_NUMBER('abcd'); returns VALUE_ERROR

The first statement requires a trip to the database. The second one doesn't. This behavior is documented in the V2.0 PL/SQL Guide. It's also in the V1.0 Guide but not written in such a way that it left it to the user to imply the above behavior.

Roderick Manalac
Oracle Corporation
[DISCLAIMER: My ball; my rules] Received on Wed Mar 10 1993 - 07:21:34 CET