Re: how to validate incoming pl/sql arguments for type/size?

On 7 Jul 2003 11:35:51 -0700, techguy_chicago_at_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