Exception handling using Oracle Forms Builder

Question

I am making my first Oracle form in Oracle Forms Builder with the help of web material i.e. tutorials etc. I have written the following code in the WHEN-BUTTON-PRESSED trigger:

INSERT INTO PATIENT VALUES 
(:CNIC_NO, :P_NAME, :CITY, :ADDRESS, :GENDER, :BLOOD_GROUP, :DISEASE, :WARD_NO);
COMMIT;

The problem here is that an unhandled exception is raised by the trigger with the following error numbers:

• ORA-12899 (Reason is the Null value is inserted in non-Null column)
• ORA-1400 (Reason is that the data length is too large than the allowed length)

I need to popup an informative message box for these and a default one for the others.

Answer 1

First of all I would avoid this errors by builtin Oracle forms validation. Set Required property to True on property panel for all items, which are based on not null database columns. Oracle forms then forces user to enter a value.

Next set Datatype and Maximum length properties to set basic data validations.

This should be enough in your case. If your still want to catch an exception, use code like this:

begin
    INSERT ...
exception
    when others then
        message(DBMSERRTEXT);
end;

Anyway - it is not good idea to insert data using when button pressed triggers. It is not transaction safe. Use standard forms block. Oracle forms does all the data validations and manipulations for you.