sqlglot.dialects.oracle

 46class Oracle(Dialect):
 47    ALIAS_POST_TABLESAMPLE = True
 48    LOCKING_READS_SUPPORTED = True
 49    TABLESAMPLE_SIZE_IS_PERCENT = True
 50    NULL_ORDERING = "nulls_are_large"
 51    ON_CONDITION_EMPTY_BEFORE_ERROR = False
 52
 53    # See section 8: https://docs.oracle.com/cd/A97630_01/server.920/a96540/sql_elements9a.htm
 54    NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE
 55
 56    # https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212
 57    # https://docs.python.org/3/library/datetime.html#strftime-and-strptime-format-codes
 58    TIME_MAPPING = {
 59        "AM": "%p",  # Meridian indicator with or without periods
 60        "A.M.": "%p",  # Meridian indicator with or without periods
 61        "PM": "%p",  # Meridian indicator with or without periods
 62        "P.M.": "%p",  # Meridian indicator with or without periods
 63        "D": "%u",  # Day of week (1-7)
 64        "DAY": "%A",  # name of day
 65        "DD": "%d",  # day of month (1-31)
 66        "DDD": "%j",  # day of year (1-366)
 67        "DY": "%a",  # abbreviated name of day
 68        "HH": "%I",  # Hour of day (1-12)
 69        "HH12": "%I",  # alias for HH
 70        "HH24": "%H",  # Hour of day (0-23)
 71        "IW": "%V",  # Calendar week of year (1-52 or 1-53), as defined by the ISO 8601 standard
 72        "MI": "%M",  # Minute (0-59)
 73        "MM": "%m",  # Month (01-12; January = 01)
 74        "MON": "%b",  # Abbreviated name of month
 75        "MONTH": "%B",  # Name of month
 76        "SS": "%S",  # Second (0-59)
 77        "WW": "%W",  # Week of year (1-53)
 78        "YY": "%y",  # 15
 79        "YYYY": "%Y",  # 2015
 80        "FF6": "%f",  # only 6 digits are supported in python formats
 81    }
 82
 83    class Tokenizer(tokens.Tokenizer):
 84        VAR_SINGLE_TOKENS = {"@", "$", "#"}
 85
 86        UNICODE_STRINGS = [
 87            (prefix + q, q)
 88            for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES)
 89            for prefix in ("U", "u")
 90        ]
 91
 92        NESTED_COMMENTS = False
 93
 94        KEYWORDS = {
 95            **tokens.Tokenizer.KEYWORDS,
 96            "(+)": TokenType.JOIN_MARKER,
 97            "BINARY_DOUBLE": TokenType.DOUBLE,
 98            "BINARY_FLOAT": TokenType.FLOAT,
 99            "BULK COLLECT INTO": TokenType.BULK_COLLECT_INTO,
100            "COLUMNS": TokenType.COLUMN,
101            "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
102            "MINUS": TokenType.EXCEPT,
103            "NVARCHAR2": TokenType.NVARCHAR,
104            "ORDER SIBLINGS BY": TokenType.ORDER_SIBLINGS_BY,
105            "SAMPLE": TokenType.TABLE_SAMPLE,
106            "START": TokenType.BEGIN,
107            "TOP": TokenType.TOP,
108            "VARCHAR2": TokenType.VARCHAR,
109        }
110
111    class Parser(parser.Parser):
112        ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False
113        WINDOW_BEFORE_PAREN_TOKENS = {TokenType.OVER, TokenType.KEEP}
114        VALUES_FOLLOWED_BY_PAREN = False
115
116        FUNCTIONS = {
117            **parser.Parser.FUNCTIONS,
118            "NVL": lambda args: build_coalesce(args, is_nvl=True),
119            "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
120            "TO_CHAR": _build_timetostr_or_tochar,
121            "TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "oracle"),
122            "TO_DATE": build_formatted_time(exp.StrToDate, "oracle"),
123            "TRUNC": lambda args: exp.DateTrunc(
124                unit=seq_get(args, 1) or exp.Literal.string("DD"),
125                this=seq_get(args, 0),
126                unabbreviate=False,
127            ),
128        }
129
130        NO_PAREN_FUNCTION_PARSERS = {
131            **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
132            "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp, sysdate=True),
133        }
134
135        FUNCTION_PARSERS: t.Dict[str, t.Callable] = {
136            **parser.Parser.FUNCTION_PARSERS,
137            "JSON_ARRAY": lambda self: self._parse_json_array(
138                exp.JSONArray,
139                expressions=self._parse_csv(lambda: self._parse_format_json(self._parse_bitwise())),
140            ),
141            "JSON_ARRAYAGG": lambda self: self._parse_json_array(
142                exp.JSONArrayAgg,
143                this=self._parse_format_json(self._parse_bitwise()),
144                order=self._parse_order(),
145            ),
146            "XMLTABLE": lambda self: self._parse_xml_table(),
147            "JSON_EXISTS": lambda self: self._parse_json_exists(),
148        }
149
150        PROPERTY_PARSERS = {
151            **parser.Parser.PROPERTY_PARSERS,
152            "GLOBAL": lambda self: self._match_text_seq("TEMPORARY")
153            and self.expression(exp.TemporaryProperty, this="GLOBAL"),
154            "PRIVATE": lambda self: self._match_text_seq("TEMPORARY")
155            and self.expression(exp.TemporaryProperty, this="PRIVATE"),
156        }
157
158        QUERY_MODIFIER_PARSERS = {
159            **parser.Parser.QUERY_MODIFIER_PARSERS,
160            TokenType.ORDER_SIBLINGS_BY: lambda self: ("order", self._parse_order()),
161            TokenType.WITH: lambda self: ("options", [self._parse_query_restrictions()]),
162        }
163
164        TYPE_LITERAL_PARSERS = {
165            exp.DataType.Type.DATE: lambda self, this, _: self.expression(
166                exp.DateStrToDate, this=this
167            )
168        }
169
170        # SELECT UNIQUE .. is old-style Oracle syntax for SELECT DISTINCT ..
171        # Reference: https://stackoverflow.com/a/336455
172        DISTINCT_TOKENS = {TokenType.DISTINCT, TokenType.UNIQUE}
173
174        QUERY_RESTRICTIONS: OPTIONS_TYPE = {
175            "WITH": (
176                ("READ", "ONLY"),
177                ("CHECK", "OPTION"),
178            ),
179        }
180
181        def _parse_xml_table(self) -> exp.XMLTable:
182            this = self._parse_string()
183
184            passing = None
185            columns = None
186
187            if self._match_text_seq("PASSING"):
188                # The BY VALUE keywords are optional and are provided for semantic clarity
189                self._match_text_seq("BY", "VALUE")
190                passing = self._parse_csv(self._parse_column)
191
192            by_ref = self._match_text_seq("RETURNING", "SEQUENCE", "BY", "REF")
193
194            if self._match_text_seq("COLUMNS"):
195                columns = self._parse_csv(self._parse_field_def)
196
197            return self.expression(
198                exp.XMLTable, this=this, passing=passing, columns=columns, by_ref=by_ref
199            )
200
201        def _parse_json_array(self, expr_type: t.Type[E], **kwargs) -> E:
202            return self.expression(
203                expr_type,
204                null_handling=self._parse_on_handling("NULL", "NULL", "ABSENT"),
205                return_type=self._match_text_seq("RETURNING") and self._parse_type(),
206                strict=self._match_text_seq("STRICT"),
207                **kwargs,
208            )
209
210        def _parse_hint_function_call(self) -> t.Optional[exp.Expression]:
211            if not self._curr or not self._next or self._next.token_type != TokenType.L_PAREN:
212                return None
213
214            this = self._curr.text
215
216            self._advance(2)
217            args = self._parse_hint_args()
218            this = self.expression(exp.Anonymous, this=this, expressions=args)
219            self._match_r_paren(this)
220            return this
221
222        def _parse_hint_args(self):
223            args = []
224            result = self._parse_var()
225
226            while result:
227                args.append(result)
228                result = self._parse_var()
229
230            return args
231
232        def _parse_query_restrictions(self) -> t.Optional[exp.Expression]:
233            kind = self._parse_var_from_options(self.QUERY_RESTRICTIONS, raise_unmatched=False)
234
235            if not kind:
236                return None
237
238            return self.expression(
239                exp.QueryOption,
240                this=kind,
241                expression=self._match(TokenType.CONSTRAINT) and self._parse_field(),
242            )
243
244        def _parse_json_exists(self) -> exp.JSONExists:
245            this = self._parse_format_json(self._parse_bitwise())
246            self._match(TokenType.COMMA)
247            return self.expression(
248                exp.JSONExists,
249                this=this,
250                path=self.dialect.to_json_path(self._parse_bitwise()),
251                passing=self._match_text_seq("PASSING")
252                and self._parse_csv(lambda: self._parse_alias(self._parse_bitwise())),
253                on_condition=self._parse_on_condition(),
254            )
255
256        def _parse_into(self) -> t.Optional[exp.Into]:
257            # https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/SELECT-INTO-statement.html
258            bulk_collect = self._match(TokenType.BULK_COLLECT_INTO)
259            if not bulk_collect and not self._match(TokenType.INTO):
260                return None
261
262            index = self._index
263
264            expressions = self._parse_expressions()
265            if len(expressions) == 1:
266                self._retreat(index)
267                self._match(TokenType.TABLE)
268                return self.expression(
269                    exp.Into, this=self._parse_table(schema=True), bulk_collect=bulk_collect
270                )
271
272            return self.expression(exp.Into, bulk_collect=bulk_collect, expressions=expressions)
273
274    class Generator(generator.Generator):
275        LOCKING_READS_SUPPORTED = True
276        JOIN_HINTS = False
277        TABLE_HINTS = False
278        DATA_TYPE_SPECIFIERS_ALLOWED = True
279        ALTER_TABLE_INCLUDE_COLUMN_KEYWORD = False
280        LIMIT_FETCH = "FETCH"
281        TABLESAMPLE_KEYWORDS = "SAMPLE"
282        LAST_DAY_SUPPORTS_DATE_PART = False
283        SUPPORTS_SELECT_INTO = True
284        TZ_TO_WITH_TIME_ZONE = True
285        QUERY_HINT_SEP = " "
286
287        TYPE_MAPPING = {
288            **generator.Generator.TYPE_MAPPING,
289            exp.DataType.Type.TINYINT: "NUMBER",
290            exp.DataType.Type.SMALLINT: "NUMBER",
291            exp.DataType.Type.INT: "NUMBER",
292            exp.DataType.Type.BIGINT: "NUMBER",
293            exp.DataType.Type.DECIMAL: "NUMBER",
294            exp.DataType.Type.DOUBLE: "DOUBLE PRECISION",
295            exp.DataType.Type.VARCHAR: "VARCHAR2",
296            exp.DataType.Type.NVARCHAR: "NVARCHAR2",
297            exp.DataType.Type.NCHAR: "NCHAR",
298            exp.DataType.Type.TEXT: "CLOB",
299            exp.DataType.Type.TIMETZ: "TIME",
300            exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
301            exp.DataType.Type.BINARY: "BLOB",
302            exp.DataType.Type.VARBINARY: "BLOB",
303            exp.DataType.Type.ROWVERSION: "BLOB",
304        }
305
306        TRANSFORMS = {
307            **generator.Generator.TRANSFORMS,
308            exp.DateStrToDate: lambda self, e: self.func(
309                "TO_DATE", e.this, exp.Literal.string("YYYY-MM-DD")
310            ),
311            exp.DateTrunc: lambda self, e: self.func("TRUNC", e.this, e.unit),
312            exp.Group: transforms.preprocess([transforms.unalias_group]),
313            exp.ILike: no_ilike_sql,
314            exp.Mod: rename_func("MOD"),
315            exp.Select: transforms.preprocess(
316                [
317                    transforms.eliminate_distinct_on,
318                    transforms.eliminate_qualify,
319                ]
320            ),
321            exp.StrToTime: lambda self, e: self.func("TO_TIMESTAMP", e.this, self.format_time(e)),
322            exp.StrToDate: lambda self, e: self.func("TO_DATE", e.this, self.format_time(e)),
323            exp.Subquery: lambda self, e: self.subquery_sql(e, sep=" "),
324            exp.Substring: rename_func("SUBSTR"),
325            exp.Table: lambda self, e: self.table_sql(e, sep=" "),
326            exp.TableSample: lambda self, e: self.tablesample_sql(e),
327            exp.TemporaryProperty: lambda _, e: f"{e.name or 'GLOBAL'} TEMPORARY",
328            exp.TimeToStr: lambda self, e: self.func("TO_CHAR", e.this, self.format_time(e)),
329            exp.ToChar: lambda self, e: self.function_fallback_sql(e),
330            exp.ToNumber: to_number_with_nls_param,
331            exp.Trim: _trim_sql,
332            exp.UnixToTime: lambda self,
333            e: f"TO_DATE('1970-01-01', 'YYYY-MM-DD') + ({self.sql(e, 'this')} / 86400)",
334        }
335
336        PROPERTIES_LOCATION = {
337            **generator.Generator.PROPERTIES_LOCATION,
338            exp.VolatileProperty: exp.Properties.Location.UNSUPPORTED,
339        }
340
341        def currenttimestamp_sql(self, expression: exp.CurrentTimestamp) -> str:
342            if expression.args.get("sysdate"):
343                return "SYSDATE"
344
345            this = expression.this
346            return self.func("CURRENT_TIMESTAMP", this) if this else "CURRENT_TIMESTAMP"
347
348        def offset_sql(self, expression: exp.Offset) -> str:
349            return f"{super().offset_sql(expression)} ROWS"
350
351        def xmltable_sql(self, expression: exp.XMLTable) -> str:
352            this = self.sql(expression, "this")
353            passing = self.expressions(expression, key="passing")
354            passing = f"{self.sep()}PASSING{self.seg(passing)}" if passing else ""
355            columns = self.expressions(expression, key="columns")
356            columns = f"{self.sep()}COLUMNS{self.seg(columns)}" if columns else ""
357            by_ref = (
358                f"{self.sep()}RETURNING SEQUENCE BY REF" if expression.args.get("by_ref") else ""
359            )
360            return f"XMLTABLE({self.sep('')}{self.indent(this + passing + by_ref + columns)}{self.seg(')', sep='')}"
361
362        def add_column_sql(self, expression: exp.Alter) -> str:
363            actions = self.expressions(expression, key="actions", flat=True)
364            if len(expression.args.get("actions", [])) > 1:
365                return f"ADD ({actions})"
366            return f"ADD {actions}"
367
368        def queryoption_sql(self, expression: exp.QueryOption) -> str:
369            option = self.sql(expression, "this")
370            value = self.sql(expression, "expression")
371            value = f" CONSTRAINT {value}" if value else ""
372
373            return f"{option}{value}"
374
375        def coalesce_sql(self, expression: exp.Coalesce) -> str:
376            func_name = "NVL" if expression.args.get("is_nvl") else "COALESCE"
377            return rename_func(func_name)(self, expression)
378
379        def into_sql(self, expression: exp.Into) -> str:
380            into = "INTO" if not expression.args.get("bulk_collect") else "BULK COLLECT INTO"
381            if expression.this:
382                return f"{self.seg(into)} {self.sql(expression, 'this')}"
383
384            return f"{self.seg(into)} {self.expressions(expression)}"
385
386        def hint_sql(self, expression: exp.Hint) -> str:
387            expressions = []
388
389            for expression in expression.expressions:
390                if isinstance(expression, exp.Anonymous):
391                    formatted_args = self.format_args(*expression.expressions, sep=" ")
392                    expressions.append(f"{self.sql(expression, 'this')}({formatted_args})")
393                else:
394                    expressions.append(self.sql(expression))
395
396            return f" /*+ {self.expressions(sqls=expressions, sep=self.QUERY_HINT_SEP).strip()} */"

 83    class Tokenizer(tokens.Tokenizer):
 84        VAR_SINGLE_TOKENS = {"@", "$", "#"}
 85
 86        UNICODE_STRINGS = [
 87            (prefix + q, q)
 88            for q in t.cast(t.List[str], tokens.Tokenizer.QUOTES)
 89            for prefix in ("U", "u")
 90        ]
 91
 92        NESTED_COMMENTS = False
 93
 94        KEYWORDS = {
 95            **tokens.Tokenizer.KEYWORDS,
 96            "(+)": TokenType.JOIN_MARKER,
 97            "BINARY_DOUBLE": TokenType.DOUBLE,
 98            "BINARY_FLOAT": TokenType.FLOAT,
 99            "BULK COLLECT INTO": TokenType.BULK_COLLECT_INTO,
100            "COLUMNS": TokenType.COLUMN,
101            "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
102            "MINUS": TokenType.EXCEPT,
103            "NVARCHAR2": TokenType.NVARCHAR,
104            "ORDER SIBLINGS BY": TokenType.ORDER_SIBLINGS_BY,
105            "SAMPLE": TokenType.TABLE_SAMPLE,
106            "START": TokenType.BEGIN,
107            "TOP": TokenType.TOP,
108            "VARCHAR2": TokenType.VARCHAR,
109        }

