Home / Questions / 532004
Accepted
MxiDev
Asked: 2022-07-05 11:29:03 +0800 CST 2022-07-05 11:29:03 +0800 CST

ORA-00907: missing right parenthesis when creating table

  • 772

I am trying to create a table in my database to simulate an application. The issue is that it throws me the error

ORA-00907: missing right parenthesis

and I don't know why it occurs (it clearly mentions the right parenthesis, but according to me it is said parenthesis). What I want to achieve is that the id_game attribute is automatically incremented. I tell you that I am working on Oracle 18c XE. I present the code below:

CREATE TABLE BIBLIOTECA(
        id_game INTEGER PRIMARY KEY DEFAULT nextval('serial'),
        rank INTEGER NOT NULL,
        name VARCHAR(64) NOT NULL
    );
sql

1 Answers

  1. Best Answer

    Since version 12c Oracle changed the way to generate this type of columns.

    Now you should use:

    GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

    In your case, this should work:

    CREATE TABLE BIBLIOTECA(
        id_game INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
        rank INTEGER NOT NULL,
        name VARCHAR(64) NOT NULL
    );

    If you want other kinds of options, you can change them freely.

    For more details, please refer to this tutorial or refer directly to the Oracle documentation .

    fiddler

    CREATE TABLE BIBLIOTECA(
        id_game INTEGER GENERATED ALWAYS AS IDENTITY(START with 1 INCREMENT by 1),
        rank INTEGER NOT NULL,
        name VARCHAR(64) NOT NULL
    );
    

    ✓

    INSERT INTO BIBLIOTECA  (rank, name) VALUES (10,'name 10');

    

    1 rows affected
    

    


    


    

    INSERT INTO BIBLIOTECA  (rank, name) VALUES (20,'name 20');

    

    

    1 rows affected
    

    


    


    

    INSERT INTO BIBLIOTECA  (rank, name) VALUES (30,'name 30');

    

    

    

    

    1 rows affected
    

    


    


    

    SELECT * FROM BIBLIOTECA;

    

    ID_GAME | RANK | YAM   
------: | ---: | :------
      1 | 10 | names 10
      2 | 20 | name 20
      3 | 30 | name 30

    

    

    db<>fiddle here
    
                                                                
    
                                                                
    
                                                                
    
                                                                
    
                                                                
    
                                                                    
      
                                                                        
      • 
                                                                        
    • 
                                                                            1
                                                                        
      • 
                                                                        
    • 
                                                                        
      • 
                                                                        
      • 
                                                                    
    


                                                                
    
                                                            
                                                            
    
                                                        
    
                                                     
    2.