Kulbear Kulbear - 4 months ago 9
MySQL Question

Simple table creation sql works for mysql and postgre, but reported errors in oracle

I am using Python with cx_Oracle to work with Oracle 11g.

I have following SQL work well with MySQL/PostgreSQL,

stock_info_create = 'CREATE TABLE STOCK_INFO(' + \
'CODE VARCHAR2(255) NOT NULL,' + \
'NAME VARCHAR2(255) NOT NULL,' + \
'TIMETOMARKET VARCHAR2(255),' + \
'PRIMARY KEY (CODE)' + \
');'


And the returned message was

(cx_Oracle.DatabaseError) ORA-00911: invalid character
[SQL: 'CREATE TABLE STOCK_INFO(CODE VARCHAR2(255) NOT NULL,NAME VARCHAR2(255) NOT NULL,TIMETOMARKET VARCHAR2(255),PRIMARY KEY (CODE));']


Of course, to use this sql for Oracle I changed VARCHAR to VARCHAR2 since I thought the error was caused by wrong data type. But after changing to VARCHAR2 it still didn't work.

Another problem is with table creation as well.

stock_h_create = 'CREATE TABLE STOCK_H_NONE(' + \
'CODE VARCHAR(16) NOT NULL,' + \
'DATE DATE,' + \
'OPEN FLOAT,' + \
'HIGH FLOAT,' + \
'CLOSE FLOAT,' + \
'LOW FLOAT,' + \
'VOLUME FLOAT,' + \
'AMOUNT FLOAT,' + \
'AUTYPE VARCHAR(16),' + \
'LAST_UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,' + \
'PRIMARY KEY (CODE,AUTYPE,DATE)' + \
');'


The returned message was

Creating table STOCK_H_NONE... (cx_Oracle.DatabaseError) ORA-00904: : invalid identifier
[SQL: 'CREATE TABLE STOCK_H_NONE(CODE VARCHAR(16) NOT NULL,DATE DATE,OPEN FLOAT,HIGH FLOAT,CLOSE FLOAT,LOW FLOAT,VOLUME FLOAT,AMOUNT FLOAT,AUTYPE VARCHAR(16),LAST_UPDATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,PRIMARY KEY (CODE,AUTYPE,DATE));']


I appreciate any kind of help and your visit.

Answer

Certain reserved words in Oracle are not allowed to be used as column names - data types are among those reserved words, e.g. Date, Number. You got an "ORA-00904: invalid identifier" error because of your Date column. For good practice, try and avoid using reserved words as column names.