summaryrefslogtreecommitdiffstats
path: root/tests/dialects/test_oracle.py
diff options
context:
space:
mode:
Diffstat (limited to 'tests/dialects/test_oracle.py')
-rw-r--r--tests/dialects/test_oracle.py63
1 files changed, 63 insertions, 0 deletions
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,
+ )