Re: Check constraints, the impossible dream?

From: John Ashton <jashton_at_thunkbox.com>
Date: Thu, 21 Mar 2002 21:37:50 GMT
Message-ID: <Oosm8.1670$IA.113988@news-binary.blueyonder.co.uk>

"damorgan" <damorgan_at_exesolutions.com> wrote in message news:3C9A1944.D8187434_at_exesolutions.com...
> No it does not mean having to use triggers. But it does mean using code in
some form whether triggers,
> procedures, functions, or packages.
>
> If you are not familiar with these things then you have been handed an
opportunity to enhance your
> skills and your resume.
>
> Daniel Morgan
>
>
>
> John Ashton wrote:
>
> > damorgan <damorgan_at_exesolutions.com> wrote in message
news:<3C98B826.F1BB7476_at_exesolutions.com>...
> > > You need to explore PRAGMA EXCEPTION_INIT and RAISE_APPLICATION_ERROR.
You
> > > can return any message you want.
> > But this does mean having to use triggers, right? I essentially can't
> > use check constraints.
>

Ummm, please forgive me for being thick (I'm new to Oracle) but the only solution I can see with PRAGMA EXCEPTION_INIT is something like:

 DECLARE
    e_myexception EXCEPTION;
    PRAGMA EXCEPTION_INIT (e_myexception, -2290); BEGIN

     INSERT INTO THE_TABLE VALUES (1,-1);
     EXCEPTION
         WHEN e_myexception THEN
              RAISE_APPLICATION_ERROR(-20001,'The column price must be

greater than zero.');
END; Doesn't this mean that only the statements in the BEGIN/END block are trapped? What if the SQL statement 'INSERT INTO THE_TABLE VALUES (1,-1);' was sent from VB or even directly entered in SQL*PLUS? How can those statements be trapped?

Also, what if there is more than one check contraint in the table? Won't the exception number ALWAYS be -2290? How can I tell which constraint has failed? Do I have to try and parse SQLERRM? Received on Thu Mar 21 2002 - 15:37:50 CST