sqlglot.parsers.oracle

  1from __future__ import annotations
  2
  3import typing as t
  4
  5from sqlglot import exp, parser
  6from sqlglot.dialects.dialect import build_formatted_time, build_timetostr_or_tochar, build_trunc
  7from sqlglot.helper import seq_get
  8from sqlglot.parser import OPTIONS_TYPE, build_coalesce
  9from sqlglot.tokens import TokenType
 10
 11if t.TYPE_CHECKING:
 12    from sqlglot._typing import E
 13    from sqlglot.dialects.dialect import Dialect
 14
 15
 16def _build_to_timestamp(args: list, dialect: Dialect) -> exp.StrToTime | exp.Anonymous:
 17    if len(args) == 1:
 18        return exp.Anonymous(this="TO_TIMESTAMP", expressions=args)
 19
 20    return build_formatted_time(exp.StrToTime)(args, dialect)
 21
 22
 23class OracleParser(parser.Parser):
 24    WINDOW_BEFORE_PAREN_TOKENS = {TokenType.OVER, TokenType.KEEP}
 25    VALUES_FOLLOWED_BY_PAREN = False
 26
 27    FUNCTIONS = {
 28        **{k: v for k, v in parser.Parser.FUNCTIONS.items() if k != "TO_BOOLEAN"},
 29        "CONVERT": exp.ConvertToCharset.from_arg_list,
 30        "L2_DISTANCE": exp.EuclideanDistance.from_arg_list,
 31        "NVL": lambda args: build_coalesce(args, is_nvl=True),
 32        "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
 33        "TO_CHAR": build_timetostr_or_tochar,
 34        "TO_TIMESTAMP": _build_to_timestamp,
 35        "TO_DATE": build_formatted_time(exp.StrToDate),
 36        "TRUNC": lambda args, dialect: build_trunc(
 37            args, dialect, date_trunc_unabbreviate=False, default_date_trunc_unit="DD"
 38        ),
 39    }
 40
 41    NO_PAREN_FUNCTION_PARSERS = {
 42        **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
 43        "NEXT": lambda self: self._parse_next_value_for(),
 44        "PRIOR": lambda self: self.expression(exp.Prior(this=self._parse_bitwise())),
 45        "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp(sysdate=True)),
 46        "DBMS_RANDOM": lambda self: self._parse_dbms_random(),
 47    }
 48
 49    NO_PAREN_FUNCTIONS = {
 50        **parser.Parser.NO_PAREN_FUNCTIONS,
 51        TokenType.LOCALTIMESTAMP: exp.Localtimestamp,
 52        TokenType.SYSTIMESTAMP: exp.Systimestamp,
 53    }
 54
 55    FUNCTION_PARSERS = {
 56        **{k: v for k, v in parser.Parser.FUNCTION_PARSERS.items() if k != "CONVERT"},
 57        "JSON_ARRAY": lambda self: self._parse_oracle_json_array(),
 58        "JSON_ARRAYAGG": lambda self: self._parse_oracle_json_arrayagg(),
 59        "JSON_EXISTS": lambda self: self._parse_json_exists(),
 60        "LISTAGG": lambda self: self._parse_string_agg(),
 61    }
 62
 63    PROPERTY_PARSERS = {
 64        **parser.Parser.PROPERTY_PARSERS,
 65        "GLOBAL": lambda self: (
 66            self._match_text_seq("TEMPORARY")
 67            and self.expression(exp.TemporaryProperty(this="GLOBAL"))
 68        ),
 69        "PRIVATE": lambda self: (
 70            self._match_text_seq("TEMPORARY")
 71            and self.expression(exp.TemporaryProperty(this="PRIVATE"))
 72        ),
 73        "FORCE": lambda self: self.expression(exp.ForceProperty()),
 74    }
 75
 76    QUERY_MODIFIER_PARSERS = {
 77        **parser.Parser.QUERY_MODIFIER_PARSERS,
 78        TokenType.ORDER_SIBLINGS_BY: lambda self: ("order", self._parse_order()),
 79        TokenType.WITH: lambda self: ("options", [self._parse_query_restrictions()]),
 80    }
 81
 82    TYPE_LITERAL_PARSERS = {
 83        exp.DType.DATE: lambda self, this, _: self.expression(exp.DateStrToDate(this=this)),
 84        # https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/NLS_TIMESTAMP_FORMAT.html
 85        exp.DType.TIMESTAMP: lambda self, this, _: _build_to_timestamp(
 86            [this, '"%Y-%m-%d %H:%M:%S.%f"'], self.dialect
 87        ),
 88    }
 89
 90    # SELECT UNIQUE .. is old-style Oracle syntax for SELECT DISTINCT ..
 91    # Reference: https://stackoverflow.com/a/336455
 92    DISTINCT_TOKENS = {TokenType.DISTINCT, TokenType.UNIQUE}
 93
 94    QUERY_RESTRICTIONS: t.ClassVar[OPTIONS_TYPE] = {
 95        "WITH": (
 96            ("READ", "ONLY"),
 97            ("CHECK", "OPTION"),
 98        ),
 99    }
