-2
try {
    String strQry= "CREATE TABLE IF NOT EXISTS ERROR_DETAIL (\"SNO\"NUMBER(16,0),\"TABLE_NAME\"  VARCHAR2(32 BYTE),\"ERROR_DESC\" VARCHAR2(255 BYTE),\"XCOORDINATE\" FLOAT(126),\"YCOORDINATE\" FLOAT(126),\"STATUS\"  VARCHAR2(30 BYTE),\"COMMENT\" Varchar2(255 Byte),PRIMARY KEY (\"TABLE_NAME\", \"ERROR_DESC\") ENABLE);";
    stmt = con.createStatement();
    stmt.execute(strQry);

} catch (Exception e) {
    e.printStackTrace();
}

this code giving Error:

java.sql.SQLSyntaxErrorException: ORA-00922: missing or invalid option

but same query working properly in oracle

SimplyPanda
  • 725
  • 7
  • 16
Sachan
  • 1
  • 1
  • Possible duplicate. http://stackoverflow.com/questions/2477099/create-table-if-not-exists-how-to-check-the-schema-too – amicngh Jun 07 '12 at 13:01
  • 2
    [IF NOT EXISTS -DDL- doesn't exist in Oracle](http://stackoverflow.com/questions/2618179/is-there-something-like-if-not-exist-create-sequence-in-oracle-sql). You'll have to either check if the object exists beforehand using the dictionary tables OR catch the appropriate error. – Vincent Malgrat Jun 07 '12 at 13:47
  • Is it really that hard to check the correct syntax in the manual? –  Nov 06 '14 at 08:54

2 Answers2

0

Print the content of your strQry variable, and you'll see that it's not valid:

  • you don't need double quotes around column names
  • you should have a space between each column name and its type
  • you shouldn't have a semi-colon at the end of the query

Moreover, I think you should use executeUpdate() and not execute().

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • Now ihave done change according to you but still not working. String strQry= "CREATE TABLE ERROR_DETAIL (SNO NUMBER(16,0),TABLE_NAME VARCHAR2(32 BYTE),ERROR_DESC VARCHAR2(255 BYTE),XCOORDINATE FLOAT(126),YCOORDINATE FLOAT(126),STATUS VARCHAR2(30 BYTE),COMMENT Varchar2(255 Byte),PRIMARY KEY (TABLE_NAME, ERROR_DESC) ENABLE)"; Now error changes java.sql.SQLSyntaxErrorException: ORA-00904: : invalid identifier – Sachan Jun 07 '12 at 13:12
  • 1
    COMMENT is not a valid column name. Choose another name. – JB Nizet Jun 07 '12 at 13:17
0

Try to use below piece of code. 

try {
    String strQry= " CREATE TABLE IF NOT EXISTS ERROR_DETAIL (SNO NUMBER(16,0),TABLE_NAME VARCHAR2(32 BYTE),ERROR_DESC VARCHAR2(255 BYTE),XCOORDINATE FLOAT(126),YCOORDINATE FLOAT(126),STATUS VARCHAR2(30 BYTE),COMMENT Varchar2(255 Byte),PRIMARY KEY (TABLE_NAME, ERROR_DESC) ENABLE)";
    stmt = con.createStatement();    
    stmt.execute(strQry);                    

} catch (Exception e) { e.printStackTrace(); }

Change is just remove the ; inside " (i.e in the given code it is ENABLE);";

I am changing it to ENABLE)";

parakmiakos
  • 2,994
  • 9
  • 29
  • 43
Bency
  • 1