We are currently solving the following issue within our repository. Here is the issue text:
--- BEGIN ISSUE ---
bug(oracle): Failing metadata query
### What happened?

Metadata query for Oracle is failing due to filtering the nullable column between SELECT and FROM statements, which is only possible after WHERE.

### What version of ibis are you using?

7.0.0

### What backend(s) are you using, if any?

Oracle

### Relevant log output

```sh
sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-00923: FROM keyword not found where expected
[SQL: SELECT all_tab_columns.column_name, all_tab_columns.data_type, all_tab_columns.data_precision, all_tab_columns.data_scale, all_tab_columns.nullable = :nullable_1 AS nullable
FROM all_tab_columns
WHERE all_tab_columns.table_name = :table_name_1]
[parameters: {'nullable_1': 'Y', 'table_name_1': '_ibis_oracle_metadata_7djjvezdl5bnrmqkf6grsevvjq'}]
```
```


### Code of Conduct

- [X] I agree to follow this project's Code of Conduct

--- END ISSUE ---

Below are some code segments, each from a relevant file. One or more of these files may contain bugs.
--- BEGIN FILES ---
Path: `ibis/backends/oracle/__init__.py`
Content:
```
1 """The Oracle backend."""
2 
3 from __future__ import annotations
4 
5 import atexit
6 import contextlib
7 import sys
8 import warnings
9 from typing import TYPE_CHECKING, Any
10 
11 import oracledb
12 import sqlglot as sg
13 
14 from ibis import util
15 
16 # Wow, this is truly horrible
17 # Get out your clippers, it's time to shave a yak.
18 #
19 # 1. snowflake-sqlalchemy doesn't support sqlalchemy 2.0
20 # 2. oracledb is only supported in sqlalchemy 2.0
21 # 3. Ergo, module hacking is required to avoid doing a silly amount of work
22 #    to create multiple lockfiles or port snowflake away from sqlalchemy
23 # 4. Also the version needs to be spoofed to be >= 7 or else the cx_Oracle
24 #    dialect barfs
25 oracledb.__version__ = oracledb.version = "7"
26 
27 sys.modules["cx_Oracle"] = oracledb
28 
29 import sqlalchemy as sa  # noqa: E402
30 
31 import ibis.common.exceptions as exc  # noqa: E402
32 import ibis.expr.datatypes as dt  # noqa: E402
33 import ibis.expr.operations as ops  # noqa: E402
34 import ibis.expr.schema as sch  # noqa: E402
35 from ibis.backends.base.sql.alchemy import (  # noqa: E402
36     AlchemyCompiler,
37     AlchemyExprTranslator,
38     BaseAlchemyBackend,
39 )
40 from ibis.backends.oracle.datatypes import OracleType  # noqa: E402
41 from ibis.backends.oracle.registry import operation_registry  # noqa: E402
42 from ibis.expr.rewrites import rewrite_sample  # noqa: E402
43 
44 if TYPE_CHECKING:
45     from collections.abc import Iterable
46 
47 
48 class OracleExprTranslator(AlchemyExprTranslator):
49     _registry = operation_registry.copy()
50     _rewrites = AlchemyExprTranslator._rewrites.copy()
51     _dialect_name = "oracle"
52     _has_reduction_filter_syntax = False
53     _require_order_by = (
54         *AlchemyExprTranslator._require_order_by,
55         ops.Reduction,
56         ops.Lag,
57         ops.Lead,
58     )
59 
60     _forbids_frame_clause = (
61         *AlchemyExprTranslator._forbids_frame_clause,
62         ops.Lag,
63         ops.Lead,
64     )
65 
66     _quote_column_names = True
67     _quote_table_names = True
68 
69     type_mapper = OracleType
70 
71 
72 class OracleCompiler(AlchemyCompiler):
73     translator_class = OracleExprTranslator
74     support_values_syntax_in_select = False
75     supports_indexed_grouping_keys = False
76     null_limit = None
77     rewrites = AlchemyCompiler.rewrites | rewrite_sample
78 
79 
80 class Backend(BaseAlchemyBackend):
81     name = "oracle"
82     compiler = OracleCompiler
83     supports_create_or_replace = False
84     supports_temporary_tables = True
85     _temporary_prefix = "GLOBAL TEMPORARY"
86 
87     def do_connect(
88         self,
89         *,
90         user: str,
91         password: str,
92         host: str = "localhost",
93         port: int = 1521,
94         database: str | None = None,
95         sid: str | None = None,
96         service_name: str | None = None,
97         dsn: str | None = None,
98         **_: Any,
99     ) -> None:
100         """Create an Ibis client using the passed connection parameters.
101 
102         Parameters
103         ----------
104         user
105             Username
106         password
107             Password
108         host
109             Hostname
110         port
111             Port
112         database
113             Used as an Oracle service name if provided.
114         sid
115             Unique name of an Oracle Instance, used to construct a DSN if
116             provided.
117         service_name
118             Oracle service name, used to construct a DSN if provided.  Only one
119             of database and service_name should be provided.
120         dsn
121             An Oracle Data Source Name.  If provided, overrides all other
122             connection arguments except username and password.
123         """
124         # SID: unique name of an INSTANCE running an oracle process (a single, identifiable machine)
125         # service name: an ALIAS to one (or many) individual instances that can
126         # be hotswapped without the client knowing / caring
127         if dsn is not None and (
128             database is not None or sid is not None or service_name is not None
129         ):
130             warnings.warn(
131                 "Oracle DSN provided, overriding additional provided connection arguments"
132             )
133 
134         if service_name is not None and database is not None:
135             raise exc.IbisInputError(
136                 "Values provided for both service_name and database. "
137                 "Both of these values map to an Oracle service_name, "
138                 "please provide only one of them."
139             )
140 
141         if service_name is None and database is not None:
142             service_name = database
143 
144         if dsn is None:
145             dsn = oracledb.makedsn(host, port, service_name=service_name, sid=sid)
146         url = sa.engine.url.make_url(f"oracle://{user}:{password}@{dsn}")
147 
148         engine = sa.create_engine(
149             url,
150             poolclass=sa.pool.StaticPool,
151             # We set the statement cache size to 0 because Oracle will otherwise
152             # attempt to reuse prepared statements even if the type of the bound variable
153             # has changed.
154             # This is apparently accepted behavior.
155             # https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_b.html#statement-caching-in-thin-and-thick-modes
156             connect_args={"stmtcachesize": 0},
157         )
158 
159         super().do_connect(engine)
160 
161         def normalize_name(name):
162             if name is None:
163                 return None
164             elif not name:
165                 return ""
166             elif name.lower() == name:
167                 return sa.sql.quoted_name(name, quote=True)
168             else:
169                 return name
170 
171         self.con.dialect.normalize_name = normalize_name
172 
173     def _from_url(self, url: str, **kwargs):
174         return self.do_connect(user=url.username, password=url.password, dsn=url.host)
175 
176     @property
177     def current_database(self) -> str:
178         return self._scalar_query("SELECT * FROM global_name")
179 
180     def list_tables(self, like=None, database=None, schema=None):
181         """List the tables in the database.
182 
183         Parameters
184         ----------
185         like
186             A pattern to use for listing tables.
187         database
188             (deprecated) The database to perform the list against.
189         schema
190             The schema to perform the list against.
191 
192             ::: {.callout-warning}
193             ## `schema` refers to database hierarchy
194 
195             The `schema` parameter does **not** refer to the column names and
196             types of `table`.
197             :::
198         """
199         if database is not None:
200             util.warn_deprecated(
201                 "database",
202                 instead="Use the `schema` keyword argument instead",
203                 as_of="7.1",
204                 removed_in="8.0",
205             )
206         schema = schema or database
207         tables = self.inspector.get_table_names(schema=schema)
208         views = self.inspector.get_view_names(schema=schema)
209         return self._filter_with_like(tables + views, like)
210 
211     def _metadata(self, query: str) -> Iterable[tuple[str, dt.DataType]]:
212         from sqlalchemy_views import CreateView, DropView
213 
214         name = util.gen_name("oracle_metadata")
215 
216         try:
217             sg_expr = sg.parse_one(query, into=sg.exp.Table, dialect="oracle")
218         except sg.ParseError:
219             sg_expr = sg.parse_one(query, dialect="oracle")
220 
221         # If query is a table, adjust the query accordingly
222         if isinstance(sg_expr, sg.exp.Table):
223             sg_expr = sg.select("*").from_(sg_expr)
224 
225         query = sg_expr.sql(dialect="oracle")
226 
227         view = sa.table(name)
228         create_view = CreateView(view, sa.text(query))
229         drop_view = DropView(view, if_exists=False)
230 
231         t = sa.table(
232             "all_tab_columns",
233             sa.column("table_name"),
234             sa.column("column_name"),
235             sa.column("data_type"),
236             sa.column("data_precision"),
237             sa.column("data_scale"),
238             sa.column("nullable"),
239         )
240         metadata_query = sa.select(
241             t.c.column_name,
242             t.c.data_type,
243             t.c.data_precision,
244             t.c.data_scale,
245             (t.c.nullable == "Y").label("nullable"),
246         ).where(t.c.table_name == name)
247 
248         with self.begin() as con:
249             con.execute(create_view)
250             try:
251                 results = con.execute(metadata_query).fetchall()
252             finally:
253                 # drop the view no matter what
254                 con.execute(drop_view)
255 
256         for name, type_string, precision, scale, nullable in results:
257             if precision is not None and scale is not None and precision != 0:
258                 typ = dt.Decimal(precision=precision, scale=scale, nullable=nullable)
259             elif precision == 0:
260                 # TODO: how to disambiguate between int and float here without inspecting the value?
261                 typ = dt.float
262             else:
263                 typ = OracleType.from_string(type_string, nullable=nullable)
264             yield name, typ
265 
266     def _table_from_schema(
267         self,
268         name: str,
269         schema: sch.Schema,
270         temp: bool = False,
271         database: str | None = None,
272         **kwargs: Any,
273     ) -> sa.Table:
274         if temp:
275             kwargs["oracle_on_commit"] = "PRESERVE ROWS"
276         t = super()._table_from_schema(name, schema, temp, database, **kwargs)
277         if temp:
278             atexit.register(self._clean_up_tmp_table, t)
279         return t
280 
281     def _clean_up_tmp_table(self, name: str) -> None:
282         tmptable = self._get_sqla_table(name, autoload=False)
283         with self.begin() as bind:
284             # global temporary tables cannot be dropped without first truncating them
285             #
286             # https://stackoverflow.com/questions/32423397/force-oracle-drop-global-temp-table
287             #
288             # ignore DatabaseError exceptions because the table may not exist
289             # because it's already been deleted
290             with contextlib.suppress(sa.exc.DatabaseError):
291                 bind.exec_driver_sql(f'TRUNCATE TABLE "{tmptable.name}"')
292             with contextlib.suppress(sa.exc.DatabaseError):
293                 tmptable.drop(bind=bind)
294 
295     def _clean_up_cached_table(self, op):
296         self._clean_up_tmp_table(op.name)
297
```
--- END FILES ---

Please first localize the bug based on the issue statement, and then generate a patch according to the `git diff` format fenced by three backticks.

Here is an example:

```diff
diff --git a/examples/server_async.py b/examples/server_async.py
--- a/examples/server_async.py
+++ b/examples/server_async.py
@@ -313,4 +313,4 @@
 
 
 if __name__ == "__main__":
