Re: Check constraints, the impossible dream?

Yup, I can see that store procs are good. I guess I'd also have to make sure noone except a DBA had access to SQL (for updating/inserting). Hopefully a DBA using SQL*PLUS can figure out the constraint message.

But what about the other problem with more than one check constraint per table?

"damorgan" <damorgan_at_exesolutions.com> wrote in message news:3C9A5CDA.EFD1109B_at_exesolutions.com...
> 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:38:05 CST