diff options
Diffstat (limited to 'tests/dialects/test_oracle.py')
| -rw-r--r-- | tests/dialects/test_oracle.py | 46 |
1 files changed, 39 insertions, 7 deletions
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py index e9ebac1..fce714e 100644 --- a/tests/dialects/test_oracle.py +++ b/tests/dialects/test_oracle.py @@ -7,14 +7,13 @@ class TestOracle(Validator): dialect = "oracle" def test_oracle(self): - self.validate_identity("ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol") - self.assertIsInstance( - parse_one("ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol", dialect="oracle"), - exp.AlterTable, - ) - self.validate_identity( - "ALTER TABLE Payments ADD (Stock NUMBER NOT NULL, dropid VARCHAR2(500) NOT NULL)" + self.validate_identity("REGEXP_REPLACE('source', 'search')") + parse_one("ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol", dialect="oracle").assert_is( + exp.AlterTable ) + + self.validate_identity("CURRENT_TIMESTAMP(precision)") + self.validate_identity("ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol") self.validate_identity("ALTER TABLE Payments ADD Stock NUMBER NOT NULL") self.validate_identity("SELECT x FROM t WHERE cond FOR UPDATE") self.validate_identity("SELECT JSON_OBJECT(k1: v1 FORMAT JSON, k2: v2 FORMAT JSON)") @@ -34,8 +33,15 @@ class TestOracle(Validator): self.validate_identity("SELECT STANDARD_HASH('hello', 'MD5')") 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 COUNT(*) * 10 FROM orders SAMPLE (10) SEED (1)") self.validate_identity("SELECT * FROM V$SESSION") self.validate_identity( + "SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name" + ) + self.validate_identity( + "ALTER TABLE Payments ADD (Stock NUMBER NOT NULL, dropid VARCHAR2(500) NOT NULL)" + ) + 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( @@ -51,6 +57,18 @@ class TestOracle(Validator): "SELECT MIN(column_name) KEEP (DENSE_RANK FIRST ORDER BY column_name DESC) FROM table_name" ) self.validate_identity( + """SELECT JSON_OBJECT(KEY 'key1' IS emp.column1, KEY 'key2' IS emp.column1) "emp_key" FROM emp""", + """SELECT JSON_OBJECT('key1': emp.column1, 'key2': emp.column1) AS "emp_key" FROM emp""", + ) + self.validate_identity( + "SELECT JSON_OBJECTAGG(KEY department_name VALUE department_id) FROM dep WHERE id <= 30", + "SELECT JSON_OBJECTAGG(department_name: department_id) FROM dep WHERE id <= 30", + ) + self.validate_identity( + "SYSDATE", + "CURRENT_TIMESTAMP", + ) + self.validate_identity( "SELECT last_name, department_id, salary, MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) " 'OVER (PARTITION BY department_id) AS "Worst", MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) ' 'OVER (PARTITION BY department_id) AS "Best" FROM employees ORDER BY department_id, salary, last_name' @@ -131,6 +149,20 @@ class TestOracle(Validator): "postgres": "CAST(x AS sch.udt)", }, ) + self.validate_all( + "SELECT TO_TIMESTAMP('2024-12-12 12:12:12.000000', 'YYYY-MM-DD HH24:MI:SS.FF6')", + write={ + "oracle": "SELECT TO_TIMESTAMP('2024-12-12 12:12:12.000000', 'YYYY-MM-DD HH24:MI:SS.FF6')", + "duckdb": "SELECT STRPTIME('2024-12-12 12:12:12.000000', '%Y-%m-%d %H:%M:%S.%f')", + }, + ) + self.validate_all( + "SELECT TO_DATE('2024-12-12', 'YYYY-MM-DD')", + write={ + "oracle": "SELECT TO_DATE('2024-12-12', 'YYYY-MM-DD')", + "duckdb": "SELECT CAST(STRPTIME('2024-12-12', '%Y-%m-%d') AS DATE)", + }, + ) def test_join_marker(self): self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y") |