Id like to error handle. I get this error code ORA-02291. Its an intentional error i have set up at the moment. How can i turn that into a more understandable message. At the moment im working on the command prompt, do I need a trigger to achieve this. Thanks for your help.
Well, you could look at Database Event Triggers. I haven't tried them myself though. I'm not sure they give you what you need - can you find out the name of the constraint in these triggers?
In my experience, users interact with the database via an application. They do not sit at the SQL Plus prompt and type in INSERT statements. The application may be a Forms screen or ASP web page or whatever, and stored procedures are often used for business logic. In the application (preferably in a stored procedure), you can trap the error message using SQLERRM as I showed earlier and parse it to see what it means and give a more user friendly message. It is quite common to build an error handling package to do this, with a lookup table of constraint names and friendly error messages, e.g.
select * from constraint_errors;
EMP_DEPT_FK Invalid department code
EMP_PK Employee number already in use
In your application, you would trap any exceptions and call the error handler like this:
WHEN OTHERS THEN error_pkg.handle_error(SQLERRM, ...);
The handle_error procedure would parse the SQLERRM text, see that it was a constraint violation, extract the constraint name, and look up the appropriate message from the table.