Re: Let Oracle truncate values on INSERT autom.

I posted a response on the other board to this question, but it doesn't seem to be visible (at least not to me, on Google).

Use a "before insert or update on your_table for each row" trigger that checks (via the WHEN clause) if the new value has a length(new.your_value) > 10 (or whatever your number is. if its greater, it substr(new.your_value,1,10) on the insert or update etc.

"J?g Jung" <joerg.jung_at_clarity-ag.net> wrote in message news:<a4ivds$jii$1_at_news.netcologne.de>...
> 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 - 13:50:28 CST