Re: Which column caused ORA-01438?

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1086035240.207775_at_yasure>...
> The Gooch wrote:
>
> > How can I tell which column has caused a ORA-01438: value larger than
> > specified precision allows for this column?
> >
> > I need to know because I'd like to return a meaningful error message,
> > and I'd like to rely on check constraints as much as possible to
> > implement these types of business rules.
> >
> > -------------------
> > create table tab1 (
> > col1 number(5),
> > col2 number(3));
> >
> > create or replace procedure proc_ins (
> > param1 in tab1.col1%type,
> > param2 in tab1.col2%type,
> > errormess varchar2) is
> >
> > begin
> > insert into tab1 (col1,col2) values
> > (param1,param2);
> > commmit;
> > errormess := 'no error';
> > exception
> > when others errormess := SQLERRM;
> > end;
>
> A meaningful error message? What king of weirdo are you? ;-)
>
> Anyway ... the only way to do this is with a BEFORE INSERT OR UPDATE
> trigger and I'd question whether it is worth the overhead. Why not
> have some quality control in the front-end?

Certainly. But in this case, the procedure is part of an API called by various front-ends. Needless to say, we can't count on the front-ends doing anything.

As I said, we are trying to do as much checking as possible at the database level, as opposed to at the front-end of even in PL/SQL. Currently, we validate the parameters using PL/SQL (procedurally) as follows:

begin
...
if length(param1) > const_param1length or (param1 < const_min or param1 > const_max) then

    raise bad_param1;
end if;
...

I was trying to eliminate that, relying on the database engine itself return an error. It's been beaten into my head that we must rely on check constraints and such as much as possible! Received on Wed Jun 02 2004 - 06:42:54 CDT