Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!logbridge.uoregon.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!not-for-mail
From: Kenneth Koenraadt
Newsgroups: comp.databases.oracle.server
Subject: Re: how to validate incoming pl/sql arguments for type/size?
Date: Mon, 07 Jul 2003 21:05:17 GMT
Message-ID: <3f09dba1.4367520@news.inet.tele.dk>
References: <94599bb3.0307071035.7f7ed94e@posting.google.com>
X-Newsreader: Forte Free Agent 1.11/32.235
Lines: 64
Organization: TDC Internet
NNTP-Posting-Host: 80.164.80.80
X-Trace: 1057611609 dread12.news.tele.dk 97235 80.164.80.80
X-Complaints-To: abuse@post.tele.dk
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:237095

On 7 Jul 2003 11:35:51 -0700, techguy_chicago@yahoo.com (Bomb Diggy)
wrote:

>My procedures have been generating 'ORA-01401: inserted value too
>large for column' errors when I try to insert runtime parameters into
>tables.  I would like to catch this before an insert attempt is made,
>but I'm not sure of the best way to proceed.
>
>For example, if I was accepting an incoming parameter of type CHAR(8)
>and I wanted to verify that it was not a string of length <= 8, what
>would be the best way to do that, syntax-wise?
>
>My ideal, I suppose, would be:
>
>IF (F_PARAMETER_DOES_NOT_FIT_INTO_TYPE( p_my_parameter, table.col%TYPE
>) ) THEN
>  RAISE a custom exception;
>END IF
>
>Can a column '%TYPE' be passed as an argument to a function?
>
>Here is my proc declaration:
>
>PROCEDURE MYPROC(  P_MY_NUMBER  IN  [TABLE_NAME].[COLUMN_NAME]%TYPE );
>
>Thanks.

Hi,

Normally, the best and simplest way is just doing the insert, and if
it fails, catch the exception and check which parameter was the
sinner, like this :

create table foo (c1 varchar2(10),c2 number(10));


create or replace procedure insert_it(p1 in varchar2,p2 in number)
as

Value_too_large Exception;
pragma exception_init(Value_too_large,-01401);
  
begin

  insert into foo values (p1,p2);
  exception
  when Value_too_large then
    if length(p1) > 10 then
      dbms_output.put_line('p1 is too long');
    else
      dbms_output.put_line('p2 is too long, apparently!');
    end if;
  
end insert_it;
/

 Oracle *always* checks if the values you try to insert are too long.
Why reinvent the wheel and slow down the application by  checking the
values manually in your code before Oracle does the job anyway ? 

- Kenneth Koenraadt