From ef2db38de92f2329c1c366318bddfc7e3dee8415 Mon Sep 17 00:00:00 2001
From: Daniel Baumann <daniel.baumann@progress-linux.org>
Date: Sun, 19 Feb 2023 14:44:59 +0100
Subject: Adding upstream version 11.1.3.

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
---
 tests/dialects/test_oracle.py | 63 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 63 insertions(+)

(limited to 'tests/dialects/test_oracle.py')

diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 1fadb84..f85a117 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -2,5 +2,68 @@ from tests.dialects.test_dialect import Validator
 
 
 class TestOracle(Validator):
+    dialect = "oracle"
+
     def test_oracle(self):
         self.validate_identity("SELECT * FROM V$SESSION")
+
+    def test_xml_table(self):
+        self.validate_identity("XMLTABLE('x')")
+        self.validate_identity("XMLTABLE('x' RETURNING SEQUENCE BY REF)")
+        self.validate_identity("XMLTABLE('x' PASSING y)")
+        self.validate_identity("XMLTABLE('x' PASSING y RETURNING SEQUENCE BY REF)")
+        self.validate_identity(
+            "XMLTABLE('x' RETURNING SEQUENCE BY REF COLUMNS a VARCHAR2, b FLOAT)"
+        )
+
+        self.validate_all(
+            """SELECT warehouse_name warehouse,
+   warehouse2."Water", warehouse2."Rail"
+   FROM warehouses,
+   XMLTABLE('/Warehouse'
+      PASSING warehouses.warehouse_spec
+      COLUMNS
+         "Water" varchar2(6) PATH 'WaterAccess',
+         "Rail" varchar2(6) PATH 'RailAccess')
+      warehouse2""",
+            write={
+                "oracle": """SELECT
+  warehouse_name AS warehouse,
+  warehouse2."Water",
+  warehouse2."Rail"
+FROM warehouses, XMLTABLE(
+  '/Warehouse'
+  PASSING
+    warehouses.warehouse_spec
+  COLUMNS
+    "Water" VARCHAR2(6) PATH 'WaterAccess',
+    "Rail" VARCHAR2(6) PATH 'RailAccess'
+) warehouse2""",
+            },
+            pretty=True,
+        )
+
+        self.validate_all(
+            """SELECT table_name, column_name, data_default FROM xmltable('ROWSET/ROW'
+    passing dbms_xmlgen.getxmltype('SELECT table_name, column_name, data_default FROM user_tab_columns')
+    columns table_name      VARCHAR2(128)   PATH '*[1]'
+            , column_name   VARCHAR2(128)   PATH '*[2]'
+            , data_default  VARCHAR2(2000)  PATH '*[3]'
+            );""",
+            write={
+                "oracle": """SELECT
+  table_name,
+  column_name,
+  data_default
+FROM XMLTABLE(
+  'ROWSET/ROW'
+  PASSING
+    dbms_xmlgen.getxmltype ("SELECT table_name, column_name, data_default FROM user_tab_columns")
+  COLUMNS
+    table_name VARCHAR2(128) PATH '*[1]',
+    column_name VARCHAR2(128) PATH '*[2]',
+    data_default VARCHAR2(2000) PATH '*[3]'
+)""",
+            },
+            pretty=True,
+        )
-- 
cgit v1.2.3