Hi all,
we have a sql statement in our program which tries to insert a row into a table but fails with ORA-01722 (invalid number).
The reason is the datatype NUMBER in the 3rd column of the table and the INSERT command that contains a SPACE character at this position.
I cannot change the program code in the next 3 month (release restrictions in the customers company)
Also, we cannot change the datatype of the column.
This is an explaining test scenario:
CREATE TABLE test_tmp (
id NUMBER,
text VARCHAR2(250),
nr NUMBER DEFAULT 0 NOT NULL
);
INSERT INTO test_tmp (id, text, nr) VALUES (4711, 'Test', ' ');
We tried a before row trigger, but it doesn't fire. It still returns the ORA-01722.
Is there any possibility to change the sql command before it executes automatically with a database function ?
It should be replaced with
INSERT INTO test_tmp (id, text, nr) VALUES (4711, 'Test', '0');
or
INSERT INTO test_tmp (id, text) VALUES (4711, 'Test');
Note that the first 2 values are bind variables in the program and only the SPACE character is fix in the statement.
Thanks in advance
Andreas