diff options
Diffstat (limited to 'tests/dialects/test_oracle.py')
-rw-r--r-- | tests/dialects/test_oracle.py | 63 |
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, + ) |