Re: Let Oracle truncate values on INSERT autom.

hi billy,

thanx for your reply. you´re right with your sugg., but my prob is that i don´t want to use substr fourty times in an rather large insert-stmt. with differing arguments. and i don´t have to write an api for other developers. i just want to be lazy.
actually it´s not important, if the values get truncated, the goal is to prevent oracle from complaining about too large values.

some people told me, there are dbms that can ignore needless chars. regards, jj

"Billy Verreynne" <vslabs_at_onwe.co.za> schrieb im Newsbeitrag news:3c6cfcd6.3132532531_at_news.saix.net...
> "news.netcologne.de" <joerg.jung_at_clarity-ag.net> wrote:
>
> >is there a way to force oracle to truncate too large values automatically
on
> >an per insert base ?
>
> Using the SUBSTR() function in the INSERT/UPDATE statement. Something
> like this:
> INSERT INTO foo ( col1, col2 ) VALUES ( SUBSTR(:c1,1,50),
> SUBSTR(:c2,1,20)
>
> Doubt that there is any magic way that Oracle can simply know that if
> you give it a 100 char literal in an INSERT/UPDATE, it should be
> ignoring the last 20 chars and only store the first 80 in a
> varchar2(80) column.
>
> Another alternative is to use Stored Procs as an API of sorts. I like
> this method myself. You provide the developers with an API into the
> system via stored procs. They use it instead of manual inserts,
> updates and deletes. Changes in the db can be made without having to
> change the client code. Performance tuning is better manageable. You
> do not have clueless developers writing SQL themselves using TOAD. And
> you can easy slap another interface (e.g. web based) on this API.
>
> In this API scenario, the developer can pass you a big string instead
> of 80 chars. You can decide whether or not to raise a "you are an
> idiot" exception in the stored proc, or to substring the first 80
> chars and ignore the rest.
>
> --
> Billy
>
>
>
Received on Fri Feb 15 2002 - 06:37:27 CST