Repository: tobymao/sqlglot

Issue:
[Bug] Transpiling built-in functions is not accurate.
**Before you file an issue**
- updated to 19.0.3

**Fully reproducible code snippet**
I am transpiling Oracle SQL to Postgres. There are multiple functions that fail when executed.
Here is an example.
`sql_in_oracle = "to_char(123)"`
`sqlglot.transpile(sql_in_oracle, read='oracle', write='postgres')`

Output: `['to_char(123)']`
Expected: `['123::text']` or whatever is valid in Postgres
Postgres supports `to_char(expr, format)`, but not `to_char(expr)`.

I have many functions that cannot be tranpiled correctly in my query list, such as,

Truncate datetime to day:
TRUNC(datetime) -- Valid Oracle
Output: TRUNC(datetime) -- Invalid Postgres
Expected: DATE_TRUNC('day', datetime) -- Valid postgres



**Official Documentation**
[sqlines](https://sqlines.com/oracle-to-postgresql) has an online tool I tried once. On its website, it lists many migration conversions for different databases. It might be a helpful resource for you to check out. Thanks.

Discussion:
GeorgeSittas: This doesn't contain the fix to `TRUNC`, can add it in a bit as well if it's ok for the scope of this PR.
tobymao: yea sure just add it

Patch:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 0505335b..0f27c4b2 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -3,7 +3,13 @@ from __future__ import annotations
 import typing as t
 
 from sqlglot import exp, generator, parser, tokens, transforms
-from sqlglot.dialects.dialect import Dialect, no_ilike_sql, rename_func, trim_sql
+from sqlglot.dialects.dialect import (
+    Dialect,
+    format_time_lambda,
+    no_ilike_sql,
+    rename_func,
+    trim_sql,
+)
 from sqlglot.helper import seq_get
 from sqlglot.tokens import TokenType
 
@@ -70,6 +76,7 @@ class Oracle(Dialect):
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
             "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
+            "TO_CHAR": format_time_lambda(exp.TimeToStr, "oracle", default=True),
         }
 
         FUNCTION_PARSERS: t.Dict[str, t.Callable] = {


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 0aa4311a..6b181153 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -62,6 +62,13 @@ class TestOracle(Validator):
             "SELECT * FROM t SAMPLE (0.25)",
         )
 
+        self.validate_all(
+            "SELECT TO_CHAR(TIMESTAMP '1999-12-01 10:00:00')",
+            write={
+                "oracle": "SELECT TO_CHAR(CAST('1999-12-01 10:00:00' AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS')",
+                "postgres": "SELECT TO_CHAR(CAST('1999-12-01 10:00:00' AS TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS')",
+            },
+        )
         self.validate_all(
             "SELECT CAST(NULL AS VARCHAR2(2328 CHAR)) AS COL1",
             write={