Re: Check constraints, the impossible dream?

I think someone there may need a serious lesson in the Oracle security model. Your statement makes no sense.

Daniel Morgan

John Ashton wrote:

> 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 - 17:54:13 CST