2

I am trying to create a table, but keep getting the following error message: Warning: oci_execute(): ORA-00907: missing right parenthesis in ... on line 14

The following is the code that deals with this issue:

$stid = oci_parse($conn, 'CREATE TABLE tags (
                            id INT NOT NULL auto_increment, 
                            PRIMARY KEY(id), 
                            name VARCHAR2(64) NOT NULL)') 
        or die(oci_error($conn));
oci_execute($stid) or die(oci_error($conn));

Line 14 is oci_execute($stid) or die(oci_error($conn));.

I am new to Oracle and don't understand this error. I used Google and found numerous posts on StackOverflow too, but none of those answers were able to solve this problem and properly create a new table.

What am I doing wrong here?

Charlesliam
  • 1,293
  • 3
  • 20
  • 36
DemCodeLines
  • 1,870
  • 8
  • 41
  • 60
  • The "Missing right parenthesis" error seems to be a red herring here. The problem is `auto_increment`. http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle – Michael Berkowski Jul 30 '14 at 01:06

1 Answers1

3

I believe your used to creating table in MySQL. Oracle don't have an auto_increment as MySQL. 

$stid = oci_parse($conn, 'CREATE TABLE tags (
                           id INT NOT NULL,
                           name VARCHAR2(64) NOT NULL),
                           PRIMARY KEY(id)')

Oracle used object Sequence to create an auto_increment value.

Example on how to create sequence assuming you have the right permission.

CREATE SEQUENCE  "CCAD"."AUTH_GROUP_SQ"      
MINVALUE 1 
MAXVALUE 999999999999 
INCREMENT BY 1 
START WITH 91 
CACHE 20 
NOORDER  NOCYCLE;

Together with Insert trigger.

create or replace TRIGGER "AUTH_GROUP_TR"
BEFORE INSERT ON "AUTH_GROUP"
FOR EACH ROW
WHEN (new."ID" IS NULL)
BEGIN
    SELECT "AUTH_GROUP_SQ".nextval
    INTO :new."ID" FROM dual;
END;
Charlesliam
  • 1,293
  • 3
  • 20
  • 36
  • I took out the auto_increment and it worked, but I need the id column to auto increment. Is there an Oracle equivalent of that? – DemCodeLines Jul 30 '14 at 01:08
  • @DemCodeLines The question you linked to in your post tells you how to implement the closest thing in Oracle. – Matt M Jul 30 '14 at 01:11
  • @DemCodeLines - If your version is `12c` or higher, have a look at [Oracle: how to create an identity column?](http://stackoverflow.com/questions/2053313/oracle-how-to-create-an-identity-column). – PM 77-1 Jul 30 '14 at 01:14