diff options
Diffstat (limited to 'sqlglot/dialects/oracle.py')
-rw-r--r-- | sqlglot/dialects/oracle.py | 62 |
1 files changed, 57 insertions, 5 deletions
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py index fde845e..74baa8a 100644 --- a/sqlglot/dialects/oracle.py +++ b/sqlglot/dialects/oracle.py @@ -1,15 +1,49 @@ 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.helper import csv from sqlglot.tokens import TokenType +PASSING_TABLE_ALIAS_TOKENS = parser.Parser.TABLE_ALIAS_TOKENS - { + TokenType.COLUMN, + TokenType.RETURNING, +} + def _limit_sql(self, expression): return self.fetch_sql(exp.Fetch(direction="FIRST", count=expression.expression)) +def _parse_xml_table(self) -> exp.XMLTable: + this = self._parse_string() + + passing = None + columns = None + + if self._match_text_seq("PASSING"): + # The BY VALUE keywords are optional and are provided for semantic clarity + self._match_text_seq("BY", "VALUE") + passing = self._parse_csv( + lambda: self._parse_table(alias_tokens=PASSING_TABLE_ALIAS_TOKENS) + ) + + by_ref = self._match_text_seq("RETURNING", "SEQUENCE", "BY", "REF") + + if self._match_text_seq("COLUMNS"): + columns = self._parse_csv(lambda: self._parse_column_def(self._parse_field(any_token=True))) + + return self.expression( + exp.XMLTable, + this=this, + passing=passing, + columns=columns, + by_ref=by_ref, + ) + + class Oracle(Dialect): # https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212 # https://docs.python.org/3/library/datetime.html#strftime-and-strptime-format-codes @@ -43,6 +77,11 @@ class Oracle(Dialect): "DECODE": exp.Matches.from_arg_list, } + FUNCTION_PARSERS: t.Dict[str, t.Callable] = { + **parser.Parser.FUNCTION_PARSERS, + "XMLTABLE": _parse_xml_table, + } + class Generator(generator.Generator): LOCKING_READS_SUPPORTED = True @@ -74,7 +113,7 @@ class Oracle(Dialect): exp.Substring: rename_func("SUBSTR"), } - def query_modifiers(self, expression, *sqls): + def query_modifiers(self, expression: exp.Expression, *sqls: str) -> str: return csv( *sqls, *[self.sql(sql) for sql in expression.args.get("joins") or []], @@ -97,19 +136,32 @@ class Oracle(Dialect): sep="", ) - def offset_sql(self, expression): + def offset_sql(self, expression: exp.Offset) -> str: return f"{super().offset_sql(expression)} ROWS" - def table_sql(self, expression): - return super().table_sql(expression, sep=" ") + def table_sql(self, expression: exp.Table, sep: str = " ") -> str: + return super().table_sql(expression, sep=sep) + + def xmltable_sql(self, expression: exp.XMLTable) -> str: + this = self.sql(expression, "this") + passing = self.expressions(expression, "passing") + passing = f"{self.sep()}PASSING{self.seg(passing)}" if passing else "" + columns = self.expressions(expression, "columns") + columns = f"{self.sep()}COLUMNS{self.seg(columns)}" if columns else "" + by_ref = ( + f"{self.sep()}RETURNING SEQUENCE BY REF" if expression.args.get("by_ref") else "" + ) + return f"XMLTABLE({self.sep('')}{self.indent(this + passing + by_ref + columns)}{self.seg(')', sep='')}" class Tokenizer(tokens.Tokenizer): KEYWORDS = { **tokens.Tokenizer.KEYWORDS, + "COLUMNS": TokenType.COLUMN, "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE, "MINUS": TokenType.EXCEPT, + "NVARCHAR2": TokenType.NVARCHAR, + "RETURNING": TokenType.RETURNING, "START": TokenType.BEGIN, "TOP": TokenType.TOP, "VARCHAR2": TokenType.VARCHAR, - "NVARCHAR2": TokenType.NVARCHAR, } |