diff options
Diffstat (limited to 'tests/dialects/test_oracle.py')
| -rw-r--r-- | tests/dialects/test_oracle.py | 28 |
1 files changed, 28 insertions, 0 deletions
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py index 01a9ca3..2dfd179 100644 --- a/tests/dialects/test_oracle.py +++ b/tests/dialects/test_oracle.py @@ -6,6 +6,7 @@ class TestOracle(Validator): dialect = "oracle" def test_oracle(self): + self.validate_identity("SELECT JSON_OBJECT(k1: v1 FORMAT JSON, k2: v2 FORMAT JSON)") self.validate_identity("SELECT JSON_OBJECT('name': first_name || ' ' || last_name) FROM t") self.validate_identity("COALESCE(c1, c2, c3)") self.validate_identity("SELECT * FROM TABLE(foo)") @@ -26,6 +27,15 @@ class TestOracle(Validator): self.validate_identity("SELECT * FROM table_name SAMPLE (25) s") self.validate_identity("SELECT * FROM V$SESSION") self.validate_identity( + "SELECT JSON_ARRAYAGG(JSON_OBJECT('RNK': RNK, 'RATING_CODE': RATING_CODE, 'DATE_VALUE': DATE_VALUE, 'AGENT_ID': AGENT_ID RETURNING CLOB) RETURNING CLOB) AS JSON_DATA FROM tablename" + ) + self.validate_identity( + "SELECT JSON_ARRAY(FOO() FORMAT JSON, BAR() NULL ON NULL RETURNING CLOB STRICT)" + ) + self.validate_identity( + "SELECT JSON_ARRAYAGG(FOO() FORMAT JSON ORDER BY bar NULL ON NULL RETURNING CLOB STRICT)" + ) + self.validate_identity( "SELECT COUNT(1) INTO V_Temp FROM TABLE(CAST(somelist AS data_list)) WHERE col LIKE '%contact'" ) self.validate_identity( @@ -190,3 +200,21 @@ MATCH_RECOGNIZE ( ) MR""", pretty=True, ) + + def test_json_table(self): + self.validate_identity( + "SELECT * FROM JSON_TABLE(foo FORMAT JSON, 'bla' ERROR ON ERROR NULL ON EMPTY COLUMNS (foo PATH 'bar'))" + ) + self.validate_identity( + "SELECT * FROM JSON_TABLE(foo FORMAT JSON, 'bla' ERROR ON ERROR NULL ON EMPTY COLUMNS foo PATH 'bar')", + "SELECT * FROM JSON_TABLE(foo FORMAT JSON, 'bla' ERROR ON ERROR NULL ON EMPTY COLUMNS (foo PATH 'bar'))", + ) + self.validate_identity( + """SELECT + CASE WHEN DBMS_LOB.GETLENGTH(info) < 32000 THEN DBMS_LOB.SUBSTR(info) END AS info_txt, + info AS info_clob +FROM schemaname.tablename ar +INNER JOIN JSON_TABLE(:emps, '$[*]' COLUMNS (empno NUMBER PATH '$')) jt + ON ar.empno = jt.empno""", + pretty=True, + ) |