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 ensure_list, seq_get
  8from sqlglot.parser import IsPredicatePrecedence, 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_str_to_time(exp_class: type[E]) -> t.Callable[[list, Dialect], E]:
 17    def _builder(args: list, dialect: Dialect) -> E:
 18        expression = build_formatted_time(exp_class)(args, dialect)
 19        expression.set("nlsparam", seq_get(args, 2))
 20        return expression
 21
 22    return _builder
 23
 24
 25def _build_to_timestamp(args: list, dialect: Dialect) -> exp.StrToTime | exp.Anonymous:
 26    if len(args) == 1:
 27        return exp.Anonymous(this="TO_TIMESTAMP", expressions=args)
 28
 29    return _build_str_to_time(exp.StrToTime)(args, dialect)
 30
 31
 32class OracleParser(parser.Parser):
 33    IS_PREDICATE_PRECEDENCE = IsPredicatePrecedence.LOW
 34
 35    WINDOW_BEFORE_PAREN_TOKENS = {TokenType.OVER, TokenType.KEEP}
 36    VALUES_FOLLOWED_BY_PAREN = False
 37    SUPPORTS_NTH_VALUE_FROM_MODIFIER = True
 38    SUPPORTS_PARTITION_SELECTION = True
 39
 40    ID_VAR_TOKENS = parser.Parser.ID_VAR_TOKENS | {
 41        TokenType.MATCH_RECOGNIZE,
 42        TokenType.TABLE_SAMPLE,
 43    }
 44    ALIAS_TOKENS = ID_VAR_TOKENS
 45
 46    # These clauses can't follow GROUP BY, so e.g. `GROUP BY sample` refers to a column
 47    QUERY_MODIFIER_TOKENS = parser.Parser.QUERY_MODIFIER_TOKENS - {
 48        TokenType.MATCH_RECOGNIZE,
 49        TokenType.TABLE_SAMPLE,
 50    }
 51
 52    VERSION_PHRASES = {
 53        **parser.Parser.VERSION_PHRASES,
 54        ("AS", "OF", "SCN"): "SCN",
 55        ("AS", "OF", "TIMESTAMP"): "TIMESTAMP",
 56    }
 57
 58    FUNCTIONS = {
 59        **{k: v for k, v in parser.Parser.FUNCTIONS.items() if k != "TO_BOOLEAN"},
 60        "ADD_MONTHS": lambda args: exp.AddMonths(
 61            this=seq_get(args, 0),
 62            expression=seq_get(args, 1),
 63            preserve_end_of_month=True,
 64        ),
 65        "CONVERT": exp.ConvertToCharset.from_arg_list,
 66        "L2_DISTANCE": exp.EuclideanDistance.from_arg_list,
 67        "NVL": lambda args: build_coalesce(args, is_nvl=True),
 68        "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
 69        "TO_CHAR": build_timetostr_or_tochar,
 70        "TO_TIMESTAMP": _build_to_timestamp,
 71        "TO_DATE": _build_str_to_time(exp.StrToDate),
 72        "TRUNC": lambda args, dialect: build_trunc(
 73            args, dialect, date_trunc_unabbreviate=False, default_date_trunc_unit="DD"
 74        ),
 75    }
 76
 77    NO_PAREN_FUNCTION_PARSERS = {
 78        **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
 79        "NEXT": lambda self: self._parse_next_value_for(),
 80        "PRIOR": lambda self: self.expression(exp.Prior(this=self._parse_bitwise())),
 81        "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp(sysdate=True)),
 82        "DBMS_RANDOM": lambda self: self._parse_dbms_random(),
 83    }
 84
 85    NO_PAREN_FUNCTIONS = {
 86        **parser.Parser.NO_PAREN_FUNCTIONS,
 87        TokenType.LOCALTIMESTAMP: exp.Localtimestamp,
 88        TokenType.SYSTIMESTAMP: exp.Systimestamp,
 89    }
 90
 91    FUNCTION_PARSERS = {
 92        **{k: v for k, v in parser.Parser.FUNCTION_PARSERS.items() if k != "CONVERT"},
 93        "JSON_ARRAY": lambda self: self._parse_oracle_json_array(),
 94        "JSON_ARRAYAGG": lambda self: self._parse_oracle_json_arrayagg(),
 95        "JSON_EXISTS": lambda self: self._parse_json_exists(),
 96        "JSON_VALUE": lambda self: self._parse_json_value(),
 97        "LISTAGG": lambda self: self._parse_string_agg(),
 98        "TO_NUMBER": lambda self: self._parse_to_number(),
 99    }