100
101    def _parse_dbms_random(self) -> exp.Expr | None:
102        if self._match_text_seq(".", "VALUE"):
103            lower, upper = None, None
104            if self._match(TokenType.L_PAREN, advance=False):
105                lower_upper = self._parse_wrapped_csv(self._parse_bitwise)
106                if len(lower_upper) == 2:
107                    lower, upper = lower_upper
108
109            return exp.Rand(lower=lower, upper=upper)
110
111        self._retreat(self._index - 1)
112        return None
113
114    def _parse_oracle_json_array(self) -> exp.JSONArray:
115        return self._parse_json_array(
116            exp.JSONArray,
117            expressions=self._parse_csv(lambda: self._parse_format_json(self._parse_bitwise())),
118        )
119
120    def _parse_oracle_json_arrayagg(self) -> exp.JSONArrayAgg:
121        return self._parse_json_array(
122            exp.JSONArrayAgg,
123            this=self._parse_format_json(self._parse_bitwise()),
124            order=self._parse_order(),
125        )
126
127    def _parse_json_array(self, expr_type: type[E], **kwargs) -> E:
128        return self.expression(
129            expr_type(
130                null_handling=self._parse_on_handling("NULL", "NULL", "ABSENT"),
131                return_type=self._match_text_seq("RETURNING") and self._parse_type(),
132                strict=self._match_text_seq("STRICT"),
133                **kwargs,
134            )
135        )
136
137    def _parse_hint_function_call(self) -> exp.Expr | None:
138        if not self._curr or not self._next or self._next.token_type != TokenType.L_PAREN:
139            return None
140
141        name = self._curr.text
142
143        self._advance(2)
144        args = self._parse_hint_args()
145        this = self.expression(exp.Anonymous(this=name, expressions=args))
146        self._match_r_paren(this)
147        return this
148
149    def _parse_hint_args(self):
150        args = []
151        result = self._parse_var()
152
153        while result:
154            args.append(result)
155            result = self._parse_var()
156
157        return args
158
159    def _parse_query_restrictions(self) -> exp.Expr | None:
160        kind = self._parse_var_from_options(self.QUERY_RESTRICTIONS, raise_unmatched=False)
161
162        if not kind:
163            return None
164
165        return self.expression(
166            exp.QueryOption(
167                this=kind, expression=self._match(TokenType.CONSTRAINT) and self._parse_field()
168            )
169        )
170
171    def _parse_json_exists(self) -> exp.JSONExists:
172        this = self._parse_format_json(self._parse_bitwise())
173        self._match(TokenType.COMMA)
174        return self.expression(
175            exp.JSONExists(
176                this=this,
177                path=self.dialect.to_json_path(self._parse_bitwise()),
178                passing=self._match_text_seq("PASSING")
179                and self._parse_csv(lambda: self._parse_alias(self._parse_bitwise())),
180                on_condition=self._parse_on_condition(),
181            )
182        )
183
184    def _parse_into(self) -> exp.Into | None:
185        # https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/SELECT-INTO-statement.html
186        bulk_collect = self._match(TokenType.BULK_COLLECT_INTO)
187        if not bulk_collect and not self._match(TokenType.INTO):
188            return None
189
190        index = self._index
191
192        expressions = self._parse_expressions()
193        if len(expressions) == 1:
194            self._retreat(index)
195            self._match(TokenType.TABLE)
196            return self.expression(
197                exp.Into(this=self._parse_table(schema=True), bulk_collect=bulk_collect)
198            )
199
200        return self.expression(exp.Into(bulk_collect=bulk_collect, expressions=expressions))
201
202    def _parse_connect_with_prior(self):
203        return self._parse_assignment()
204
205    def _parse_column_ops(self, this: exp.Expr | None) -> exp.Expr | None:
206        this = super()._parse_column_ops(this)
207
208        if not this:
209            return this
210
211        index = self._index
212
213        # https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Interval-Exprs.html
214        interval_span = self._try_parse(lambda: self._parse_interval_span(this))
215        if interval_span and isinstance(interval_span.args.get("unit"), exp.IntervalSpan):
216            return interval_span
217
218        self._retreat(index)
219        return this
220
221    def _parse_insert_table(self) -> exp.Expr | None:
222        # Oracle does not use AS for INSERT INTO alias
223        # https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/INSERT.html
224        # Parse table parts without schema to avoid parsing the alias with its columns
225        this = self._parse_table_parts(schema=True)
226
227        if isinstance(this, exp.Table):
228            alias_name = self._parse_id_var(any_token=False)
229            if alias_name:
230                this.set("alias", exp.TableAlias(this=alias_name))
231
232            this.set("partition", self._parse_partition())
233
234            # Now parse the schema (column list) if present
235            return self._parse_schema(this=this)
236
237        return this