use simple decimal query to detect decimal char

Fixed issue where the ``nls_session_parameters`` view queried on first
connect in order to get the default decimal point character may not be
available depending on Oracle connection modes, and would therefore raise
an error.  The approach to detecting decimal char has been simplified to
test a decimal value directly, instead of reading system views, which
works on any backend / driver.

Fixes: #8744
Change-Id: I39825131c13513798863197d0c180dd5a18b32dc

3 files changed, 95 insertions, 58 deletions

diff --git a/doc/build/changelog/unreleased_14/8744.rst b/doc/build/changelog/unreleased_14/8744.rst
new file mode 100644
index 000000000..6d24f0ff9
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/8744.rst
@@ -0,0 +1,11 @@
+.. change::
+    :tags: bug, oracle
+    :tickets: 8744
+
+    Fixed issue where the ``nls_session_parameters`` view queried on first
+    connect in order to get the default decimal point character may not be
+    available depending on Oracle connection modes, and would therefore raise
+    an error.  The approach to detecting decimal char has been simplified to
+    test a decimal value directly, instead of reading system views, which
+    works on any backend / driver.
+
diff --git a/lib/sqlalchemy/dialects/oracle/cx_oracle.py b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
index 24262c181..5a0c0e160 100644
--- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py
+++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py
@@ -1133,10 +1133,33 @@ class OracleDialect_cx_oracle(OracleDialect):
         # NLS_TERRITORY or formatting behavior of the DB, we opt
         # to just look at it
 
-        self._decimal_char = connection.exec_driver_sql(
-            "select value from nls_session_parameters "
-            "where parameter = 'NLS_NUMERIC_CHARACTERS'"
-        ).scalar()[0]
+        dbapi_connection = connection.connection
+
+        with dbapi_connection.cursor() as cursor:
+            # issue #8744
+            # nls_session_parameters is not available in some Oracle
+            # modes like "mount mode".  But then, v$nls_parameters is not
+            # available if the connection doesn't have SYSDBA priv.
+            #
+            # simplify the whole thing and just use the method that we were
+            # doing in the test suite already, selecting a number
+
+            def output_type_handler(
+                cursor, name, defaultType, size, precision, scale
+            ):
+                return cursor.var(
+                    self.dbapi.STRING, 255, arraysize=cursor.arraysize
+                )
+
+            cursor.outputtypehandler = output_type_handler
+            cursor.execute("SELECT 1.1 FROM DUAL")
+            value = cursor.fetchone()[0]
+
+            decimal_char = value.lstrip("0")[1]
+            assert not decimal_char[0].isdigit()
+
+        self._decimal_char = decimal_char
+
         if self._decimal_char != ".":
             _detect_decimal = self._detect_decimal
             _to_decimal = self._to_decimal
diff --git a/test/dialect/oracle/test_types.py b/test/dialect/oracle/test_types.py
index 76fc7d542..8c78fe85b 100644
--- a/test/dialect/oracle/test_types.py
+++ b/test/dialect/oracle/test_types.py
@@ -1115,64 +1115,67 @@ class EuroNumericTest(fixtures.TestBase):
     def teardown_test(self):
         self.engine.dispose()
 
-    # https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_b.html#globalization-in-thin-and-thick-modes
-    @testing.requires.fail_on_oracledb_thin
-    def test_were_getting_a_comma(self):
-        connection = self.engine.pool._creator()
-        cursor = connection.cursor()
-        try:
-            cx_Oracle = self.engine.dialect.dbapi
+    def test_detection(self):
+        # revised as of #8744
+        with self.engine.connect() as conn:
+            connection = conn.connection
 
-            def output_type_handler(
-                cursor, name, defaultType, size, precision, scale
-            ):
-                return cursor.var(
-                    cx_Oracle.STRING, 255, arraysize=cursor.arraysize
-                )
+            with connection.cursor() as cursor:
+                cx_Oracle = self.engine.dialect.dbapi
 
-            cursor.outputtypehandler = output_type_handler
-            cursor.execute("SELECT 1.1 FROM DUAL")
-            row = cursor.fetchone()
-            eq_(row[0], "1,1")
-        finally:
-            cursor.close()
-            connection.close()
+                def output_type_handler(
+                    cursor, name, defaultType, size, precision, scale
+                ):
+                    return cursor.var(
+                        cx_Oracle.STRING, 255, arraysize=cursor.arraysize
+                    )
 
-    def test_output_type_handler(self):
-        with self.engine.connect() as conn:
-            for stmt, exp, kw in [
-                ("SELECT 0.1 FROM DUAL", decimal.Decimal("0.1"), {}),
-                ("SELECT CAST(15 AS INTEGER) FROM DUAL", 15, {}),
-                (
-                    "SELECT CAST(15 AS NUMERIC(3, 1)) FROM DUAL",
-                    decimal.Decimal("15"),
-                    {},
-                ),
-                (
-                    "SELECT CAST(0.1 AS NUMERIC(5, 2)) FROM DUAL",
-                    decimal.Decimal("0.1"),
-                    {},
-                ),
-                (
-                    "SELECT :num FROM DUAL",
-                    decimal.Decimal("2.5"),
-                    {"num": decimal.Decimal("2.5")},
-                ),
-                (
-                    text(
-                        "SELECT CAST(28.532 AS NUMERIC(5, 3)) "
-                        "AS val FROM DUAL"
-                    ).columns(val=Numeric(5, 3, asdecimal=True)),
-                    decimal.Decimal("28.532"),
-                    {},
-                ),
-            ]:
-                if isinstance(stmt, str):
-                    test_exp = conn.exec_driver_sql(stmt, kw).scalar()
+                cursor.outputtypehandler = output_type_handler
+                cursor.execute("SELECT 1.1 FROM DUAL")
+                row = cursor.fetchone()
+                decimal_char = row[0][1]
+
+                if testing.against("+cx_oracle"):
+                    eq_(decimal_char, ",")
                 else:
-                    test_exp = conn.scalar(stmt, **kw)
-                eq_(test_exp, exp)
-                assert type(test_exp) is type(exp)
+                    assert decimal_char in ",."
+
+                eq_(conn.dialect._decimal_char, decimal_char)
+
+    @testing.combinations(
+        ("SELECT 0.1 FROM DUAL", decimal.Decimal("0.1"), {}),
+        ("SELECT CAST(15 AS INTEGER) FROM DUAL", 15, {}),
+        (
+            "SELECT CAST(15 AS NUMERIC(3, 1)) FROM DUAL",
+            decimal.Decimal("15"),
+            {},
+        ),
+        (
+            "SELECT CAST(0.1 AS NUMERIC(5, 2)) FROM DUAL",
+            decimal.Decimal("0.1"),
+            {},
+        ),
+        (
+            "SELECT :num FROM DUAL",
+            decimal.Decimal("2.5"),
+            {"num": decimal.Decimal("2.5")},
+        ),
+        (
+            text(
+                "SELECT CAST(28.532 AS NUMERIC(5, 3)) " "AS val FROM DUAL"
+            ).columns(val=Numeric(5, 3, asdecimal=True)),
+            decimal.Decimal("28.532"),
+            {},
+        ),
+    )
+    def test_output_type_handler(self, stmt, expected, kw):
+        with self.engine.connect() as conn:
+            if isinstance(stmt, str):
+                test_exp = conn.exec_driver_sql(stmt, kw).scalar()
+            else:
+                test_exp = conn.scalar(stmt, **kw)
+            eq_(test_exp, expected)
+            assert type(test_exp) is type(expected)
 
 
 class SetInputSizesTest(fixtures.TestBase):