100
101    PROPERTY_PARSERS = {
102        **parser.Parser.PROPERTY_PARSERS,
103        "GLOBAL": lambda self: (
104            self._match_text_seq("TEMPORARY")
105            and self.expression(exp.TemporaryProperty(this="GLOBAL"))
106        ),
107        "PRIVATE": lambda self: (
108            self._match_text_seq("TEMPORARY")
109            and self.expression(exp.TemporaryProperty(this="PRIVATE"))
110        ),
111        "FORCE": lambda self: self.expression(exp.ForceProperty()),
112        "TABLESPACE": lambda self: self.expression(
113            exp.TableSpaceProperty(this=self._parse_id_var())
114        ),
115        **dict.fromkeys(
116            ("PCTFREE", "PCTUSED", "INITRANS", "MAXTRANS"),
117            lambda self: self._parse_physical_property(),
118        ),
119        **dict.fromkeys(
120            ("LOGGING", "NOLOGGING"),
121            lambda self: self.expression(exp.PhysicalProperty(this=self._prev.text.upper())),
122        ),
123        "STORAGE": lambda self: self._parse_storage(),
124    }
125
126    STORAGE_OPTIONS = {
127        "INITIAL",
128        "NEXT",
129        "MINEXTENTS",
130        "MAXEXTENTS",
131        "MAXSIZE",
132        "PCTINCREASE",
133        "FREELISTS",
134        "FREELIST",
135        "BUFFER_POOL",
136        "FLASH_CACHE",
137        "CELL_FLASH_CACHE",
138    }
139
140    QUERY_MODIFIER_PARSERS = {
141        **parser.Parser.QUERY_MODIFIER_PARSERS,
142        TokenType.ORDER_SIBLINGS_BY: lambda self: ("order", self._parse_order()),
143        TokenType.WITH: lambda self: (
144            "options",
145            ensure_list(self._parse_query_restrictions()),
146        ),
147    }
148
149    TYPE_LITERAL_PARSERS = {
150        exp.DType.DATE: lambda self, this, _: self.expression(exp.DateStrToDate(this=this)),
151        # https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/NLS_TIMESTAMP_FORMAT.html
152        exp.DType.TIMESTAMP: lambda self, this, _: _build_to_timestamp(
153            [this, '"%Y-%m-%d %H:%M:%S.%f"'], self.dialect
154        ),
155    }
156
157    # SELECT UNIQUE .. is old-style Oracle syntax for SELECT DISTINCT ..
158    # Reference: https://stackoverflow.com/a/336455
159    DISTINCT_TOKENS = {TokenType.DISTINCT, TokenType.UNIQUE}
160
161    QUERY_RESTRICTIONS: t.ClassVar[OPTIONS_TYPE] = {
162        "WITH": (
163            ("READ", "ONLY"),
164            ("CHECK", "OPTION"),
165        ),
166    }
167
168    def _parse_physical_property(self) -> exp.PhysicalProperty:
169        name = self._prev.text.upper()
170        value = self._parse_number()
171        if value is None:
172            self.raise_error(f"Expected value for {name}")
173        return self.expression(exp.PhysicalProperty(this=name, value=value))
174
175    def _parse_storage(self) -> exp.StorageProperty:
176        def parse_options() -> list[exp.Expr]:
177            options: list[exp.Expr] = []
178            while self._match_texts(self.STORAGE_OPTIONS):
179                name = self._prev.text.upper()
180                if name == "FREELIST":
181                    if not self._match_text_seq("GROUPS"):
182                        self.raise_error("Expected GROUPS after FREELIST")
183                    name = "FREELIST GROUPS"
184
185                if name in ("BUFFER_POOL", "FLASH_CACHE", "CELL_FLASH_CACHE"):
186                    value: exp.Expr | None = self._parse_var_from_options(
187                        {
188                            "KEEP": (),
189                            "DEFAULT": (),
190                            "RECYCLE" if name == "BUFFER_POOL" else "NONE": (),
191                        }
192                    )
193                elif name in ("MAXEXTENTS", "MAXSIZE") and self._match_text_seq("UNLIMITED"):
194                    value = exp.var("UNLIMITED")
195                else:
196                    value = self._parse_number()
197                    if (
198                        value is not None
199                        and name in ("INITIAL", "NEXT", "MAXSIZE")
200                        and self._match_texts(("K", "M", "G", "T", "P", "E"))
201                    ):
202                        value = exp.var(f"{value.name}{self._prev.text.upper()}")
203                if value is None:
204                    self.raise_error(f"Expected value for {name}")
205                options.append(self.expression(exp.PhysicalProperty(this=name, value=value)))
206            return options
207
208        return self.expression(exp.StorageProperty(expressions=self._parse_wrapped(parse_options)))
209
210    def _parse_to_number(self) -> exp.ToNumber:
211        # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TO_NUMBER.html
212        this = self._parse_bitwise()
213
214        default = None
215        if self._match(TokenType.DEFAULT):
216            default = self._parse_bitwise()
217            self._match_text_seq("ON", "CONVERSION", "ERROR")
218
219        fmt = None
220        nlsparam = None
221        if self._match(TokenType.COMMA):
222            fmt = self._parse_bitwise()
223            if self._match(TokenType.COMMA):
224                nlsparam = self._parse_bitwise()
225
226        return self.expression(
227            exp.ToNumber(this=this, format=fmt, nlsparam=nlsparam, default=default)
228        )
229
230    def _parse_dbms_random(self) -> exp.Expr | None:
231        if self._match_text_seq(".", "VALUE"):
232            lower, upper = None, None
233            if self._match(TokenType.L_PAREN, advance=False):
234                lower_upper = self._parse_wrapped_csv(self._parse_bitwise)
235                if len(lower_upper) == 2:
236                    lower, upper = lower_upper
237
238            return exp.Rand(lower=lower, upper=upper)
239
240        self._retreat(self._index - 1)
241        return None
242
243    def _parse_oracle_json_array(self) -> exp.JSONArray:
244        return self._parse_json_array(
245            exp.JSONArray,
246            expressions=self._parse_csv(lambda: self._parse_format_json(self._parse_bitwise())),
247        )
248
249    def _parse_oracle_json_arrayagg(self) -> exp.JSONArrayAgg:
250        return self._parse_json_array(
251            exp.JSONArrayAgg,
252            this=self._parse_format_json(self._parse_bitwise()),
253            order=self._parse_order(),
254        )
255
256    def _parse_json_array(self, expr_type: type[E], **kwargs) -> E:
257        return self.expression(
258            expr_type(
259                null_handling=self._parse_on_handling("NULL", "NULL", "ABSENT"),
260                return_type=self._match_text_seq("RETURNING") and self._parse_type(),
261                strict=self._match_text_seq("STRICT"),
262                **kwargs,
263            )
264        )
265
266    def _parse_hint_function_call(self) -> exp.Expr | None:
267        if not self._curr or not self._next or self._next.token_type != TokenType.L_PAREN:
268            return None
269
270        name = self._curr.text
271
272        self._advance(2)
273        args = self._parse_hint_args()
274        this = self.expression(exp.Anonymous(this=name, expressions=args))
275        self._match_r_paren(this)
276        return this
277
278    def _parse_hint_args(self):
279        args = []
280        result = self._parse_var()
281
282        while result:
283            args.append(result)
284            result = self._parse_var()
285
286        return args
287
288    def _parse_query_restrictions(self) -> exp.Expr | None:
289        kind = self._parse_var_from_options(self.QUERY_RESTRICTIONS, raise_unmatched=False)
290
291        if not kind:
292            return None
293
294        return self.expression(
295            exp.QueryOption(
296                this=kind, expression=self._match(TokenType.CONSTRAINT) and self._parse_field()
297            )
298        )
299
300    def _parse_json_exists(self) -> exp.JSONExists:
301        this = self._parse_format_json(self._parse_bitwise())
302        self._match(TokenType.COMMA)
303        return self.expression(
304            exp.JSONExists(
305                this=this,
306                path=self.dialect.to_json_path(self._parse_bitwise()),
307                passing=self._match_text_seq("PASSING")
308                and self._parse_csv(lambda: self._parse_alias(self._parse_bitwise())),
309                on_condition=self._parse_on_condition(),
310            )
311        )
312
313    def _parse_into(self) -> exp.Into | None:
314        # https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/SELECT-INTO-statement.html
315        bulk_collect = self._match(TokenType.BULK_COLLECT_INTO)
316        if not bulk_collect and not self._match(TokenType.INTO):
317            return None
318
319        index = self._index
320
321        expressions = self._parse_expressions()
322        if len(expressions) == 1:
323            self._retreat(index)
324            self._match(TokenType.TABLE)
325            return self.expression(
326                exp.Into(this=self._parse_table(schema=True), bulk_collect=bulk_collect)
327            )
328
329        return self.expression(exp.Into(bulk_collect=bulk_collect, expressions=expressions))
330
331    def _parse_connect_with_prior(self):
332        return self._parse_assignment()
333
334    def _parse_column_ops(self, this: exp.Expr | None) -> exp.Expr | None:
335        this = super()._parse_column_ops(this)
336
337        if not this:
338            return this
339
340        index = self._index
341
342        # https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/Interval-Exprs.html
343        interval_span = self._try_parse(lambda: self._parse_interval_span(this))
344        if interval_span and isinstance(interval_span.args.get("unit"), exp.IntervalSpan):
345            return interval_span
346
347        self._retreat(index)
348        return this
349
350    def _parse_insert_table(self) -> exp.Expr | None:
351        # Oracle does not use AS for INSERT INTO alias
352        # https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/INSERT.html
353        # Parse table parts without schema to avoid parsing the alias with its columns
354        this = self._parse_table_parts(schema=True)
355
356        if isinstance(this, exp.Table):
357            this.set("partition", self._parse_partition())
358
359            alias_name = self._parse_id_var(any_token=False)
360            if alias_name:
361                this.set("alias", exp.TableAlias(this=alias_name))
362
363            # Now parse the schema (column list) if present
364            return self._parse_schema(this=this)
365
366        return this