-
I have created the following small table and trigger to simulate my problem for you as follows:
-- Table:
CREATE TABLE qis.rk_test_num
(
seqn NUMBER(8),
seqv VARCHAR2(8)
)
/
-- Trigger
CREATE OR REPLACE TRIGGER qis.t_rk_test_num
BEFORE INSERT
ON qis.rk_test_num
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
Begin
if :new.seqn = ' ' then :new.seqn := null; end if;
End;
/
My goal is to check the insert values to make sure that users are not inserting blanks, before I actually insert into the table.
This used to work fine on Oracle 8.0.5. We migrated to 8.1.7.3 a couple days ago. Since migration, my insert statements are failing due to this trigger (Please see below). I am guessing this might be a bug in Oracle 8.1.7.3.
SQL> insert into rk_test_num values(1,'1');
insert into rk_test_num values(1,'1')
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "QIS.T_RK_TEST_NUM", line 2
ORA-04088: error during execution of trigger 'QIS.T_RK_TEST_NUM'
SQL> insert into rk_test_num values(' ','1');
insert into rk_test_num values(' ','1')
*
ERROR at line 1:
ORA-01722: invalid number
Please help. Thanks in Advance.
Ramesh
-
It seems to me that:
if :new.seqn = ' ' then :new.seqn := null; end if;
is useless.
In :new.seqn = ' ' it is comparing a numeric against a string, what can cause the problem.
On the other hand the check is not needed here, as you can notice in:
insert into rk_test_num values(' ','1');
You'll get an error
