Forum Stats

  • 3,851,919 Users
  • 2,264,053 Discussions
  • 7,904,904 Comments

Discussions

Migration Workbench -- Access migration code contains an infinite loop

JustinCave
JustinCave Member Posts: 30,293 Gold Crown
edited Oct 23, 2009 12:33AM in SQL Developer
Based on a discussion that started over in the Database - General forum, it appears that the trigger code generated by the SQL Developer Migration Workbench may contain an infinite loop.

976432

The trigger that is generated
create or replace TRIGGER t_xxx_fld01_TRG BEFORE INSERT OR UPDATE ON t_xxx
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.fld01 IS NULL THEN
    SELECT  t_xxx_fld01_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN
      --get the max indentity value from the table
      SELECT max(fld01) INTO v_newVal FROM t_xxx;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT t_xxx_fld01_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
    -- save this to emulate @@identity
   msaccess_utilities.identity := v_newVal;
   -- assign the value from the sequence to emulate the identity column
   :new.fld01 := v_newVal;
  END IF;
END;
stores the MAX(fld01) in v_newVal if the sequence has a value of 1. If the table is empty, however, the MAX(fld01) will be NULL. So the loop condition becomes
EXIT WHEN v_incval >= NULL
which will never be satisfied.

Pierre generated the trigger above using SQL Developer 1.5.5, Access 2003, and Oracle 10.2.0.4

Justin

Answers

  • Mkirtley-Oracle
    Mkirtley-Oracle Member Posts: 1,859
    Justin,
    Could you provide the original Access code which produces the migrated trigger code ? We can then investigate further.
    Also, could you check if the same problem happens using the 2.1 Early Adopter version which is available from -

    http://www.oracle.com/technology/products/database/sql_developer/index.html

    Regards,
    Mike
  • Pierre Forstmann
    Pierre Forstmann Member Posts: 6,961 Silver Crown
    What do you exactly mean by Access code ? The SQL code to create the table or some VB code linked to the table ?

    In my case I don't think that the trigger is generated from VB code because we are only testing data migration and not application migration: I think that the trigger is generated if the Access table has a AUTONUMBER column.
  • Mkirtley-Oracle
    Mkirtley-Oracle Member Posts: 1,859
    Hi,
    The best way to investigate the problem is to try and reproduce it so we need know what there is in the Access database that causes the trigger to be created. The best way would be to provide the Access mdb file so if you have access to Metalink then probably the best way to try and resolve it is to open a tar against the product 1875 and the component migration.
    If you are not able to do this then can you provide us with whatever we need to try and reproduce the problem ?

    Regards,
    Mike
  • reproduced and filed a bug for this issue.
This discussion has been closed.