Uploaded image for project: 'DSpace'
  1. DSpace
  2. DS-961

Oracle update sequence script can result in deleted sequences

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.5.0, 1.5.1, 1.5.2, 1.6.0, 1.6.1, 1.6.2, 1.7.0, 1.7.1, 1.7.2, 1.8.0
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Attachments:
      1
    • Comments:
      1
    • Documentation Status:
      Needed

      Description

      When updating the sequences using oracle if the table is empty the sequence for this table will be dropped, but not recreated.

      Imagine the following situation:

      DECLARE
      curr NUMBER := 0;
      BEGIN
      – If the table is empty, curr will be equal to Null
      SELECT max(workflowitem_id) INTO curr FROM workflowitem;
      – If curr is equal to Null, curr + 1 will be equal to Null as well
      curr := curr + 1;
      – The sequence will be dropped
      EXECUTE IMMEDIATE 'DROP SEQUENCE workflowitem_seq';
      – The sequence will not be recreated because of an 'Invalid Number' error since, curr equals Null
      EXECUTE IMMEDIATE 'CREATE SEQUENCE workflowitem START WITH ' || curr;
      END;

      I therefore suggest to replace the CREATE statement with the following line:
      EXECUTE IMMEDIATE 'CREATE SEQUENCE &1 START WITH ' || NVL(curr,1);

      By using the NVL() function, 1 will be used as the starting value for the sequence instead of the invalid Null value.

        Attachments

          Activity

            People

            • Assignee:
              kevin van de velde Kevin Van de Velde (Atmire)
              Reporter:
              kevin van de velde Kevin Van de Velde (Atmire)
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: