Having trouble reading data from another schema in Oracle database

[tweakyTweeter]

Hi,

I'm using ibis-framework[oracle]==9.3.0 to connect to an oracle database to read a table from another schema. Here is an example of the actual code that I'm running:

import oracledb

# I need this since I'm connecting to Oracle in "thick mode" 
# due to TLS settings that only work with Thick mode
oracledb.init_oracle_client(lib_dir="Oracle/instantclient_23_3")

db = ibis.oracle.connect(
    user = 'test_user',
    password = 'password123',
    dsn = 'FREEPDB1'
)

db.table(name = "TEST_TABLE", database = "DUMMY_SCHEMA")

I keep getting the following error:

DatabaseError: ORA-00923: FROM keyword not found where expected

Full traceback is as follows:

---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
Cell In[8], line 1
----> 1 db.table(name = \"TEST_TABLE\", database = \"DUMMY_SCHEMA\")

File ~/.pyenv/versions/3.11.9/lib/python3.11/site-packages/ibis/backends/sql/__init__.py:140, in SQLBackend.table(self, name, schema, database)
    137     catalog = table_loc.catalog or None
    138     database = table_loc.db or None
--> 140 table_schema = self.get_schema(name, catalog=catalog, database=database)
    141 return ops.DatabaseTable(
    142     name,
    143     schema=table_schema,
    144     source=self,
    145     namespace=ops.Namespace(catalog=catalog, database=database),
    146 ).to_expr()

File ~/.pyenv/versions/3.11.9/lib/python3.11/site-packages/ibis/backends/oracle/__init__.py:348, in Backend.get_schema(self, name, catalog, database)
    332     database = self.con.username.upper()
    333 stmt = (
    334     sg.select(
    335         C.column_name,
   (...)
    346     .order_by(C.column_id)
    347 )
--> 348 with self._safe_raw_sql(stmt) as cur:
    349     results = cur.fetchall()
    351 if not results:

File ~/.pyenv/versions/3.11.9/lib/python3.11/contextlib.py:137, in _GeneratorContextManager.__enter__(self)
    135 del self.args, self.kwds, self.func
    136 try:
--> 137     return next(self.gen)
    138 except StopIteration:
    139     raise RuntimeError(\"generator didn't yield\") from None

File ~/.pyenv/versions/3.11.9/lib/python3.11/site-packages/ibis/backends/oracle/__init__.py:217, in Backend._safe_raw_sql(self, *args, **kwargs)
    215 @contextlib.contextmanager
    216 def _safe_raw_sql(self, *args, **kwargs):
--> 217     with contextlib.closing(self.raw_sql(*args, **kwargs)) as result:
    218         yield result

File ~/.pyenv/versions/3.11.9/lib/python3.11/site-packages/ibis/backends/oracle/__init__.py:228, in Backend.raw_sql(self, query, **kwargs)
    225 cursor = con.cursor()
    227 try:
--> 228     cursor.execute(query, **kwargs)
    229 except Exception:
    230     con.rollback()

File ~/.pyenv/versions/3.11.9/lib/python3.11/site-packages/oracledb/cursor.py:383, in Cursor.execute(self, statement, parameters, **keyword_parameters)
    381 if parameters is not None:
    382     impl.bind_one(self, parameters)
--> 383 impl.execute(self)
    384 if impl.fetch_vars is not None:
    385     return self

File src/oracledb/impl/thick/cursor.pyx:243, in oracledb.thick_impl.ThickCursorImpl.execute()

File src/oracledb/impl/thick/utils.pyx:431, in oracledb.thick_impl._raise_from_odpi()

File src/oracledb/impl/thick/utils.pyx:421, in oracledb.thick_impl._raise_from_info()

DatabaseError: ORA-00923: FROM keyword not found where expected
Help: https://docs.oracle.com/error-help/db/ora-00923/"
}

I think the issue is the get_schema method, specifically in the following lines:

        stmt = (
            sg.select(
                C.column_name,
                C.data_type,
                C.data_precision,
                C.data_scale,
                C.nullable.eq(sge.convert("Y")).as_("nullable"),
            )
            .from_(sg.table("all_tab_columns"))
            .where(
                C.table_name.eq(sge.convert(name)),
                C.owner.eq(sge.convert(database)),
            )
            .order_by(C.column_id)
        )
        with self._safe_raw_sql(stmt) as cur:
            results = cur.fetchall()

sqlglot translates stmt to the following SQL query:

SELECT 
column_name, 
data_type, 
data_precision, 
data_scale, 
nullable = 'Y' AS nullable 
FROM all_tab_columns 
WHERE table_name = 'TEST_TABLE' 
AND owner = 'DUMMY_SCHEMA' ORDER BY column_id

It seems like C.nullable.eq(sge.convert("Y")).as_("nullable") isn't translating correctly for Oracle since nullable = 'Y' AS nullable is not a valid Oracle syntax.

Has anyone else faced a similar issue?

[gforsyth]

Ahh, what version of Oracle is this? We currently only have robust support for 23+, since that's when Oracle decided that booleans were a valid datatype.

[tweakyTweeter]

Ah, I see. Yes, I'm on version 19.0.0.0. I see there was a fix proposed in #9379.

Is there any chance the fix could be implemented in upcoming versions since many folks still use an older version of Oracle? I really like this package. I'm moving from R to Python and have been looking for something equivalent to R's dbplyr package and this package provides dbplyr's exact functionality and usability.

[gforsyth]

Hey @tweakyTweeter -- it's a pretty hairy problem, especially because sqlglot also doesn't support Oracle version <23.
I don't know that we'll add support for <23 anytime soon. I recognize that isn't a very satisfying answer.

[tweakyTweeter]

@gforsyth, thanks for responding and no worries. I'll just wait for our team to move to Oracle version 23.