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