From bdf5cc7bdd5ec93dc928d81e286f7b1e678ba19d Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Wed, 13 Sep 2023 11:17:40 +0200 Subject: Merging upstream version 18.4.1. Signed-off-by: Daniel Baumann --- tests/dialects/test_oracle.py | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) (limited to 'tests/dialects/test_oracle.py') 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)") @@ -25,6 +26,15 @@ class TestOracle(Validator): self.validate_identity("SELECT * FROM table_name@dblink_name.database_link_domain") 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'" ) @@ -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, + ) -- cgit v1.2.3