You Asked
Hi Tom,
Got a chance to ask a question after quite a while... your site is always so busy! ]:-) Anyway, here goes...
I have a situation where there are tables in my database receiving data from numerous external sources. Quite a few of these tables have comment-type freetext fields in them, and there have been instances where the "comments" sent in by one system have gone beyond the size of db columns allocated to hold them resulting in error.
The easy (and most obvious) way to fix this is to make the size of these db columns the same as maximum expected size of the "comments" data. However, the no. of interfacing systems will constantly ramp up over time, and I do not want to continually change the column length whenever a system with larger "comments" field comes in.
I would much rather do this through triggers or such-like, where the comments data is checked to be <= db column size, and truncated appropriately when it exceeds this size.
However, when I try and create a before-insert trigger to implement this, I get a "inserted value too large" error. It seems Oracle is checking to ensure the data can fit into destination column BEFORE firing the trigger, thus circumventing my check-and-fix logic!
A simple case to illustrate this is given below...
--------------------------------------------------------------
SQL> drop table mytest;
Table dropped.
SQL> create table mytest (col1 number(10), col2 varchar2(10));
Table created.
SQL> create or replace trigger fmtTrig
2 before insert on mytest
3 for each row
4 declare
5 maxColSize number(4);
6 tmpBuffer varchar2(4000);
7 begin
8 select data_length
9 into maxColSize
10 from user_tab_columns
11 where column_name = 'COL2'
12 and table_name = 'MYTEST';
13
14 tmpBuffer := substr(:new.col2,1,maxColSize - 1);
15 :new.col2 := tmpBuffer;
16 dbms_output.PUT_LINE('inserted values are: '||tmpBuffer);
17 end;
18 /
Trigger created.
SQL> insert into mytest values (1, 'this goes');
1 row created.
SQL> insert into mytest values (2, 'this is truncated');
insert into mytest values (2, 'this is truncated')
*
ERROR at line 1:
ORA-01401: inserted value too large for column
SQL> rollback;
Rollback complete.
-------------------------------------------------------------
All help on this matter will be highly appreciated.
Many thanks for taking the time out day in and day out to answer queries from people like myself!
Best Regards,
Mohit.
and Tom said...
Well, we cannot do it directly on the TABLE itself -- as you have seen, when the data "don't fit", we don't let it happen. Same thing would happen if you tried to insert "hello" into the number field -- the data doesn't fit, we don't do it.
You can use a View and an insert of trigger however. Here is a sample of what you could do. I made it pretty generic -- so this procedure can regenerate the trigger/view whenever we need (more columns, less columns, etc). You could expand this to do whatever edits you wanted on dates, numbers, etc. Load into this instead of the table and you'll achieve what you want. Also, you can replace the dbms_output with a better logging facility (like an insert into another "log table" showing the truncated values and all)
Owner of the procedure MUST have
create view
create trigger
granted directly to them -- not via a ROLE
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( col1 number(10), col2 varchar2(10) );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure gen_trigger_view( p_tname in varchar2 )
2 as
3 l_stmt long;
4 begin
5 l_stmt := 'create or replace view ' || p_tname || '_v as select ';
6 for x in ( select column_name
7 from user_tab_columns
8 where table_name = upper(p_tname)
9 order by column_id )
10 loop
11 l_stmt := l_stmt || ' cast( ' || x.column_name || ' as varchar2(4000) )' || x.column_name || ',';
12 end loop;
13 l_stmt := rtrim( l_stmt, ',' ) || ' from ' || p_tname;
14
15 execute immediate l_stmt;
16
17 l_stmt := 'create trigger ' || p_tname || '_v instead of INSERT on ' || p_tname || '_v
18 begin';
19
20 for x in ( select column_name, data_length
21 from user_tab_columns
22 where table_name = upper(p_tname)
23 and data_type = 'VARCHAR2'
24 order by column_id )
25 loop
26 l_stmt := l_stmt || '
27 if :new.' || x.column_name || ' <> substr(:new.' || x.column_name || ',1,' || x.data_length || ') then
28 dbms_output.put_line( ''Field ' || x.column_name || ' truncated'' );
29 end if;';
30 end loop;
31
32 l_stmt := l_stmt || '
33 insert into ' || p_tname || '( ' ;
34
35 for x in ( select column_name, data_length
36 from user_tab_columns
37 where table_name = upper(p_tname)
38 order by column_id )
39 loop
40 l_stmt := l_stmt || x.column_name || ',';
41 end loop;
42 l_stmt := rtrim(l_stmt,',') || ') values (';
43
44 for x in ( select column_name, data_type, data_length
45 from user_tab_columns
46 where table_name = upper(p_tname)
47 order by column_id )
48 loop
49 if ( x.data_type = 'VARCHAR2' )
50 then
51 l_stmt := l_stmt || 'substr( :new.' || x.column_name || ',1,' || x.data_length || '),';
52 else
53 l_stmt := l_stmt || ':new.' || x.column_name || ',';
54 end if;
55 end loop;
56 l_stmt := rtrim(l_stmt,',') || ');
57 end;';
58
59 execute immediate l_stmt;
60 end;
61 /
Procedure created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec gen_trigger_view( 't' );
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> desc t_v
Name Null? Type
----------------------------------------------------------------------- -------- -------------------------------------------------
COL1 VARCHAR2(4000)
COL2 VARCHAR2(4000)
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t_v values ( 10, 'helloworld' );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t_v values ( 11, 'hello world' );
Field COL2 truncated
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t;
COL1 COL2
---------- ----------
10 helloworld
11 hello worl
ops$tkyte@ORA817DEV.US.ORACLE.COM>
Rating
(5 ratings)
Is this answer out of date? If it is, please let us know via a Comment