Add max_identifier_length attribute to get connection

[CaselIT]

1. Describe your new request in detail

Oracle between 12 and 18 changed the max_identifier_length. Normally using the version property of a connection works to know what the max len is, but since Oracle allows customizing the compatible version, it may not be correct in all cases.

A property on the connection would simplify this use case.

2. Give supporting information about tools and operating systems. Give relevant product version numbers

This has came up in sqlalchemy here https://github.com/sqlalchemy/sqlalchemy/discussions/11879. Sqlalchemy at the moment uses the current query "SELECT value FROM v$parameter WHERE name = 'compatible'" to get the compatible version and from it set the max_identifier_length. The issue is that the v$parameter view (?) may not be visible to the user of the connection

[anthony-tuininga]

The ability to get the maximum identifier length in both thick and thin modes appears to be viable, so we will add this attribute to the connection object for the next release.

[CaselIT]

Will you be able to also get the compatible version and add an attribute for it too?

[anthony-tuininga]

That one is less likely but I have asked about it and will get back to you on the answer once I have it!

[CaselIT]

Ok thanks!

[anthony-tuininga]

The changes required for thin mode are as follows:

--- a/src/oracledb/connection.py
+++ b/src/oracledb/connection.py
@@ -334,6 +334,15 @@ class BaseConnection:
         self._verify_connected()
         return self._impl.get_ltxid()

