sqlglot.dialects.oracle

 17class Oracle(Dialect):
 18    ALIAS_POST_TABLESAMPLE = True
 19    LOCKING_READS_SUPPORTED = True
 20    TABLESAMPLE_SIZE_IS_PERCENT = True
 21    NULL_ORDERING = "nulls_are_large"
 22    ON_CONDITION_EMPTY_BEFORE_ERROR = False
 23    ALTER_TABLE_ADD_REQUIRED_FOR_EACH_COLUMN = False
 24    DISABLES_ALIAS_REF_EXPANSION = True
 25    INTERSECT_BINDS_TIGHTER_THAN_UNION_AND_EXCEPT = False
 26
 27    # See section 8: https://docs.oracle.com/cd/A97630_01/server.920/a96540/sql_elements9a.htm
 28    NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE
 29
 30    # https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212
 31    # https://docs.python.org/3/library/datetime.html#strftime-and-strptime-format-codes
 32    TIME_MAPPING = {
 33        "D": "%u",  # Day of week (1-7)
 34        "DAY": "%A",  # name of day
 35        "DD": "%d",  # day of month (1-31)
 36        "DDD": "%j",  # day of year (1-366)
 37        "DY": "%a",  # abbreviated name of day
 38        "HH": "%I",  # Hour of day (1-12)
 39        "HH12": "%I",  # alias for HH
 40        "HH24": "%H",  # Hour of day (0-23)
 41        "IW": "%V",  # Calendar week of year (1-52 or 1-53), as defined by the ISO 8601 standard
 42        "IYYY": "%G",  # 4-digit year of the ISO 8601 week-numbering calendar
 43        "MI": "%M",  # Minute (0-59)
 44        "MM": "%m",  # Month (01-12; January = 01)
 45        "MON": "%b",  # Abbreviated name of month
 46        "MONTH": "%B",  # Name of month
 47        "SS": "%S",  # Second (0-59)
 48        "WW": "%W",  # Week of year (1-53)
 49        "YY": "%y",  # 15
 50        "YYYY": "%Y",  # 2015
 51        "FF6": "%f",  # only 6 digits are supported in python formats
 52    }
 53
 54    PSEUDOCOLUMNS = {"ROWNUM", "ROWID", "OBJECT_ID", "OBJECT_VALUE", "LEVEL"}
 55
 56    def can_quote(self, identifier: exp.Identifier, identify: str | bool = "safe") -> bool:
 57        # Disable quoting for pseudocolumns as it may break queries e.g
 58        # `WHERE "ROWNUM" = ...` does not work but `WHERE ROWNUM = ...` does
 59        return (
 60            identifier.quoted or not isinstance(identifier.parent, exp.Pseudocolumn)
 61        ) and super().can_quote(identifier, identify=identify)
 62
 63    class Tokenizer(tokens.Tokenizer):
 64        VAR_SINGLE_TOKENS = {"@", "$", "#"}
 65
 66        UNICODE_STRINGS = [
 67            (prefix + q, q)
 68            for q in t.cast(list[str], tokens.Tokenizer.QUOTES)
 69            for prefix in ("U", "u")
 70        ]
 71
 72        # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html
 73        # Alternative quoting, e.g. q'[it's]': the delimiters are part of the quote, so the
 74        # text in between is taken as is, without escapes
 75        RAW_STRINGS = [
 76            (f"{prefix}'{start}", f"{end}'")
 77            for prefix in ("q", "Q")
 78            for start, end in (
 79                ("[", "]"),
 80                ("{", "}"),
 81                ("(", ")"),
 82                ("<", ">"),
 83                *(
 84                    (c, c)
 85                    for c in string.ascii_letters + string.digits + string.punctuation
 86                    if c not in "[{(<"
 87                ),
 88            )
 89        ]
 90        NATIONAL_RAW_STRINGS = [
 91            (prefix + start, end)
 92            for start, end in t.cast(list[tuple[str, str]], RAW_STRINGS)
 93            for prefix in ("n", "N")
 94        ]
 95        STRING_ESCAPES_ALLOWED_IN_RAW_STRINGS = False
 96
 97        NESTED_COMMENTS = False
 98
 99        KEYWORDS = {
100            **tokens.Tokenizer.KEYWORDS,
101            "(+)": TokenType.JOIN_MARKER,
102            # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comparison-Conditions.html
103            "^=": TokenType.NEQ,
104            "BINARY_DOUBLE": TokenType.DOUBLE,
105            "BINARY_FLOAT": TokenType.FLOAT,
106            "BULK COLLECT INTO": TokenType.BULK_COLLECT_INTO,
107            "COLUMNS": TokenType.COLUMN,
108            "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
109            "MINUS": TokenType.EXCEPT,
110            "NVARCHAR2": TokenType.NVARCHAR,
111            "ORDER SIBLINGS BY": TokenType.ORDER_SIBLINGS_BY,
112            "SAMPLE": TokenType.TABLE_SAMPLE,
113            "START": TokenType.BEGIN,
114            "SUBPARTITION": TokenType.PARTITION,
115            "TOP": TokenType.TOP,
116            "VARCHAR2": TokenType.VARCHAR,
117            "SYSTIMESTAMP": TokenType.SYSTIMESTAMP,
118        }
119
120    Parser = OracleParser
121
122    Generator = OracleGenerator

 63    class Tokenizer(tokens.Tokenizer):
 64        VAR_SINGLE_TOKENS = {"@", "$", "#"}
 65
 66        UNICODE_STRINGS = [
 67            (prefix + q, q)
 68            for q in t.cast(list[str], tokens.Tokenizer.QUOTES)
 69            for prefix in ("U", "u")
 70        ]
 71
 72        # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Literals.html
 73        # Alternative quoting, e.g. q'[it's]': the delimiters are part of the quote, so the
 74        # text in between is taken as is, without escapes
 75        RAW_STRINGS = [
 76            (f"{prefix}'{start}", f"{end}'")
 77            for prefix in ("q", "Q")
 78            for start, end in (
 79                ("[", "]"),
 80                ("{", "}"),
 81                ("(", ")"),
 82                ("<", ">"),
 83                *(
 84                    (c, c)
 85                    for c in string.ascii_letters + string.digits + string.punctuation
 86                    if c not in "[{(<"
 87                ),
 88            )
 89        ]
 90        NATIONAL_RAW_STRINGS = [
 91            (prefix + start, end)
 92            for start, end in t.cast(list[tuple[str, str]], RAW_STRINGS)
 93            for prefix in ("n", "N")
 94        ]
 95        STRING_ESCAPES_ALLOWED_IN_RAW_STRINGS = False
 96
 97        NESTED_COMMENTS = False
 98
 99        KEYWORDS = {
100            **tokens.Tokenizer.KEYWORDS,
101            "(+)": TokenType.JOIN_MARKER,
102            # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Comparison-Conditions.html
103            "^=": TokenType.NEQ,
104            "BINARY_DOUBLE": TokenType.DOUBLE,
105            "BINARY_FLOAT": TokenType.FLOAT,
106            "BULK COLLECT INTO": TokenType.BULK_COLLECT_INTO,
107            "COLUMNS": TokenType.COLUMN,
108            "MATCH_RECOGNIZE": TokenType.MATCH_RECOGNIZE,
109            "MINUS": TokenType.EXCEPT,
110            "NVARCHAR2": TokenType.NVARCHAR,
111            "ORDER SIBLINGS BY": TokenType.ORDER_SIBLINGS_BY,
112            "SAMPLE": TokenType.TABLE_SAMPLE,
113            "START": TokenType.BEGIN,
114            "SUBPARTITION": TokenType.PARTITION,
115            "TOP": TokenType.TOP,
116            "VARCHAR2": TokenType.VARCHAR,
117            "SYSTIMESTAMP": TokenType.SYSTIMESTAMP,
118        }