Subquery question in 10g
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Subquery question in 10g

  1. #1
    Join Date
    Dec 2005
    Posts
    2

    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

  2. #2
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

  3. #3
    Join Date
    Dec 2005
    Posts
    2
    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
  •  


Click Here to Expand Forum to Full Width