Repository: tobymao/sqlglot

Issue:
Oracle SQL `cast(X as Y default Z on conversion error)` syntax throws missing parenthesis ParseError
Oracle SQL supports the addition of `default X on conversion error` inside casting functions to provide a default value upon conversion error.
See the docs here:
- `CAST()` [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CAST.html)
- `TO_NUMBER()` [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_NUMBER.html)
- `TO_DATE()` [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_DATE.html)
- etc.


When using the SQLGlot parser, these lines throw `ParseError: Expecting )`

## Reprex 1: `cast()`
```python
reprex1 = """
select
    field1
    , cast(field1 as numeric default 0 on conversion error) as numericfield
from exampletbl
"""

sqlglot.parse_one(reprex1, read='oracle')

```
throws
```
ParseError: Expecting ). Line 4, Col: 33.
  
select
    field1
    , cast(field1 as numeric default 0 on conversion error) as numericfield
from exampletbl
```
## Reprex 2: `to_date()`
```python
reprex2 = """
select
    field2
    ,to_date(field_2 default null on conversion error) as datefield
from exampletbl
"""
sqlglot.parse_one(reprex2, read='oracle')
```
throws
```
ParseError: Expecting ). Line 4, Col: 25.
  
select
    field2
    , to_date(field_2 default null on conversion error) as datefield
from exampletbl
```

Discussion:
@jaredschwartz-ofs: Thanks for the quick response! I'm a little confused by comment on the PR. It says that it only covers the first reprex and to tackle the second one in a follow up if needed. Does that mean make a new issue for the to_date() function? @georgesittas@georgesittas: I meant to tackle only the first example. Feel free to use that as an example in order to tackle the second one as well– it shouldn't be a big lift. It's relatively low priority for the core team so I think we'll leave it out of scope for now.@jaredschwartz-ofs: Ah, got it. Thank you!

Solution patch:
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index f2b6898068..04f74a00df 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5518,6 +5518,7 @@ class Cast(Func):
         "format": False,
         "safe": False,
         "action": False,
+        "default": False,
     }
 
     @property
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index c427581261..65337639bb 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3182,7 +3182,9 @@ def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) ->
         to_sql = f" {to_sql}" if to_sql else ""
         action = self.sql(expression, "action")
         action = f" {action}" if action else ""
-        return f"{safe_prefix or ''}CAST({self.sql(expression, 'this')} AS{to_sql}{format_sql}{action})"
+        default = self.sql(expression, "default")
+        default = f" DEFAULT {default} ON CONVERSION ERROR" if default else ""
+        return f"{safe_prefix or ''}CAST({self.sql(expression, 'this')} AS{to_sql}{default}{format_sql}{action})"
 
     def currentdate_sql(self, expression: exp.CurrentDate) -> str:
         zone = self.sql(expression, "this")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 8632244f8a..295319996a 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -6122,7 +6122,12 @@ def _parse_cast(self, strict: bool, safe: t.Optional[bool] = None) -> exp.Expres
         fmt = None
         to = self._parse_types()
 
-        if self._match(TokenType.FORMAT):
+        default = self._match(TokenType.DEFAULT)
+        if default:
+            default = self._parse_bitwise()
+            self._match_text_seq("ON", "CONVERSION", "ERROR")
+
+        if self._match_set((TokenType.FORMAT, TokenType.COMMA)):
             fmt_string = self._parse_string()
             fmt = self._parse_at_time_zone(fmt_string)
 
@@ -6160,6 +6165,7 @@ def _parse_cast(self, strict: bool, safe: t.Optional[bool] = None) -> exp.Expres
             format=fmt,
             safe=safe,
             action=self._parse_var_from_options(self.CAST_ACTIONS, raise_unmatched=False),
+            default=default,
         )
 
     def _parse_string_agg(self) -> exp.GroupConcat:
@@ -6263,7 +6269,7 @@ def _parse_xml_namespace(self) -> t.List[exp.XMLNamespace]:
         namespaces = []
 
         while True:
-            if self._match_text_seq("DEFAULT"):
+            if self._match(TokenType.DEFAULT):
                 uri = self._parse_string()
             else:
                 uri = self._parse_alias(self._parse_string())


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 982b133235..1f299afd4e 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -16,6 +16,7 @@ def test_oracle(self):
         )
         self.parse_one("ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol").assert_is(exp.Alter)
 
+        self.validate_identity("CAST(value AS NUMBER DEFAULT 0 ON CONVERSION ERROR)")
         self.validate_identity("SYSDATE")
         self.validate_identity("CREATE GLOBAL TEMPORARY TABLE t AS SELECT * FROM orders")
         self.validate_identity("CREATE PRIVATE TEMPORARY TABLE t AS SELECT * FROM orders")
@@ -79,6 +80,10 @@ def test_oracle(self):
         self.validate_identity(
             "SELECT MIN(column_name) KEEP (DENSE_RANK FIRST ORDER BY column_name DESC) FROM table_name"
         )
+        self.validate_identity(
+            "SELECT CAST('January 15, 1989, 11:00 A.M.' AS DATE DEFAULT NULL ON CONVERSION ERROR, 'Month dd, YYYY, HH:MI A.M.') FROM DUAL",
+            "SELECT TO_DATE('January 15, 1989, 11:00 A.M.', 'Month dd, YYYY, HH12:MI P.M.') FROM DUAL",
+        )
         self.validate_identity(
             "SELECT TRUNC(SYSDATE)",
             "SELECT TRUNC(SYSDATE, 'DD')",