Subquery question in 10g

[cygnus008]

We are doing preliminary testing in 10g prior to migrating our database from 9i to 10g. Our application broke on this sql--

INSERT INTO phantom_process_log (phantom_process_log_id, enc_tran_id, enc_status, start_datetime, stop_datetime, enc_process_time)
VALUES (phantom_process_log_seq.nextval, 1106651771, (SELECT enc_status FROM enc_tran
WHERE enc_tran_id = 1003345970), TO_DATE('2005-12-14 09:55:53','YYYY-MM-DD HH24:MI:SS'), TO_DATE('2005-12-14 09:55:53','YYYY-MM-DD HH24:MI:SS'), 0.016)--

We get an error - ora-01400- can't insert NULL in enc_status. The value is coming from the subquery - SELECT ENC_STATUS from ENC_TRAN where enc_tran_ed = 100345970 -

When I run the subquery stand alone I get a value of 'SU3' so there is a value available.

This insert has run for several years in 8i and 9i so we know it is valid there. What has changed in 10G?

Thanks
Larry

[Scorby]

I haven't got 10g so I can't verify that there isn't a bug with the insert..values, but I suggest you try running a test like this:

CREATE TABLE X (ID NUMBER, A NUMBER);
insert into x values (1, 5);

CREATE TABLE Y (ID NUMBER, A NUMBER, B DATE);

INSERT INTO Y (ID, A, B)
VALUES (2, (SELECT A FROM x WHERE ID=1), SYSDATE);

select * from y;

If that works, then it probably isn't a bug in Oracle and is probably some sort of environmental problem. Maybe the ID you are using to run the job is seeing a different version of the ENC_TRAN table from the one you are using to run the query standalone.

[cygnus008]

I took the table structure Scorby had suggested and then we ran the following. There was a trigger on the original table and I disabled the trigger first and was able to do the insert. That pointed us in the direction we took. The following is the code to create a trigger on the X and Y tables and then the different test cases.

Looks like this is being caused by a before-row-level trigger
wherein the :new value appears on both sides of an assignment function.

It seems to be only a problem for insert, not update

Other things to try: after-row trigger, delete statements, ...

-----------------------------------------------------------------------------
-- TEST TRIGGER
-----------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER Y_BIUR
BEFORE INSERT OR UPDATE ON Y
FOR EACH ROW

DECLARE
ln_mynum NUMBER := 0;

BEGIN

--test case 1 reference to :new value FAIL INSERT, PASS UPDATE
:new.A := :new.A ;

--test case 2 reference to :new value FAIL INSERT, PASS UPDATE
-- :new.A := :new.A + 1 ;

--test case 3 reference to :new value PASS INSERT, PASS UPDATE
--ln_mynum := :new.A ;

--test case 4 reference to :new value PASS INSERT, PASS UPDATE
--:new.A := 1;

END;

-------------------------------------------------------------------------
-- TEST DML
-------------------------------------------------------------------------
-- insert test
INSERT INTO y (ID, a, b)
VALUES (6, (SELECT a FROM y WHERE ID =3), SYSDATE);

-- update test
UPDATE y
SET ID = 5, a = (SELECT a FROM y WHERE ID = 3), b = SYSDATE
WHERE ID = 5;