-    asyncio.run(run_async_server("."), debug=True)
+    asyncio.run(run_async_server(), debug=True)
diff --git a/examples/server_sync.py b/examples/server_sync.py
--- a/examples/server_sync.py
+++ b/examples/server_sync.py
@@ -313,5 +313,5 @@
 
 
 if __name__ == "__main__":
-    server = run_sync_server(".")
+    server = run_sync_server()
     server.shutdown()

```

diff --git a/ibis/backends/oracle/__init__.py b/ibis/backends/oracle/__init__.py
--- a/ibis/backends/oracle/__init__.py
+++ b/ibis/backends/oracle/__init__.py
@@ -209,6 +209,7 @@
         return self._filter_with_like(tables + views, like)
 
     def _metadata(self, query: str) -> Iterable[tuple[str, dt.DataType]]:
+        from sqlalchemy import case
         from sqlalchemy_views import CreateView, DropView
 
         name = util.gen_name("oracle_metadata")
@@ -242,7 +243,7 @@
             t.c.data_type,
             t.c.data_precision,
             t.c.data_scale,
-            (t.c.nullable == "Y").label("nullable"),
+            case((t.c.nullable == "Y", True), else_=False).label("nullable"),
         ).where(t.c.table_name == name)
 
         with self.begin() as con:

{"golden_diff": "diff --git a/ibis/backends/oracle/__init__.py b/ibis/backends/oracle/__init__.py\n--- a/ibis/backends/oracle/__init__.py\n+++ b/ibis/backends/oracle/__init__.py\n@@ -209,6 +209,7 @@\n         return self._filter_with_like(tables + views, like)\n \n     def _metadata(self, query: str) -> Iterable[tuple[str, dt.DataType]]:\n+        from sqlalchemy import case\n         from sqlalchemy_views import CreateView, DropView\n \n         name = util.gen_name(\"oracle_metadata\")\n@@ -242,7 +243,7 @@\n             t.c.data_type,\n             t.c.data_precision,\n             t.c.data_scale,\n-            (t.c.nullable == \"Y\").label(\"nullable\"),\n+            case((t.c.nullable == \"Y\", True), else_=False).label(\"nullable\"),\n         ).where(t.c.table_name == name)\n \n         with self.begin() as con:\n", "issue": "bug(oracle): Failing metadata query\n### What happened?\n\nMetadata query for Oracle is failing due to filtering the nullable column between SELECT and FROM statements, which is only possible after WHERE.\n\n### What version of ibis are you using?\n\n7.0.0\n\n### What backend(s) are you using, if any?\n\nOracle\n\n### Relevant log output\n\n```sh\nsqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-00923: FROM keyword not found where expected\r\n[SQL: SELECT all_tab_columns.column_name, all_tab_columns.data_type, all_tab_columns.data_precision, all_tab_columns.data_scale, all_tab_columns.nullable = :nullable_1 AS nullable\r\nFROM all_tab_columns\r\nWHERE all_tab_columns.table_name = :table_name_1]\r\n[parameters: {'nullable_1': 'Y', 'table_name_1': '_ibis_oracle_metadata_7djjvezdl5bnrmqkf6grsevvjq'}]\r\n```\n```\n\n\n### Code of Conduct\n\n- [X] I agree to follow this project's Code of Conduct\n", "before_files": [{"content": "\"\"\"The Oracle backend.\"\"\"\n\nfrom __future__ import annotations\n\nimport atexit\nimport contextlib\nimport sys\nimport warnings\nfrom typing import TYPE_CHECKING, Any\n\nimport oracledb\nimport sqlglot as sg\n\nfrom ibis import util\n\n# Wow, this is truly horrible\n# Get out your clippers, it's time to shave a yak.\n#\n# 1. snowflake-sqlalchemy doesn't support sqlalchemy 2.0\n# 2. oracledb is only supported in sqlalchemy 2.0\n# 3. Ergo, module hacking is required to avoid doing a silly amount of work\n#    to create multiple lockfiles or port snowflake away from sqlalchemy\n# 4. Also the version needs to be spoofed to be >= 7 or else the cx_Oracle\n#    dialect barfs\noracledb.__version__ = oracledb.version = \"7\"\n\nsys.modules[\"cx_Oracle\"] = oracledb\n\nimport sqlalchemy as sa  # noqa: E402\n\nimport ibis.common.exceptions as exc  # noqa: E402\nimport ibis.expr.datatypes as dt  # noqa: E402\nimport ibis.expr.operations as ops  # noqa: E402\nimport ibis.expr.schema as sch  # noqa: E402\nfrom ibis.backends.base.sql.alchemy import (  # noqa: E402\n    AlchemyCompiler,\n    AlchemyExprTranslator,\n    BaseAlchemyBackend,\n)\nfrom ibis.backends.oracle.datatypes import OracleType  # noqa: E402\nfrom ibis.backends.oracle.registry import operation_registry  # noqa: E402\nfrom ibis.expr.rewrites import rewrite_sample  # noqa: E402\n\nif TYPE_CHECKING:\n    from collections.abc import Iterable\n\n\nclass OracleExprTranslator(AlchemyExprTranslator):\n    _registry = operation_registry.copy()\n    _rewrites = AlchemyExprTranslator._rewrites.copy()\n    _dialect_name = \"oracle\"\n    _has_reduction_filter_syntax = False\n    _require_order_by = (\n        *AlchemyExprTranslator._require_order_by,\n        ops.Reduction,\n        ops.Lag,\n        ops.Lead,\n    )\n\n    _forbids_frame_clause = (\n        *AlchemyExprTranslator._forbids_frame_clause,\n        ops.Lag,\n        ops.Lead,\n    )\n\n    _quote_column_names = True\n    _quote_table_names = True\n\n    type_mapper = OracleType\n\n\nclass OracleCompiler(AlchemyCompiler):\n    translator_class = OracleExprTranslator\n    support_values_syntax_in_select = False\n    supports_indexed_grouping_keys = False\n    null_limit = None\n    rewrites = AlchemyCompiler.rewrites | rewrite_sample\n\n\nclass Backend(BaseAlchemyBackend):\n    name = \"oracle\"\n    compiler = OracleCompiler\n    supports_create_or_replace = False\n    supports_temporary_tables = True\n    _temporary_prefix = \"GLOBAL TEMPORARY\"\n\n    def do_connect(\n        self,\n        *,\n        user: str,\n        password: str,\n        host: str = \"localhost\",\n        port: int = 1521,\n        database: str | None = None,\n        sid: str | None = None,\n        service_name: str | None = None,\n        dsn: str | None = None,\n        **_: Any,\n    ) -> None:\n        \"\"\"Create an Ibis client using the passed connection parameters.\n\n        Parameters\n        ----------\n        user\n            Username\n        password\n            Password\n        host\n            Hostname\n        port\n            Port\n        database\n            Used as an Oracle service name if provided.\n        sid\n            Unique name of an Oracle Instance, used to construct a DSN if\n            provided.\n        service_name\n            Oracle service name, used to construct a DSN if provided.  Only one\n            of database and service_name should be provided.\n        dsn\n            An Oracle Data Source Name.  If provided, overrides all other\n            connection arguments except username and password.\n        \"\"\"\n        # SID: unique name of an INSTANCE running an oracle process (a single, identifiable machine)\n        # service name: an ALIAS to one (or many) individual instances that can\n        # be hotswapped without the client knowing / caring\n        if dsn is not None and (\n            database is not None or sid is not None or service_name is not None\n        ):\n            warnings.warn(\n                \"Oracle DSN provided, overriding additional provided connection arguments\"\n            )\n\n        if service_name is not None and database is not None:\n            raise exc.IbisInputError(\n                \"Values provided for both service_name and database. \"\n                \"Both of these values map to an Oracle service_name, \"\n                \"please provide only one of them.\"\n            )\n\n        if service_name is None and database is not None:\n            service_name = database\n\n        if dsn is None:\n            dsn = oracledb.makedsn(host, port, service_name=service_name, sid=sid)\n        url = sa.engine.url.make_url(f\"oracle://{user}:{password}@{dsn}\")\n\n        engine = sa.create_engine(\n            url,\n            poolclass=sa.pool.StaticPool,\n            # We set the statement cache size to 0 because Oracle will otherwise\n            # attempt to reuse prepared statements even if the type of the bound variable\n            # has changed.\n            # This is apparently accepted behavior.\n            # https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_b.html#statement-caching-in-thin-and-thick-modes\n            connect_args={\"stmtcachesize\": 0},\n        )\n\n        super().do_connect(engine)\n\n        def normalize_name(name):\n            if name is None:\n                return None\n            elif not name:\n                return \"\"\n            elif name.lower() == name:\n                return sa.sql.quoted_name(name, quote=True)\n            else:\n                return name\n\n        self.con.dialect.normalize_name = normalize_name\n\n    def _from_url(self, url: str, **kwargs):\n        return self.do_connect(user=url.username, password=url.password, dsn=url.host)\n\n    @property\n    def current_database(self) -> str:\n        return self._scalar_query(\"SELECT * FROM global_name\")\n\n    def list_tables(self, like=None, database=None, schema=None):\n        \"\"\"List the tables in the database.\n\n        Parameters\n        ----------\n        like\n            A pattern to use for listing tables.\n        database\n            (deprecated) The database to perform the list against.\n        schema\n            The schema to perform the list against.\n\n            ::: {.callout-warning}\n            ## `schema` refers to database hierarchy\n\n            The `schema` parameter does **not** refer to the column names and\n            types of `table`.\n            :::\n        \"\"\"\n        if database is not None:\n            util.warn_deprecated(\n                \"database\",\n                instead=\"Use the `schema` keyword argument instead\",\n                as_of=\"7.1\",\n                removed_in=\"8.0\",\n            )\n        schema = schema or database\n        tables = self.inspector.get_table_names(schema=schema)\n        views = self.inspector.get_view_names(schema=schema)\n        return self._filter_with_like(tables + views, like)\n\n    def _metadata(self, query: str) -> Iterable[tuple[str, dt.DataType]]:\n        from sqlalchemy_views import CreateView, DropView\n\n        name = util.gen_name(\"oracle_metadata\")\n\n        try:\n            sg_expr = sg.parse_one(query, into=sg.exp.Table, dialect=\"oracle\")\n        except sg.ParseError:\n            sg_expr = sg.parse_one(query, dialect=\"oracle\")\n\n        # If query is a table, adjust the query accordingly\n        if isinstance(sg_expr, sg.exp.Table):\n            sg_expr = sg.select(\"*\").from_(sg_expr)\n\n        query = sg_expr.sql(dialect=\"oracle\")\n\n        view = sa.table(name)\n        create_view = CreateView(view, sa.text(query))\n        drop_view = DropView(view, if_exists=False)\n\n        t = sa.table(\n            \"all_tab_columns\",\n            sa.column(\"table_name\"),\n            sa.column(\"column_name\"),\n            sa.column(\"data_type\"),\n            sa.column(\"data_precision\"),\n            sa.column(\"data_scale\"),\n            sa.column(\"nullable\"),\n        )\n        metadata_query = sa.select(\n            t.c.column_name,\n            t.c.data_type,\n            t.c.data_precision,\n            t.c.data_scale,\n            (t.c.nullable == \"Y\").label(\"nullable\"),\n        ).where(t.c.table_name == name)\n\n        with self.begin() as con:\n            con.execute(create_view)\n            try:\n                results = con.execute(metadata_query).fetchall()\n            finally:\n                # drop the view no matter what\n                con.execute(drop_view)\n\n        for name, type_string, precision, scale, nullable in results:\n            if precision is not None and scale is not None and precision != 0:\n                typ = dt.Decimal(precision=precision, scale=scale, nullable=nullable)\n            elif precision == 0:\n                # TODO: how to disambiguate between int and float here without inspecting the value?\n                typ = dt.float\n            else:\n                typ = OracleType.from_string(type_string, nullable=nullable)\n            yield name, typ\n\n    def _table_from_schema(\n        self,\n        name: str,\n        schema: sch.Schema,\n        temp: bool = False,\n        database: str | None = None,\n        **kwargs: Any,\n    ) -> sa.Table:\n        if temp:\n            kwargs[\"oracle_on_commit\"] = \"PRESERVE ROWS\"\n        t = super()._table_from_schema(name, schema, temp, database, **kwargs)\n        if temp:\n            atexit.register(self._clean_up_tmp_table, t)\n        return t\n\n    def _clean_up_tmp_table(self, name: str) -> None:\n        tmptable = self._get_sqla_table(name, autoload=False)\n        with self.begin() as bind:\n            # global temporary tables cannot be dropped without first truncating them\n            #\n            # https://stackoverflow.com/questions/32423397/force-oracle-drop-global-temp-table\n            #\n            # ignore DatabaseError exceptions because the table may not exist\n            # because it's already been deleted\n            with contextlib.suppress(sa.exc.DatabaseError):\n                bind.exec_driver_sql(f'TRUNCATE TABLE \"{tmptable.name}\"')\n            with contextlib.suppress(sa.exc.DatabaseError):\n                tmptable.drop(bind=bind)\n\n    def _clean_up_cached_table(self, op):\n        self._clean_up_tmp_table(op.name)\n", "path": "ibis/backends/oracle/__init__.py"}], "after_files": [{"content": "\"\"\"The Oracle backend.\"\"\"\n\nfrom __future__ import annotations\n\nimport atexit\nimport contextlib\nimport sys\nimport warnings\nfrom typing import TYPE_CHECKING, Any\n\nimport oracledb\nimport sqlglot as sg\n\nfrom ibis import util\n\n# Wow, this is truly horrible\n# Get out your clippers, it's time to shave a yak.\n#\n# 1. snowflake-sqlalchemy doesn't support sqlalchemy 2.0\n# 2. oracledb is only supported in sqlalchemy 2.0\n# 3. Ergo, module hacking is required to avoid doing a silly amount of work\n#    to create multiple lockfiles or port snowflake away from sqlalchemy\n# 4. Also the version needs to be spoofed to be >= 7 or else the cx_Oracle\n#    dialect barfs\noracledb.__version__ = oracledb.version = \"7\"\n\nsys.modules[\"cx_Oracle\"] = oracledb\n\nimport sqlalchemy as sa  # noqa: E402\n\nimport ibis.common.exceptions as exc  # noqa: E402\nimport ibis.expr.datatypes as dt  # noqa: E402\nimport ibis.expr.operations as ops  # noqa: E402\nimport ibis.expr.schema as sch  # noqa: E402\nfrom ibis.backends.base.sql.alchemy import (  # noqa: E402\n    AlchemyCompiler,\n    AlchemyExprTranslator,\n    BaseAlchemyBackend,\n)\nfrom ibis.backends.oracle.datatypes import OracleType  # noqa: E402\nfrom ibis.backends.oracle.registry import operation_registry  # noqa: E402\nfrom ibis.expr.rewrites import rewrite_sample  # noqa: E402\n\nif TYPE_CHECKING:\n    from collections.abc import Iterable\n\n\nclass OracleExprTranslator(AlchemyExprTranslator):\n    _registry = operation_registry.copy()\n    _rewrites = AlchemyExprTranslator._rewrites.copy()\n    _dialect_name = \"oracle\"\n    _has_reduction_filter_syntax = False\n    _require_order_by = (\n        *AlchemyExprTranslator._require_order_by,\n        ops.Reduction,\n        ops.Lag,\n        ops.Lead,\n    )\n\n    _forbids_frame_clause = (\n        *AlchemyExprTranslator._forbids_frame_clause,\n        ops.Lag,\n        ops.Lead,\n    )\n\n    _quote_column_names = True\n    _quote_table_names = True\n\n    type_mapper = OracleType\n\n\nclass OracleCompiler(AlchemyCompiler):\n    translator_class = OracleExprTranslator\n    support_values_syntax_in_select = False\n    supports_indexed_grouping_keys = False\n    null_limit = None\n    rewrites = AlchemyCompiler.rewrites | rewrite_sample\n\n\nclass Backend(BaseAlchemyBackend):\n    name = \"oracle\"\n    compiler = OracleCompiler\n    supports_create_or_replace = False\n    supports_temporary_tables = True\n    _temporary_prefix = \"GLOBAL TEMPORARY\"\n\n    def do_connect(\n        self,\n        *,\n        user: str,\n        password: str,\n        host: str = \"localhost\",\n        port: int = 1521,\n        database: str | None = None,\n        sid: str | None = None,\n        service_name: str | None = None,\n        dsn: str | None = None,\n        **_: Any,\n    ) -> None:\n        \"\"\"Create an Ibis client using the passed connection parameters.\n\n        Parameters\n        ----------\n        user\n            Username\n        password\n            Password\n        host\n            Hostname\n        port\n            Port\n        database\n            Used as an Oracle service name if provided.\n        sid\n            Unique name of an Oracle Instance, used to construct a DSN if\n            provided.\n        service_name\n            Oracle service name, used to construct a DSN if provided.  Only one\n            of database and service_name should be provided.\n        dsn\n            An Oracle Data Source Name.  If provided, overrides all other\n            connection arguments except username and password.\n        \"\"\"\n        # SID: unique name of an INSTANCE running an oracle process (a single, identifiable machine)\n        # service name: an ALIAS to one (or many) individual instances that can\n        # be hotswapped without the client knowing / caring\n        if dsn is not None and (\n            database is not None or sid is not None or service_name is not None\n        ):\n            warnings.warn(\n                \"Oracle DSN provided, overriding additional provided connection arguments\"\n            )\n\n        if service_name is not None and database is not None:\n            raise exc.IbisInputError(\n                \"Values provided for both service_name and database. \"\n                \"Both of these values map to an Oracle service_name, \"\n                \"please provide only one of them.\"\n            )\n\n        if service_name is None and database is not None:\n            service_name = database\n\n        if dsn is None:\n            dsn = oracledb.makedsn(host, port, service_name=service_name, sid=sid)\n        url = sa.engine.url.make_url(f\"oracle://{user}:{password}@{dsn}\")\n\n        engine = sa.create_engine(\n            url,\n            poolclass=sa.pool.StaticPool,\n            # We set the statement cache size to 0 because Oracle will otherwise\n            # attempt to reuse prepared statements even if the type of the bound variable\n            # has changed.\n            # This is apparently accepted behavior.\n            # https://python-oracledb.readthedocs.io/en/latest/user_guide/appendix_b.html#statement-caching-in-thin-and-thick-modes\n            connect_args={\"stmtcachesize\": 0},\n        )\n\n        super().do_connect(engine)\n\n        def normalize_name(name):\n            if name is None:\n                return None\n            elif not name:\n                return \"\"\n            elif name.lower() == name:\n                return sa.sql.quoted_name(name, quote=True)\n            else:\n                return name\n\n        self.con.dialect.normalize_name = normalize_name\n\n    def _from_url(self, url: str, **kwargs):\n        return self.do_connect(user=url.username, password=url.password, dsn=url.host)\n\n    @property\n    def current_database(self) -> str:\n        return self._scalar_query(\"SELECT * FROM global_name\")\n\n    def list_tables(self, like=None, database=None, schema=None):\n        \"\"\"List the tables in the database.\n\n        Parameters\n        ----------\n        like\n            A pattern to use for listing tables.\n        database\n            (deprecated) The database to perform the list against.\n        schema\n            The schema to perform the list against.\n\n            ::: {.callout-warning}\n            ## `schema` refers to database hierarchy\n\n            The `schema` parameter does **not** refer to the column names and\n            types of `table`.\n            :::\n        \"\"\"\n        if database is not None:\n            util.warn_deprecated(\n                \"database\",\n                instead=\"Use the `schema` keyword argument instead\",\n                as_of=\"7.1\",\n                removed_in=\"8.0\",\n            )\n        schema = schema or database\n        tables = self.inspector.get_table_names(schema=schema)\n        views = self.inspector.get_view_names(schema=schema)\n        return self._filter_with_like(tables + views, like)\n\n    def _metadata(self, query: str) -> Iterable[tuple[str, dt.DataType]]:\n        from sqlalchemy import case\n        from sqlalchemy_views import CreateView, DropView\n\n        name = util.gen_name(\"oracle_metadata\")\n\n        try:\n            sg_expr = sg.parse_one(query, into=sg.exp.Table, dialect=\"oracle\")\n        except sg.ParseError:\n            sg_expr = sg.parse_one(query, dialect=\"oracle\")\n\n        # If query is a table, adjust the query accordingly\n        if isinstance(sg_expr, sg.exp.Table):\n            sg_expr = sg.select(\"*\").from_(sg_expr)\n\n        query = sg_expr.sql(dialect=\"oracle\")\n\n        view = sa.table(name)\n        create_view = CreateView(view, sa.text(query))\n        drop_view = DropView(view, if_exists=False)\n\n        t = sa.table(\n            \"all_tab_columns\",\n            sa.column(\"table_name\"),\n            sa.column(\"column_name\"),\n            sa.column(\"data_type\"),\n            sa.column(\"data_precision\"),\n            sa.column(\"data_scale\"),\n            sa.column(\"nullable\"),\n        )\n        metadata_query = sa.select(\n            t.c.column_name,\n            t.c.data_type,\n            t.c.data_precision,\n            t.c.data_scale,\n            case((t.c.nullable == \"Y\", True), else_=False).label(\"nullable\"),\n        ).where(t.c.table_name == name)\n\n        with self.begin() as con:\n            con.execute(create_view)\n            try:\n                results = con.execute(metadata_query).fetchall()\n            finally:\n                # drop the view no matter what\n                con.execute(drop_view)\n\n        for name, type_string, precision, scale, nullable in results:\n            if precision is not None and scale is not None and precision != 0:\n                typ = dt.Decimal(precision=precision, scale=scale, nullable=nullable)\n            elif precision == 0:\n                # TODO: how to disambiguate between int and float here without inspecting the value?\n                typ = dt.float\n            else:\n                typ = OracleType.from_string(type_string, nullable=nullable)\n            yield name, typ\n\n    def _table_from_schema(\n        self,\n        name: str,\n        schema: sch.Schema,\n        temp: bool = False,\n        database: str | None = None,\n        **kwargs: Any,\n    ) -> sa.Table:\n        if temp:\n            kwargs[\"oracle_on_commit\"] = \"PRESERVE ROWS\"\n        t = super()._table_from_schema(name, schema, temp, database, **kwargs)\n        if temp:\n            atexit.register(self._clean_up_tmp_table, t)\n        return t\n\n    def _clean_up_tmp_table(self, name: str) -> None:\n        tmptable = self._get_sqla_table(name, autoload=False)\n        with self.begin() as bind:\n            # global temporary tables cannot be dropped without first truncating them\n            #\n            # https://stackoverflow.com/questions/32423397/force-oracle-drop-global-temp-table\n            #\n            # ignore DatabaseError exceptions because the table may not exist\n            # because it's already been deleted\n            with contextlib.suppress(sa.exc.DatabaseError):\n                bind.exec_driver_sql(f'TRUNCATE TABLE \"{tmptable.name}\"')\n            with contextlib.suppress(sa.exc.DatabaseError):\n                tmptable.drop(bind=bind)\n\n    def _clean_up_cached_table(self, op):\n        self._clean_up_tmp_table(op.name)\n", "path": "ibis/backends/oracle/__init__.py"}]}