Repository: tobymao/sqlglot

Issue:
oracle unicode literals parse error 
Parsing some oracle string literal in sqlglot is not working, and returns `sqlglot.errors.ParseError: Invalid expression / Unexpected token.`

A simple example using U"str", or N"str": 

```
import sqlglot

res = sqlglot.transpile('''
SELECT u'\\\20ac' FROM dual;
''', read="oracle", write="oracle")[0]

print(res)
```

The following is valid oracle query: 

```
select n'\€', u'\\\20ac', n'\\\20ac' from dual;

N'\€' U'\\\20AC' N'\\\20AC'
----- ---------- ----------
\€    \€         \\\20ac
```


in sqlglot returns error in parsing: 

```
Traceback (most recent call last):
  File "/Users/deeb/PycharmProjects/datahub_app/parse.py", line 3, in <module>
    res = sqlglot.transpile('''
          ^^^^^^^^^^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/__init__.py", line 177, in transpile
    for expression in parse(sql, read, error_level=error_level)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/__init__.py", line 102, in parse
    return Dialect.get_or_raise(read or dialect).parse(sql, **opts)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/dialects/dialect.py", line 512, in parse
    return self.parser(**opts).parse(self.tokenize(sql), sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/parser.py", line 1246, in parse
    return self._parse(
           ^^^^^^^^^^^^
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/parser.py", line 1318, in _parse
    self.raise_error("Invalid expression / Unexpected token")
  File "/Users/deeb/anaconda3/lib/python3.11/site-packages/sqlglot/parser.py", line 1359, in raise_error
    raise error
sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 2, Col: 14.
  
SELECT u'\ac' FROM dual;
```



Discussion:

Patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 0ddcf4e9..74066765 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -169,6 +169,7 @@ class _Dialect(type):
 
         if enum not in ("", "athena", "presto", "trino"):
             klass.generator_class.TRY_SUPPORTED = False
+            klass.generator_class.SUPPORTS_UESCAPE = False
 
         if enum not in ("", "databricks", "hive", "spark", "spark2"):
             modifier_transforms = klass.generator_class.AFTER_HAVING_MODIFIER_TRANSFORMS.copy()
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index b2db7fb7..0791141f 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -231,6 +231,12 @@ class Oracle(Dialect):
     class Tokenizer(tokens.Tokenizer):
         VAR_SINGLE_TOKENS = {"@", "$", "#"}
 
+        UNICODE_STRINGS = [
+            (prefix + q, q)
+            for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES)
+            for prefix in ("U", "u")
+        ]
+
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
             "(+)": TokenType.JOIN_MARKER,
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index b5624fab..b0de4437 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -356,6 +356,9 @@ class Generator(metaclass=_Generator):
     # Whether the conditional TRY(expression) function is supported
     TRY_SUPPORTED = True
 
+    # Whether the UESCAPE syntax in unicode strings is supported
+    SUPPORTS_UESCAPE = True
+
     # The keyword to use when generating a star projection with excluded columns
     STAR_EXCEPT = "EXCEPT"
 
@@ -1143,16 +1146,23 @@ class Generator(metaclass=_Generator):
         escape = expression.args.get("escape")
 
         if self.dialect.UNICODE_START:
-            escape = f" UESCAPE {self.sql(escape)}" if escape else ""
-            return f"{self.dialect.UNICODE_START}{this}{self.dialect.UNICODE_END}{escape}"
+            escape_substitute = r"\\\1"
+            left_quote, right_quote = self.dialect.UNICODE_START, self.dialect.UNICODE_END
+        else:
+            escape_substitute = r"\\u\1"
+            left_quote, right_quote = self.dialect.QUOTE_START, self.dialect.QUOTE_END
 
         if escape:
-            pattern = re.compile(rf"{escape.name}(\d+)")
+            escape_pattern = re.compile(rf"{escape.name}(\d+)")
+            escape_sql = f" UESCAPE {self.sql(escape)}" if self.SUPPORTS_UESCAPE else ""
         else:
-            pattern = ESCAPED_UNICODE_RE
+            escape_pattern = ESCAPED_UNICODE_RE
+            escape_sql = ""
+
+        if not self.dialect.UNICODE_START or (escape and not self.SUPPORTS_UESCAPE):
+            this = escape_pattern.sub(escape_substitute, this)
 
-        this = pattern.sub(r"\\u\1", this)
-        return f"{self.dialect.QUOTE_START}{this}{self.dialect.QUOTE_END}"
+        return f"{left_quote}{this}{right_quote}{escape_sql}"
 
     def rawstring_sql(self, expression: exp.RawString) -> str:
         string = self.escape_str(expression.this.replace("\\", "\\\\"), escape_backslash=False)


Test patch:
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index f1bbcc13..ebb270ab 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -564,6 +564,7 @@ class TestPresto(Validator):
             self.validate_all(
                 f"{prefix}'Hello winter \\2603 !'",
                 write={
+                    "oracle": "U'Hello winter \\2603 !'",
                     "presto": "U&'Hello winter \\2603 !'",
                     "snowflake": "'Hello winter \\u2603 !'",
                     "spark": "'Hello winter \\u2603 !'",
@@ -572,6 +573,7 @@ class TestPresto(Validator):
             self.validate_all(
                 f"{prefix}'Hello winter #2603 !' UESCAPE '#'",
                 write={
+                    "oracle": "U'Hello winter \\2603 !'",
                     "presto": "U&'Hello winter #2603 !' UESCAPE '#'",
                     "snowflake": "'Hello winter \\u2603 !'",
                     "spark": "'Hello winter \\u2603 !'",