New Gerrit review created for change f5fdd03: https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6547

this looks very nice, everything is there. just need to see if it actually works ! :) since oracle can be very difficult. will add to CI (now that we have oracle 23 running)

I use this:

$ docker pull gvenzl/oracle-free:latest
$ docker run -d \
  --name oracle-free \
  -p 1521:1521 \
  -e ORACLE_PASSWORD=STRONGpassword123 \
  -e APP_USER=scott \
  -e APP_USER_PASSWORD=tiger \
  gvenzl/oracle-free:latest

And I added this link to my test.cfg

oracle=oracle+oracledb://scott:tiger@localhost:1521/?service_name=FREEPDB1%

And I ran the following and the newly added tests turned green:)

$$ LD_LIBRARY_PATH=/opt/oracle/instantclient_21_20:$LD_LIBRARY_PATH pytest test/dialect/oracle/test_types.py --db=oracle --dbdriver=oracledb -v

so we have some pep484 failures, and the main failure for Oracle is that the JSON suite doesnt include IDENTITY for the table, so we need to add this:

diff --git a/lib/sqlalchemy/testing/suite/test_types.py b/lib/sqlalchemy/testing/suite/test_types.py
index bf720d919..c8714d5ed 100644
--- a/lib/sqlalchemy/testing/suite/test_types.py
+++ b/lib/sqlalchemy/testing/suite/test_types.py
@@ -1318,7 +1318,9 @@ class JSONTest(_LiteralRoundTripFixture, fixtures.TablesTest):
         Table(
             "data_table",
             metadata,
-            Column("id", Integer, primary_key=True),
+            Column(
+                "id", Integer, primary_key=True, test_needs_autoincrement=True
+            ),
             Column("name", String(30), nullable=False),
             Column("data", cls.datatype, nullable=False),
             Column("nulldata", cls.datatype(none_as_null=True)),
@@ -1936,7 +1938,9 @@ class JSONLegacyStringCastIndexTest(
         Table(
             "data_table",
             metadata,
-            Column("id", Integer, primary_key=True),
+            Column(
+                "id", Integer, primary_key=True, test_needs_autoincrement=True
+            ),
             Column("name", String(30), nullable=False),
             Column("data", cls.datatype),
             Column("nulldata", cls.datatype(none_as_null=True)),

Patchset f69051f added to existing Gerrit review https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/6547

errors are at https://jenkins-static.sqlalchemy.org/static-files/33ISLIt8YqsIbB-1X1LB3kUG4PUOpAGkTBcxatxC9lUxNzY3NzU1MTcxMzU4Ojk6YW5vbnltb3VzOmpvYi9zcWxhbGNoZW15L2pvYi9nZXJyaXRfMjEvNDQ2L2FydGlmYWN0/FAILED-py313-oracle-cext-backendonly-test-report.html

this is unfortunately quite complicated. the "supports_json" qualifier for oracle needs to be limited to the oracledb driver and not the older cx_oracle driver, which is why most of those failures are occurring. it might be for all of them so we would start by limiting to oracledb

it might work like this

diff --git a/test/requirements.py b/test/requirements.py
index dbeb287c3..39b1fcd95 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -1194,8 +1194,9 @@ class DefaultRequirements(SuiteRequirements):
                 "postgresql >= 9.3",
                 self._sqlite_json,
                 "mssql",
+                "oracle>=21",
             ]
-        )
+        ) + skip_if("oracle+cx_oracle")
 
     @property
     def json_index_supplementary_unicode_element(self):

also it would be helpful if you could run these tests locally, you can get an Oracle db running using the instructions in https://github.com/sqlalchemy/sqlalchemy/blob/main/README.unittests.rst for "Oracle" shows how to run a container and set up the DB

also it would be helpful if you could run these tests locally, you can get an Oracle db running using the instructions in https://github.com/sqlalchemy/sqlalchemy/blob/main/README.unittests.rst for "Oracle" shows how to run a container and set up the DB

I have run pytest --db=oracle -v --dropfirst on the main branch, and I got this:

!!!!!!!!!!!!!!!!!!!!!!!!! stopping after 250 failures !!!!!!!!!!!!!!!!!!!!!!!!!!
====== 32 failed, 781 passed, 9 skipped, 218 errors in 303.87s (0:05:03) =======

when i rn the same on my branch, I also get the same:

!!!!!!!!!!!!!!!!!!!!!!!!! stopping after 250 failures !!!!!!!!!!!!!!!!!!!!!!!!!!
====== 32 failed, 781 passed, 9 skipped, 218 errors in 299.22s (0:04:59) =======

I can attach the full logs if it will be useful, but I have attached sample from the errors.

When I run pytest --db=oracle -v --dropfirst test/dialect/oracle/, it all green:

================================================================================== short test summary info ===================================================================================
SKIPPED [1] lib/sqlalchemy/testing/config.py:436: 'test/dialect/oracle/test_reflection.py::AdditionalReflectionTests_oracle+oracledb_23_26_0_0_0::test_multi_dblink_synonyms (call)' : oracle_db_link2 option not specified in config or dblink not found in db
========================================================================= 493 passed, 1 skipped in 123.97s (0:02:03) =========================================================================

Please let me know if I need to add more tests or if I am missing something.

the full test suite isn't set up to work with Oracle because many table fixtures lack a sequence or identity column to create new primary key values.

if you're running the whole suite (which I dont think is needed here) you would run with the --backend-only flag, but here you should probably just target the tests for your JSON columns ( there's really no need to run the wider range of tests, we'll do that up in CI):

pytest test/dialect/ --db oracle
pytest test/dialect/oracle/ --db oracle
pytest test/dialect/test_suite.py --db oracle

etc

While working on this PR, I noticed that the Oracle driver returns JSON columns as dictionaries. Going back to the driver documentation, I didn't find any interface where I can pass my own serializer. Because of that, I have decided to skip the' test_round_trip_custom_json' test if it's Oracle. Please let me know what you think about this.

Another approach would be to serialize and then deserialize or vice versa, but i dont think it's a good idea to do this.

from a quick look it may be achieved using inputtypehandler and outputtypehandler

Agree, the type objects can include outputtypehandlers in the oracle dialect which is generally how we do this.

I will check it out and update my approach, thank you both!

We can ping the oracledb developted if needed to see if there is something a bit more centralized.

I think that if json encoder / decoder are provided we may add functions on the connections to handle the input - output data conversions.

The input/outputtypehandler seem to be present both in the connection and in the curstor, so I would expect that setting on the connection will make is useful from them on:

• https://python-oracledb.readthedocs.io/en/latest/api_manual/connection.html#oracledb.Connection.inputtypehandler
• https://python-oracledb.readthedocs.io/en/latest/api_manual/cursor.html#oracledb.Cursor.inputtypehandler