-
Subquery question in 10g
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
-
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.
-
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;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|