Repository: tobymao/sqlglot

Issue:
Transpile Oracle nvl function to ClickHouse ifNull
**Fully reproducible code snippet**
```
from sqlglot.dialects import Oracle, ClickHouse
sqlglot.transpile("select nvl(null, 1)", read=Oracle, write=ClickHouse, comments=False)
['SELECT nvl(NULL, 1)']
```
expected result `SELECT ifNull(NULL, 1)`

Discussion:

Patch:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index ff5dcb64..53fad5ae 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -107,8 +107,10 @@ class Oracle(Dialect):
             "TO_CHAR": _build_timetostr_or_tochar,
             "TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "oracle"),
             "TO_DATE": build_formatted_time(exp.StrToDate, "oracle"),
+            "NVL": lambda args: exp.Coalesce(
+                this=seq_get(args, 0), expressions=args[1:], is_nvl=True
+            ),
         }
-        FUNCTIONS.pop("NVL")
 
         NO_PAREN_FUNCTION_PARSERS = {
             **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
@@ -313,3 +315,7 @@ class Oracle(Dialect):
             value = f" CONSTRAINT {value}" if value else ""
 
             return f"{option}{value}"
+
+        def coalesce_sql(self, expression: exp.Coalesce) -> str:
+            func_name = "NVL" if expression.args.get("is_nvl") else "COALESCE"
+            return rename_func(func_name)(self, expression)
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 00a54a04..faf85b0c 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5087,7 +5087,7 @@ class Ceil(Func):
 
 
 class Coalesce(Func):
-    arg_types = {"this": True, "expressions": False}
+    arg_types = {"this": True, "expressions": False, "is_nvl": False}
     is_var_len_args = True
     _sql_names = ["COALESCE", "IFNULL", "NVL"]
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 9a099104..cdc631ee 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1302,7 +1302,7 @@ class Generator(metaclass=_Generator):
     def fetch_sql(self, expression: exp.Fetch) -> str:
         direction = expression.args.get("direction")
         direction = f" {direction}" if direction else ""
-        count = expression.args.get("count")
+        count = self.sql(expression, "count")
         count = f" {count}" if count else ""
         if expression.args.get("percent"):
             count = f"{count} PERCENT"
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index d20c2406..55552840 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -222,6 +222,7 @@ class Parser(metaclass=_Parser):
         "UNNEST": lambda args: exp.Unnest(expressions=ensure_list(seq_get(args, 0))),
         "UPPER": build_upper,
         "VAR_MAP": build_var_map,
+        "COALESCE": lambda args: exp.Coalesce(this=seq_get(args, 0), expressions=args[1:]),
     }
 
     NO_PAREN_FUNCTIONS = {


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 594aee5c..4813a495 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -89,7 +89,6 @@ class TestOracle(Validator):
         self.validate_identity(
             "SELECT * FROM T ORDER BY I OFFSET NVL(:variable1, 10) ROWS FETCH NEXT NVL(:variable2, 10) ROWS ONLY",
         )
-        self.validate_identity("NVL(x, y)").assert_is(exp.Anonymous)
         self.validate_identity(
             "SELECT * FROM t SAMPLE (.25)",
             "SELECT * FROM t SAMPLE (0.25)",
@@ -253,6 +252,15 @@ class TestOracle(Validator):
             """SELECT * FROM t ORDER BY a ASC, b ASC NULLS FIRST, c DESC NULLS LAST, d DESC""",
         )
 
+        self.validate_all(
+            "NVL(NULL, 1)",
+            write={
+                "oracle": "NVL(NULL, 1)",
+                "": "COALESCE(NULL, 1)",
+                "clickhouse": "COALESCE(NULL, 1)",
+            },
+        )
+
     def test_join_marker(self):
         self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y")