Re: Which column caused ORA-01438?

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?

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)