+    @property
+    def max_identifier_length(self) -> int:
+        """
+        Returns the maximum length of identifiers supported by the database to
+        which this connection has been established.
+        """
+        self._verify_connected()
+        return self._impl.get_max_identifier_length()
+
     @property
     def max_open_cursors(self) -> int:
         """
diff --git a/src/oracledb/impl/base/connection.pyx b/src/oracledb/impl/base/connection.pyx
index 8cf1dbee..2110efee 100644
--- a/src/oracledb/impl/base/connection.pyx
+++ b/src/oracledb/impl/base/connection.pyx
@@ -270,6 +270,9 @@ cdef class BaseConnImpl:
     def get_ltxid(self):
         errors._raise_not_supported("getting the logical transaction id")

+    def get_max_identifier_length(self):
+        errors._raise_not_supported("getting the maximum identifier length")
+
     def get_max_open_cursors(self):
         errors._raise_not_supported(
             "getting the maximum number of open cursors"
diff --git a/src/oracledb/impl/thin/connection.pyx b/src/oracledb/impl/thin/connection.pyx
index 8bbbb563..0db10bb2 100644
--- a/src/oracledb/impl/thin/connection.pyx
+++ b/src/oracledb/impl/thin/connection.pyx
@@ -50,6 +50,7 @@ cdef class BaseThinConnImpl(BaseConnImpl):
         bytes _ltxid
         str _current_schema
         bint _current_schema_modified
+        uint8_t _max_identifier_length
         uint32_t _max_open_cursors
         str _db_domain
         str _db_name
@@ -235,6 +236,9 @@ cdef class BaseThinConnImpl(BaseConnImpl):
     def get_ltxid(self):
         return self._ltxid or b''

+    def get_max_identifier_length(self):
+        return self._max_identifier_length
+
     def get_max_open_cursors(self):
         return self._max_open_cursors

diff --git a/src/oracledb/impl/thin/messages.pyx b/src/oracledb/impl/thin/messages.pyx
index bc165988..9c24397f 100644
--- a/src/oracledb/impl/thin/messages.pyx
+++ b/src/oracledb/impl/thin/messages.pyx
@@ -1634,6 +1634,8 @@ cdef class AuthMessage(Message):
                     self.session_data.get("AUTH_SC_SERVICE_NAME")
             self.conn_impl._instance_name = \
                     self.session_data.get("AUTH_INSTANCENAME")
+            self.conn_impl._max_identifier_length = \
+                    int(self.session_data.get("AUTH_MAX_IDEN_LENGTH", 30))
             self.conn_impl.server_version = self._get_version_tuple(buf)
             self.conn_impl.supports_bool = \
                     buf._caps.ttc_field_version >= TNS_CCAP_FIELD_VERSION_23_1

The thick mode changes will take more effor.t.

[anthony-tuininga]

I have pushed a patch that adds this support and have initated a build from which you can download pre-built development wheels once it completes. You can also build from source if you prefer.

[anthony-tuininga]

This was included in python-oracledb 2.5.0 which was just released.

[CaselIT]

Hi @anthony-tuininga

I'm trying to add support for this in sqlalchemy but I'm getting a error in thick mode in the ci (that uses version 18):

221:37:38   File "/home/jenkins/workspace/sqlalchemy_21_gerrit/ee662925/sqlalchemy/sqlalchemy/.tox/py312-cext-oracle-backendonly/lib/python3.12/site-packages/sqlalchemy/dialects/oracle/oracledb.py", line 222, in _check_max_identifier_length
21:37:38     return connection.connection.max_identifier_length
21:37:38            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
21:37:38   File "/home/jenkins/workspace/sqlalchemy_21_gerrit/ee662925/sqlalchemy/sqlalchemy/.tox/py312-cext-oracle-backendonly/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1183, in __getattr__
21:37:38     return getattr(self.dbapi_connection, key)
21:37:38            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
21:37:38   File "/home/jenkins/workspace/sqlalchemy_21_gerrit/ee662925/sqlalchemy/sqlalchemy/.tox/py312-cext-oracle-backendonly/lib/python3.12/site-packages/oracledb/connection.py", line 360, in max_identifier_length
21:37:38     return self._impl.get_max_identifier_length()
21:37:38            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
21:37:38   File "src/oracledb/impl/thick/connection.pyx", line 668, in oracledb.thick_impl.ThickConnImpl.get_max_identifier_length
21:37:38   File "src/oracledb/impl/thick/utils.pyx", line 456, in oracledb.thick_impl._raise_from_odpi
21:37:38   File "src/oracledb/impl/thick/utils.pyx", line 446, in oracledb.thick_impl._raise_from_info
21:37:38 oracledb.exceptions.DatabaseError: ORA-24315: illegal attribute type

I've tried locally and the same test is fine on 23 and 18, so not really sure what's up. Thin mode works ok both locally and in the CI

Pinging also @zzzeek since it may be due to some config on the db?

The run is https://jenkins.sqlalchemy.org/job/sqlalchemy_21_gerrit/cext=cext,db=oracle-backendonly,pyv=py312/838/ (it may be deleted in a few days) while the change is https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/5551

[cjbj]

Bleh. Sounds like some buglet. What's the exact DB version you are using, and the exact Oracle Client library (if it's different)?

[CaselIT]

the db version seems to be 18_0_0_0_0, but I'm not 100% sure about the particular version. Not sure about client lib.

@zzzeek can you find them?

[zzzeek]

the client libraries we use are the older 11.2.0 XE version. i am not able to get newer client libraries to install without doing the whole server setup which is prohibitive

[CaselIT]

the docker image that is run in the CI is the one defined here mike? https://github.com/sqlalchemyorg/ci_containers/tree/main/roles/oracle/files

[zzzeek]

the client libraries come from here:

https://github.com/sqlalchemyorg/ci_containers/blob/28d71675df2c32044040cd0bae0e54766feb2c44/roles/jenkins/files/Dockerfile.dbdrivers#L41

[anthony-tuininga]

Looks like version 11.2 support was not checked. We are confirming internally and will create a patch to address it -- but 11.2? Really? ;-)

[zzzeek]

What can I install on linux to get just the client libraries for Oracle that doesn't require I set aside a ton of extra swap space and install the whole server? can you point me to a download ?

[zzzeek]

also using red hat tools, not debian

[CaselIT]

I think the best option is to just use the old path when using the thick for this in sqlalchemy. Any objection on your part @zzzeek ?

[zzzeek]

why dont we wait for the fix?

[anthony-tuininga]

I think the best option is to just use the old path when using the thick for this in sqlalchemy.

You can also check the client version. If 11.2 you already know the answer and don't need to do any checks. If > 11.2 you can use the new attribute safely. That's what I plan to do anyway in ODPI-C!

[anthony-tuininga]

What can I install on linux to get just the client libraries for Oracle that doesn't require I set aside a ton of extra swap space and install the whole server? can you point me to a download ?

The simplest is to download the instant client. There are RPMs available for Oracle Linux (which is a clone of Red Hat Linux). Would that work for you?

[zzzeek]

the last time I tried instant client is was some weird thing written all in Java and did not provide .so files. I'll look again

[CaselIT]

why dont we wait for the fix?

since it's client lib dependent it may be that ancient versions may have other unaccounted issues too. Also I have the impression that most people using oracledb use it in thin mode.

Probably checking the client version in thick mode is the best option.

[anthony-tuininga]

the last time I tried instant client is was some weird thing written all in Java and did not provide .so files. I'll look again

You're thinking of the full client -- what you had to use before instant client became available. That thing is still written in Java. :-) The instant client has always been a simple zip file containing the binaries. It should work well for you, I think!

[zzzeek]

im installing it now, seems OK so far

[cjbj]

@zzzeek if you're playing with Docker and Dockerfiles, I'd recommend jumping to the 23ai image https://hub.docker.com/r/gvenzl/oracle-free from the Oracle DB PM team.

[zzzeek]

how does oracle "free" differ from the traditional oracle "express" ? can I install "free" in my own docker images?

[cjbj]

@zzzeek "Free" is the new name for "XE". You can download it from https://www.oracle.com/au/database/technologies/oracle-database-software-downloads.html#db_free if you want to install it yourself. Note that unlike XE, there will be new 'release updates' of Free.

[zzzeek]

the constant name changes sure make it easy to find things on oracle's site :)

[cjbj]

@zzzeek the decision to rename it (after ~15 years) was taken way above my pay grade.

[cjbj]

@zzzeek @CaselIT the plan is that python-oracledb will now have Connection.max_identifier_length set to 0 for the cases that python-oracledb can't internally get the value. This will only happen when using Thick mode, using Oracle Client 12.1 (or lower) to connect to Oracle Database 12.2 (or higher).

[CaselIT]

Ok, I can update sqlalchemy to reflect that. Maybe None is better than 0 though?

[cjbj]

@anthony-tuininga do you want to map the DPI 0 to a Python None?

[anthony-tuininga]

I can make python-oracledb return None instead of 0, sure.

[anthony-tuininga]

I have pushed a patch that corrects the handling of this attribute in thick mode when using older client libraries. I have initated a build from which you can download pre-built development wheels once it completes. You can also build from source if you prefer.

[CaselIT]

Thanks, I've updated the implementation to test for none.