Re: Check constraints, the impossible dream?

You should not allow VB applications to directly access tables if you want Oracle architecture. Either rewrite the error message in VB or perform your access via stored procedures/packages. Doing so is far more efficient and secure so it is better architecturally.

Daniel Morgan

John Ashton wrote:

> "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 - 16:21:14 CST