111    class Parser(parser.Parser):
112        ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False
113        WINDOW_BEFORE_PAREN_TOKENS = {TokenType.OVER, TokenType.KEEP}
114        VALUES_FOLLOWED_BY_PAREN = False
115
116        FUNCTIONS = {
117            **parser.Parser.FUNCTIONS,
118            "NVL": lambda args: build_coalesce(args, is_nvl=True),
119            "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
120            "TO_CHAR": _build_timetostr_or_tochar,
121            "TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "oracle"),
122            "TO_DATE": build_formatted_time(exp.StrToDate, "oracle"),
123            "TRUNC": lambda args: exp.DateTrunc(
124                unit=seq_get(args, 1) or exp.Literal.string("DD"),
125                this=seq_get(args, 0),
126                unabbreviate=False,
127            ),
128        }
129
130        NO_PAREN_FUNCTION_PARSERS = {
131            **parser.Parser.NO_PAREN_FUNCTION_PARSERS,
132            "SYSDATE": lambda self: self.expression(exp.CurrentTimestamp, sysdate=True),
133        }
134
135        FUNCTION_PARSERS: t.Dict[str, t.Callable] = {
136            **parser.Parser.FUNCTION_PARSERS,
137            "JSON_ARRAY": lambda self: self._parse_json_array(
138                exp.JSONArray,
139                expressions=self._parse_csv(lambda: self._parse_format_json(self._parse_bitwise())),
140            ),
141            "JSON_ARRAYAGG": lambda self: self._parse_json_array(
142                exp.JSONArrayAgg,
143                this=self._parse_format_json(self._parse_bitwise()),
144                order=self._parse_order(),
145            ),
146            "XMLTABLE": lambda self: self._parse_xml_table(),
147            "JSON_EXISTS": lambda self: self._parse_json_exists(),
148        }
149
150        PROPERTY_PARSERS = {
151            **parser.Parser.PROPERTY_PARSERS,
152            "GLOBAL": lambda self: self._match_text_seq("TEMPORARY")
153            and self.expression(exp.TemporaryProperty, this="GLOBAL"),
154            "PRIVATE": lambda self: self._match_text_seq("TEMPORARY")
155            and self.expression(exp.TemporaryProperty, this="PRIVATE"),
156        }
157
158        QUERY_MODIFIER_PARSERS = {
159            **parser.Parser.QUERY_MODIFIER_PARSERS,
160            TokenType.ORDER_SIBLINGS_BY: lambda self: ("order", self._parse_order()),
161            TokenType.WITH: lambda self: ("options", [self._parse_query_restrictions()]),
162        }
163
164        TYPE_LITERAL_PARSERS = {
165            exp.DataType.Type.DATE: lambda self, this, _: self.expression(
166                exp.DateStrToDate, this=this
167            )
168        }
169
170        # SELECT UNIQUE .. is old-style Oracle syntax for SELECT DISTINCT ..
171        # Reference: https://stackoverflow.com/a/336455
172        DISTINCT_TOKENS = {TokenType.DISTINCT, TokenType.UNIQUE}
173
174        QUERY_RESTRICTIONS: OPTIONS_TYPE = {
175            "WITH": (
176                ("READ", "ONLY"),
177                ("CHECK", "OPTION"),
178            ),
179        }
180
181        def _parse_xml_table(self) -> exp.XMLTable:
182            this = self._parse_string()
183
184            passing = None
185            columns = None
186
187            if self._match_text_seq("PASSING"):
188                # The BY VALUE keywords are optional and are provided for semantic clarity
189                self._match_text_seq("BY", "VALUE")
190                passing = self._parse_csv(self._parse_column)
191
192            by_ref = self._match_text_seq("RETURNING", "SEQUENCE", "BY", "REF")
193
194            if self._match_text_seq("COLUMNS"):
195                columns = self._parse_csv(self._parse_field_def)
196
197            return self.expression(
198                exp.XMLTable, this=this, passing=passing, columns=columns, by_ref=by_ref
199            )
200
201        def _parse_json_array(self, expr_type: t.Type[E], **kwargs) -> E:
202            return self.expression(
203                expr_type,
204                null_handling=self._parse_on_handling("NULL", "NULL", "ABSENT"),
205                return_type=self._match_text_seq("RETURNING") and self._parse_type(),
206                strict=self._match_text_seq("STRICT"),
207                **kwargs,
208            )
209
210        def _parse_hint_function_call(self) -> t.Optional[exp.Expression]:
211            if not self._curr or not self._next or self._next.token_type != TokenType.L_PAREN:
212                return None
213
214            this = self._curr.text
215
216            self._advance(2)
217            args = self._parse_hint_args()
218            this = self.expression(exp.Anonymous, this=this, expressions=args)
219            self._match_r_paren(this)
220            return this
221
222        def _parse_hint_args(self):
223            args = []
224            result = self._parse_var()
225
226            while result:
227                args.append(result)
228                result = self._parse_var()
229
230            return args
231
232        def _parse_query_restrictions(self) -> t.Optional[exp.Expression]:
233            kind = self._parse_var_from_options(self.QUERY_RESTRICTIONS, raise_unmatched=False)
234
235            if not kind:
236                return None
237
238            return self.expression(
239                exp.QueryOption,
240                this=kind,
241                expression=self._match(TokenType.CONSTRAINT) and self._parse_field(),
242            )
243
244        def _parse_json_exists(self) -> exp.JSONExists:
245            this = self._parse_format_json(self._parse_bitwise())
246            self._match(TokenType.COMMA)
247            return self.expression(
248                exp.JSONExists,
249                this=this,
250                path=self.dialect.to_json_path(self._parse_bitwise()),
251                passing=self._match_text_seq("PASSING")
252                and self._parse_csv(lambda: self._parse_alias(self._parse_bitwise())),
253                on_condition=self._parse_on_condition(),
254            )
255
256        def _parse_into(self) -> t.Optional[exp.Into]:
257            # https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/SELECT-INTO-statement.html
258            bulk_collect = self._match(TokenType.BULK_COLLECT_INTO)
259            if not bulk_collect and not self._match(TokenType.INTO):
260                return None
261
262            index = self._index
263
264            expressions = self._parse_expressions()
265            if len(expressions) == 1:
266                self._retreat(index)
267                self._match(TokenType.TABLE)
268                return self.expression(
269                    exp.Into, this=self._parse_table(schema=True), bulk_collect=bulk_collect
270                )
271
272            return self.expression(exp.Into, bulk_collect=bulk_collect, expressions=expressions)