Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: inserted value too large for column
On Wed, 21 Jul 1999 22:14:46 GMT, hamard_at_my-deja.com wrote:
>Hi there,
>
>I'm looking on a solution to insert strings with
>length that exceed the column size. For the
>moment I have a exception evry time I attempt an
>insertion in the table, it's ok. but I want to
>truncate the string according the size of the
>column at the insertion and avoiding the
>exception "ORA-011401: inserted value too large
>for column".
Why don't you just code the insert statement like
insert into foo
( id, data )
values
( l_id, substr( l_data, 1, <column_length> );
>
>Is it possible to use the substr function in the
>tregger?
Nope.
>
>After some tests, it seems that I can raise the
>exception. What is the name of the exception,
>like that I will be abale to catch the exception.
This is not mapped the a named exception. You can map it yourself in you procedure by using ...
SQL>
1 declare
2 STRING_TOO_BIG exception;
3 pragma EXCEPTION_INIT( STRING_TOO_BIG, -1401 );
4 begin
5 insert into test values ( 1, '123456789012345' );
6 exception
7 when STRING_TOO_BIG then
8 dbms_output.put_line( 'Must make string smaller' );
9* end;
SQL> /
Must make string smaller
PL/SQL procedure successfully completed.
or you can just use the when others in the exception handler.
SQL>
1 begin
2 insert into test values ( 1, '123456789012345' );
3 exception
4 when others then
5 dbms_output.put_line( 'Must make string smaller' );
6* end;
SQL> /
Must make string smaller
hope this helps.
chris.
>
>Thanks.
>
>Kamel
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.