DBAL-699: toSql() for Oracle table with auto increment column produces SQL errors #1921

Closed
doctrinebot opened this Issue Dec 5, 2013 · 3 comments

2 participants

@doctrinebot

Jira issue originally created by user eugenemorgan:

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;
@doctrinebot

Comment created by @deeky666:

[~eugenemorgan] 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. sqlCREATE 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. 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. sqlCREATE SEQUENCE BVS*REQUEST_BRQ_REQUEST_ID*SEQ START WITH 1 MINVALUE 1 INCREMENT BY 1

  4. sqlCREATE 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.

@doctrinebot

Comment created by @deeky666:

This is no bug, but a user error.

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot doctrinebot added the Bug label Dec 7, 2015
@beberlei beberlei was assigned by doctrinebot Dec 7, 2015
@doctrinebot doctrinebot closed this Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment