Creating table with all built in datatypes result in ORA-00922 (same ddl works in any other oracle client.

[stepanovdg]

We are trying to create dynamically table with all build in oracle datatypes (and fill some data) to use for testing of datatypes conversions in our application. It appears that valid ddl statement is failing when use oracledb. Same table is created successfully in commandline or dbveawer. (maybe thin/thick mode related). But is strange create table not work in thin.

1. What versions are you using?

platform.platform: Windows-11-10.0.22631-SP0 sys.maxsize > 2**32: True platform.python_version: 3.12.4 oracledb.version: 2.2.1

2. Error:

   File "src\\oracledb\\impl/thin/cursor.pyx", line 178, in oracledb.thin_impl.ThinCursorImpl.execute
   File "src\\oracledb\\impl/thin/protocol.pyx", line 438, in oracledb.thin_impl.Protocol._process_single_message
   File "src\\oracledb\\impl/thin/protocol.pyx", line 439, in oracledb.thin_impl.Protocol._process_single_message
   File "src\\oracledb\\impl/thin/protocol.pyx", line 432, in oracledb.thin_impl.Protocol._process_message
   oracledb.exceptions.DatabaseError: ORA-00922: missing or invalid option
   Help: https://docs.oracle.com/error-help/db/ora-00922/

3. Does your application call init_oracle_client()?

Thin mode.

6. Include a runnable Python script that shows the problem.

import oracledb

ddl = '''CREATE TABLE "DATATYPES" 
(   
    "FIELD_LONG" LONG RAW NOT NULL ENABLE,

    "FIELD_CHAR" CHAR NOT NULL ENABLE,
    "FIELD_CHAR_NULL" CHAR,
    "FIELD_CHAR_256" CHAR(256) NOT NULL ENABLE,
    "FIELD_CHAR_256_NULL" CHAR(256),
    "FIELD_VARCHAR2_256" VARCHAR2(256) NOT NULL ENABLE,
    "FIELD_VARCHAR2_256_NULL" VARCHAR2(256),
    "FIELD_NCHAR" NCHAR NOT NULL ENABLE,
    "FIELD_NCHAR_NULL" NCHAR,
    "FIELD_NCHAR_256" NCHAR(256) NOT NULL ENABLE,
    "FIELD_NCHAR_256_NULL" NCHAR(256),
    "FIELD_NVARCHAR2_256" NVARCHAR2(256) NOT NULL ENABLE,
    "FIELD_NVARCHAR2_256_NULL" NVARCHAR2(256),
    "FIELD_CLOB" CLOB NOT NULL ENABLE,
    "FIELD_CLOB_NULL" CLOB,
    "FIELD_NCLOB" NCLOB NOT NULL ENABLE,
    "FIELD_NCLOB_NULL" NCLOB,
    "FIELD_BLOB" BLOB NOT NULL ENABLE,
    "FIELD_BLOB_NULL" BLOB,
    "FIELD_BFILE" BFILE NOT NULL ENABLE,
    "FIELD_BFILE_NULL" BFILE,
    "FIELD_NUMBER" NUMBER NOT NULL ENABLE,
    "FIELD_NUMBER_NULL" NUMBER,
    "FIELD_NUMBER_3" NUMBER(3) NOT NULL ENABLE,
    "FIELD_NUMBER_3_NULL" NUMBER(3),
    "FIELD_NUMBER_*_1" NUMBER(*,1) NOT NULL ENABLE,
    "FIELD_NUMBER_*_1_NULL" NUMBER(*,1),
    "FIELD_NUMBER_3_-1" NUMBER(3,-1) NOT NULL ENABLE,
    "FIELD_NUMBER_3_-1_NULL" NUMBER(3,-1),
    "FIELD_FLOAT" FLOAT NOT NULL ENABLE,
    "FIELD_FLOAT_NULL" FLOAT,
    "FIELD_FLOAT_1" FLOAT(1) NOT NULL ENABLE,
    "FIELD_FLOAT_1_NULL" FLOAT(1),
    "FIELD_INTEGER" INTEGER NOT NULL ENABLE,
    "FIELD_INTEGER_NULL" INTEGER,
    "FIELD_DATE" DATE NOT NULL ENABLE,
    "FIELD_DATE_NULL" DATE,
    "FIELD_BINARY_FLOAT" BINARY_FLOAT NOT NULL ENABLE,
    "FIELD_BINARY_FLOAT_NULL" BINARY_FLOAT,
    "FIELD_BINARY_DOUBLE" BINARY_DOUBLE NOT NULL ENABLE,
    "FIELD_BINARY_DOUBLE_NULL" BINARY_DOUBLE,
    "FIELD_TIMESTAMP" TIMESTAMP NOT NULL ENABLE,
    "FIELD_TIMESTAMP_NULL" TIMESTAMP,
    "FIELD_TIMESTAMP_6" TIMESTAMP(6) NOT NULL ENABLE,
    "FIELD_TIMESTAMP_6_NULL" TIMESTAMP(6),
    "FIELD_TIMESTAMP_9" TIMESTAMP(9) NOT NULL ENABLE,
    "FIELD_TIMESTAMP_9_NULL" TIMESTAMP(9),
    "FIELD_TIMESTAMP_WITH_TIME_ZONE" TIMESTAMP WITH TIME ZONE NOT NULL ENABLE,
    "FIELD_TIMESTAMP_WITH_TIME_ZONE_NULL" TIMESTAMP WITH TIME ZONE,
    "FIELD_TIMESTAMP_6_WITH_TIME_ZONE" TIMESTAMP(6) WITH TIME ZONE NOT NULL ENABLE,
    "FIELD_TIMESTAMP_6_WITH_TIME_ZONE_NULL" TIMESTAMP(6) WITH TIME ZONE,
    "FIELD_TIMESTAMP_9_WITH_TIME_ZONE" TIMESTAMP(9) WITH TIME ZONE NOT NULL ENABLE,
    "FIELD_TIMESTAMP_9_WITH_TIME_ZONE_NULL" TIMESTAMP(9) WITH TIME ZONE,
    "FIELD_TIMESTAMP_WITH_LOCAL_TIME_ZONE" TIMESTAMP WITH LOCAL TIME ZONE NOT NULL ENABLE,
    "FIELD_TIMESTAMP_WITH_LOCAL_TIME_ZONE_NULL" TIMESTAMP WITH LOCAL TIME ZONE,
    "FIELD_TIMESTAMP_6_WITH_LOCAL_TIME_ZONE" TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL ENABLE,
    "FIELD_TIMESTAMP_6_WITH_LOCAL_TIME_ZONE_NULL" TIMESTAMP(6) WITH LOCAL TIME ZONE,
    "FIELD_TIMESTAMP_9_WITH_LOCAL_TIME_ZONE" TIMESTAMP(9) WITH LOCAL TIME ZONE NOT NULL ENABLE,
    "FIELD_TIMESTAMP_9_WITH_LOCAL_TIME_ZONE_NULL" TIMESTAMP(9) WITH LOCAL TIME ZONE,
    "FIELD_INTERVAL_YEAR_TO_MONTH" INTERVAL YEAR TO MONTH NOT NULL ENABLE,
    "FIELD_INTERVAL_YEAR_TO_MONTH_NULL" INTERVAL YEAR TO MONTH,
    "FIELD_INTERVAL_YEAR_0_TO_MONTH" INTERVAL YEAR(0) TO MONTH NOT NULL ENABLE,
    "FIELD_INTERVAL_YEAR_0_TO_MONTH_NULL" INTERVAL YEAR(0) TO MONTH,
    "FIELD_INTERVAL_DAY_TO_SECOND" INTERVAL DAY TO SECOND NOT NULL ENABLE,
    "FIELD_INTERVAL_DAY_TO_SECOND_NULL" INTERVAL DAY TO SECOND,
    "FIELD_INTERVAL_DAY_0_TO_SECOND" INTERVAL DAY(0) TO SECOND NOT NULL ENABLE,
    "FIELD_INTERVAL_DAY_0_TO_SECOND_NULL" INTERVAL DAY(0) TO SECOND,
    "FIELD_INTERVAL_DAY_TO_SECOND_0" INTERVAL DAY TO SECOND(0) NOT NULL ENABLE,
    "FIELD_INTERVAL_DAY_TO_SECOND_0_NULL" INTERVAL DAY TO SECOND(0),
    "FIELD_INTERVAL_DAY_9_TO_SECOND_9" INTERVAL DAY(9) TO SECOND(9) NOT NULL ENABLE,
    "FIELD_INTERVAL_DAY_9_TO_SECOND_9_NULL" INTERVAL DAY(9) TO SECOND(9),
    "FIELD_RAW_256" RAW(256) NOT NULL ENABLE,
    "FIELD_RAW_256_NULL" RAW(256),
    "FIELD_UROWID" UROWID NOT NULL ENABLE,
    "FIELD_UROWID_NULL" UROWID,
    "FIELD_UROWID_256" UROWID(256) NOT NULL ENABLE,
    "FIELD_UROWID_256_NULL" UROWID(256)
)

with get_conn().cursor() as cursor:
  cursor.execute(ddl)

[anthony-tuininga]

I just tried this (after adding the missing ending triple quote in your code above) and it worked just fine for me! What database (platform and version) are you using? I just tried with 23.4 on Linux.

[stepanovdg]

@anthony-tuininga Yes sorry for triple qoute missing. Incorrectly copied. Just checked - we have cluster on ec2 running oracle 19c enterprise.

Also just rechecked - its the same error. I wrote simple generate sql query function that just iterate over datatypes (two columns null and not null version). So have that query printed and and executed. Execute (getConnection helper method works fine as executing all other queries good - it is just something with that particular) And in order to check if I had problems in syntax I copied into dbveawer with session to the same db and it successfully created table (without any change). I assume maybe its realted to thin client. But it is strange. And error itself related to not null constraint.

So I am unblocked for development - just using manually created that query but wanted to create it also in our automated tests later.( and its failing ()

[cjbj]

@stepanovdg as a workaround for automated tests, how about creating it in PL/SQL?:

SQL> @t
SQL> begin
  2    execute immediate 'drop table if exists mytab';
  3    execute immediate 'create table mytab (c number)';
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> desc mytab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C                                                  NUMBER

[anthony-tuininga]

@stepanovdg, the error you are getting (ORA-00922: missing or invalid option) is coming from the server. It might be useful to get the packet output (set the environment variable PYO_DEBUG_PACKETS to any value before running your script). The only packets required are the ones where the DDL is sent and the response received. Hopefully this will uncover the mystery!

[cjbj]

@stepanovdg Are you able to get the requested trace?