Repository: tobymao/sqlglot

Issue:
add support for table cast to parse the tables correctly when casting is done with user defined table types
Hello Team,
Warm greetings!

Parser is unable to parse query when TABLE(CAST(:VAR AS PAKAGE.UERDEFINEDTABLETYPE)) is used.
TABLE(CAST(...)) retuning wrong table name. But when we used Schema or pakage name o access the userdefined datatype, then sqlglot fails.

**Oracle Documentation -**
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6600331069731
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj33562.html

**Code for your reference -**

```
import sqlglot.expressions as exp

# previously raised issue, but it returns 'TABLE(CAST(somelist ' as output
query1 ="""
SELECT col1, col2
FROM table1, TABLE(CAST(somelist as data_list))
WHERE col like '%contact'
"""

# user defined table datatype can be accessed with pakage.<type>
# below query generate error as it expects ')' and not the dot(.)
query2="""
SELECT col1, col2
FROM table1, TABLE(CAST(somelist as pakage.data_list))
WHERE col like '%contact'
"""
expressions = sqlglot.parse_one(query2, read='oracle')
x = list(expressions.find_all(exp.Table))

for i in x:
  listTables = str(i).split(' ')[0]
  print(listTables)
```

Note that above queries are valid oracle queries.

query1 returns incorrect data and query2 fails.
Request you to add support for the same.

Thanks ton in advance!

Thanks,
Sachin

Discussion:

Patch:
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 3472f9df..06bc1eb5 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -3279,7 +3279,12 @@ class Parser(metaclass=_Parser):
                 if tokens[0].token_type in self.TYPE_TOKENS:
                     self._prev = tokens[0]
                 elif self.SUPPORTS_USER_DEFINED_TYPES:
-                    return exp.DataType.build(identifier.name, udt=True)
+                    type_name = identifier.name
+
+                    while self._match(TokenType.DOT):
+                        type_name = f"{type_name}.{self._advance_any() and self._prev.text}"
+
+                    return exp.DataType.build(type_name, udt=True)
                 else:
                     return None
             else:


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 8e718e65..675ee8a4 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -91,6 +91,16 @@ class TestOracle(Validator):
                 "": "CAST(x AS FLOAT)",
             },
         )
+        self.validate_all(
+            "CAST(x AS sch.udt)",
+            read={
+                "postgres": "CAST(x AS sch.udt)",
+            },
+            write={
+                "oracle": "CAST(x AS sch.udt)",
+                "postgres": "CAST(x AS sch.udt)",
+            },
+        )
 
     def test_join_marker(self):
         self.validate_identity("SELECT e1.x, e2.x FROM e e1, e e2 WHERE e1.y (+) = e2.y")
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 5d1f810f..b3ce926f 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -934,5 +934,8 @@ FROM foo""",
         assert dtype.is_type("foo")
         assert not dtype.is_type("bar")
 
+        dtype = exp.DataType.build("a.b.c", udt=True)
+        assert dtype.is_type("a.b.c")
+
         with self.assertRaises(ParseError):
             exp.DataType.build("foo")