Ibis Oracle backend column types

[rd5802]

The following code

schema = ibis.schema(dict(foo='string', year='int32', month='int16'))
name = 'NEW_TABLE'
connection.create_table(name, schema=schema)

results in creation of table in Oracle database with column data types as follows:

foo CLOB
year NUMBER(38,0)
month NUMBER(38,0)

How can I manipulate schema types to receive VARCHAR, NUMBER with precision and scale, etc.
Should I use sqlalchemy Oracle dialect types to specify schema?

[gforsyth]

Hi @rd5802!

If you want fine-grained control over DDL, you are probably better off using raw_sql to create your tables. Something like:

In [10]: cursor = con.raw_sql("""CREATE TABLE "new_table" ("foo" 
    ...: VARCHAR2(255), "year" NUMBER(18,2), "month" NUMBER(18,2)
    ...: )""")

In [11]: cursor.close()

In [12]: con.list_tables()
Out[12]: ['new_table']

In [13]: t = con.tables.new_table

In [14]: t
Out[14]: 
DatabaseTable: new_table
  foo   string
  year  decimal(18, 2)
  month decimal(18, 2)

Ibis will treat all of Oracle's many stringy times as a string, but the specific type of string in the underlying table schema will be preserved.

Also note that you have to manage the cursor generated by the raw_sql call yourself -- just calling close() on it is sufficient.