Re: Let Oracle truncate values on INSERT autom.

"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