Re: Check constraints, the impossible dream?

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?
> >

Just taking JL's post to a logical conclusion, you can have some code like:

create or replace
function get_the_constraint(p_err_msg varchar2) return varchar2 is   v_cons varchar2(60);
  v_check varchar2(4000);
begin
  if p_err_msg like 'ORA-02290%' then
    v_cons := substr(p_err_msg,instr(p_err_msg,'(')+1);     v_cons := substr(v_cons,1,instr(v_cons,')')-1);   end if;
  select search_condition
  into v_check
  from all_constraints
  where owner = substr(v_cons,1,instr(v_cons,'.')-1)   and constraint_name = substr(v_cons,instr(v_cons,'.')+1);   return v_check;
end;
/

which returns the text of the check constraint when passed the error message, for example:

SQL> begin
  2 insert into x values (0,0);
  3 exception when others then
  4 dbms_output.put_line(get_the_constraint(sqlerrm));   5 end;
  6 /

z > 0

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."