Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-699

toSql() for Oracle table with auto increment column produces SQL errors

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: 2.4.1
    • Fix Version/s: None
    • Component/s: Schema Managers
    • Security Level: All
    • Labels:
      None
    • Environment:
      Oracle 10g

      Description

      I have a table with an ID column that is auto incremented, and I'm trying to use the Doctrine schema representation objects to generate the SQL.

      But, the SQL that gets generated for Oracle results in errors when I try to run the SQL script.

      The Oracle version we are using is "Oracle Database 10g Release 10.2.0.4.0 - 64bit Production." (on Windows)

      I have this PHP code:

      $schema = new Doctrine\DBAL\Schema\Schema();
      $table = $schema->createTable('bvs_request');
      
      $table->addColumn('brq_request_id', 'bigint', array('autoincrement' => true));
      $table->addColumn('brq_policy_num', 'string', array('length' => 25));
      $table->addColumn('brq_request_date', 'datetime', array('default' => 'CURRENT_TIMESTAMP'));
      $table->setPrimaryKey(array('brq_request_id'));
      
      $platform = new Doctrine\DBAL\Platforms\OraclePlatform();
      $sql = $schema->toSql($platform);
      

      ... which generates this SQL:

      CREATE TABLE bvs_request (brq_request_id NUMBER(20) NOT NULL, brq_policy_num VARCHAR2(25) NOT NULL, brq_request_date TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY(brq_request_id))
      DECLARE
        constraints_Count NUMBER;
      BEGIN
        SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'BVS_REQUEST' AND CONSTRAINT_TYPE = 'P';
        IF constraints_Count = 0 OR constraints_Count = '' THEN
          EXECUTE IMMEDIATE 'ALTER TABLE BVS_REQUEST ADD CONSTRAINT BVS_REQUEST_AI_PK PRIMARY KEY (BRQ_REQUEST_ID)';
        END IF;
      END;
      CREATE SEQUENCE BVS_REQUEST_BRQ_REQUEST_ID_SEQ START WITH 1 MINVALUE 1 INCREMENT BY 1
      CREATE TRIGGER BVS_REQUEST_AI_PK
         BEFORE INSERT
         ON BVS_REQUEST
         FOR EACH ROW
      DECLARE
         last_Sequence NUMBER;
         last_InsertID NUMBER;
      BEGIN
         SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL;
         IF (:NEW.BRQ_REQUEST_ID IS NULL OR :NEW.BRQ_REQUEST_ID = 0) THEN
            SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL;
         ELSE
            SELECT NVL(Last_Number, 0) INTO last_Sequence
              FROM User_Sequences
             WHERE Sequence_Name = 'BVS_REQUEST_BRQ_REQUEST_ID_SEQ';
            SELECT :NEW.BRQ_REQUEST_ID INTO last_InsertID FROM DUAL;
            WHILE (last_InsertID > last_Sequence) LOOP
               SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO last_Sequence FROM DUAL;
            END LOOP;
         END IF;
      END;
      

      This is the result when trying to run the SQL in Oracle Developer:

      Error starting at line 1 in command:
      CREATE TABLE bvs_request (brq_request_id NUMBER(20) NOT NULL, brq_policy_num VARCHAR2(25) NOT NULL, brq_request_date TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY(brq_request_id))
      DECLARE
        constraints_Count NUMBER
      Error at Command Line:2 Column:1
      Error report:
      SQL Error: ORA-00922: missing or invalid option
      00922. 00000 -  "missing or invalid option"
      *Cause:    
      *Action:
      
      Error starting at line 4 in command:
      BEGIN
        SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'BVS_REQUEST' AND CONSTRAINT_TYPE = 'P';
        IF constraints_Count = 0 OR constraints_Count = '' THEN
          EXECUTE IMMEDIATE 'ALTER TABLE BVS_REQUEST ADD CONSTRAINT BVS_REQUEST_AI_PK PRIMARY KEY (BRQ_REQUEST_ID)';
        END IF;
      END;
      CREATE SEQUENCE BVS_REQUEST_BRQ_REQUEST_ID_SEQ START WITH 1 MINVALUE 1 INCREMENT BY 1
      CREATE TRIGGER BVS_REQUEST_AI_PK
         BEFORE INSERT
         ON BVS_REQUEST
         FOR EACH ROW
      DECLARE
         last_Sequence NUMBER;
         last_InsertID NUMBER;
      BEGIN
         SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL;
         IF (:NEW.BRQ_REQUEST_ID IS NULL OR :NEW.BRQ_REQUEST_ID = 0) THEN
            SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL;
         ELSE
            SELECT NVL(Last_Number, 0) INTO last_Sequence
              FROM User_Sequences
             WHERE Sequence_Name = 'BVS_REQUEST_BRQ_REQUEST_ID_SEQ';
            SELECT :NEW.BRQ_REQUEST_ID INTO last_InsertID FROM DUAL;
            WHILE (last_InsertID > last_Sequence) LOOP
               SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO last_Sequence FROM DUAL;
            END LOOP;
         END IF;
      END;
      Error report:
      ORA-06550: line 7, column 1:
      PLS-00103: Encountered the symbol "CREATE" 
      06550. 00000 -  "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error.
      *Action:
      Error starting at line 4 in command:
      BEGIN
        SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'BVS_REQUEST' AND CONSTRAINT_TYPE = 'P';
        IF constraints_Count = 0 OR constraints_Count = '' THEN
          EXECUTE IMMEDIATE 'ALTER TABLE BVS_REQUEST ADD CONSTRAINT BVS_REQUEST_AI_PK PRIMARY KEY (BRQ_REQUEST_ID)';
        END IF;
      END;
      CREATE SEQUENCE BVS_REQUEST_BRQ_REQUEST_ID_SEQ START WITH 1 MINVALUE 1 INCREMENT BY 1
      CREATE TRIGGER BVS_REQUEST_AI_PK
         BEFORE INSERT
         ON BVS_REQUEST
         FOR EACH ROW
      DECLARE
         last_Sequence NUMBER;
         last_InsertID NUMBER;
      BEGIN
         SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL;
         IF (:NEW.BRQ_REQUEST_ID IS NULL OR :NEW.BRQ_REQUEST_ID = 0) THEN
            SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL;
         ELSE
            SELECT NVL(Last_Number, 0) INTO last_Sequence
              FROM User_Sequences
             WHERE Sequence_Name = 'BVS_REQUEST_BRQ_REQUEST_ID_SEQ';
            SELECT :NEW.BRQ_REQUEST_ID INTO last_InsertID FROM DUAL;
            WHILE (last_InsertID > last_Sequence) LOOP
               SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO last_Sequence FROM DUAL;
            END LOOP;
         END IF;
      END;
      

        Activity

        Hide
        Steve Müller added a comment -

        Eugene Morgan Can it be that you tried to execute the sequence of statement as ONE statement in your client? Because the SQL output you gave here are several statements that normally get execute after each other (each as a single statement):

        1.

        CREATE TABLE bvs_request (brq_request_id NUMBER(20) NOT NULL, brq_policy_num VARCHAR2(25) NOT NULL, brq_request_date TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY(brq_request_id))

        2.

        DECLARE
          constraints_Count NUMBER;
        BEGIN
          SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'BVS_REQUEST' AND CONSTRAINT_TYPE = 'P';
          IF constraints_Count = 0 OR constraints_Count = '' THEN
            EXECUTE IMMEDIATE 'ALTER TABLE BVS_REQUEST ADD CONSTRAINT BVS_REQUEST_AI_PK PRIMARY KEY (BRQ_REQUEST_ID)';
          END IF;
        END;

        3.

        CREATE SEQUENCE BVS_REQUEST_BRQ_REQUEST_ID_SEQ START WITH 1 MINVALUE 1 INCREMENT BY 1

        4.

        CREATE TRIGGER BVS_REQUEST_AI_PK
           BEFORE INSERT
           ON BVS_REQUEST
           FOR EACH ROW
        DECLARE
           last_Sequence NUMBER;
           last_InsertID NUMBER;
        BEGIN
           SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL;
           IF (:NEW.BRQ_REQUEST_ID IS NULL OR :NEW.BRQ_REQUEST_ID = 0) THEN
              SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL;
           ELSE
              SELECT NVL(Last_Number, 0) INTO last_Sequence
                FROM User_Sequences
               WHERE Sequence_Name = 'BVS_REQUEST_BRQ_REQUEST_ID_SEQ';
              SELECT :NEW.BRQ_REQUEST_ID INTO last_InsertID FROM DUAL;
              WHILE (last_InsertID > last_Sequence) LOOP
                 SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO last_Sequence FROM DUAL;
              END LOOP;
           END IF;
        END;

        When executing all statements at once, there are some missing ";" terminators (for example after the first CREATE TABLE statement). This is why Oracle complains about syntax.

        Show
        Steve Müller added a comment - Eugene Morgan Can it be that you tried to execute the sequence of statement as ONE statement in your client? Because the SQL output you gave here are several statements that normally get execute after each other (each as a single statement): 1. CREATE TABLE bvs_request (brq_request_id NUMBER(20) NOT NULL, brq_policy_num VARCHAR2(25) NOT NULL, brq_request_date TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY(brq_request_id)) 2. DECLARE constraints_Count NUMBER; BEGIN SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'BVS_REQUEST' AND CONSTRAINT_TYPE = 'P'; IF constraints_Count = 0 OR constraints_Count = '' THEN EXECUTE IMMEDIATE 'ALTER TABLE BVS_REQUEST ADD CONSTRAINT BVS_REQUEST_AI_PK PRIMARY KEY (BRQ_REQUEST_ID)'; END IF; END; 3. CREATE SEQUENCE BVS_REQUEST_BRQ_REQUEST_ID_SEQ START WITH 1 MINVALUE 1 INCREMENT BY 1 4. CREATE TRIGGER BVS_REQUEST_AI_PK BEFORE INSERT ON BVS_REQUEST FOR EACH ROW DECLARE last_Sequence NUMBER; last_InsertID NUMBER; BEGIN SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL; IF (:NEW.BRQ_REQUEST_ID IS NULL OR :NEW.BRQ_REQUEST_ID = 0) THEN SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO :NEW.BRQ_REQUEST_ID FROM DUAL; ELSE SELECT NVL(Last_Number, 0) INTO last_Sequence FROM User_Sequences WHERE Sequence_Name = 'BVS_REQUEST_BRQ_REQUEST_ID_SEQ'; SELECT :NEW.BRQ_REQUEST_ID INTO last_InsertID FROM DUAL; WHILE (last_InsertID > last_Sequence) LOOP SELECT BVS_REQUEST_BRQ_REQUEST_ID_SEQ.NEXTVAL INTO last_Sequence FROM DUAL; END LOOP; END IF; END; When executing all statements at once, there are some missing ";" terminators (for example after the first CREATE TABLE statement). This is why Oracle complains about syntax.
        Hide
        Steve Müller added a comment -

        This is no bug, but a user error.

        Show
        Steve Müller added a comment - This is no bug, but a user error.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Eugene Morgan
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: