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