Appearance

Rules for Naming Database Objects According to Oracle 19c ​

In an SQL statement, the name of an object is represented by an identifier, which can be used with or without double quotes ' " ' (quoted and unquoted identifiers). If the name is enclosed in double quotes when creating an object, the double quotes must always be used when referring to this object.

Both quoted and unquoted identifiers can be used to name a database object.

Doesn't quite work in MKW:

Objects can be created and exist both with and without quotes simultaneously. Apparently, names are set to lowercase before creation. However, in the metadata window, the names appear as they were created. Uppercase objects either cannot be queried or, as with tables, a union is shown.

sql
---------------------------TABLES
CREATE TABLE mkw_doku.my_table (fc INTEGER, sc STRING);
INSERT INTO mkw_doku.my_table VALUES(1,'first');
INSERT INTO mkw_doku.my_table VALUES(2,'second');

CREATE TABLE mkw_doku."MY_TABLE" (fc INTEGER, sc STRING);
INSERT INTO mkw_doku."MY_TABLE" VALUES(3,'third');
INSERT INTO mkw_doku."MY_TABLE" VALUES(4,'fourth');

-- This query returns a UNION of my_table and "MY_TABLE"
SELECT * FROM mkw_doku.my_table;

--- This table cannot be created if my_table and MY_TABLE exist.
CREATE TABLE mkw_doku."my_table" (fc INTEGER, sc STRING);

-- These queries drop the tables
drop table mkw_doku.my_table; -- this drops my_table
drop table mkw_doku."my_table"; -- this drops "MY_TABLE"

---------------------------VIEWS
CREATE TABLE mkw_doku.my_table (col1 INTEGER, col2 STRING); 
INSERT INTO mkw_doku.my_table VALUES(1,'first'); 

CREATE OR REPLACE VIEW mkw_doku.my_view1 AS 
    SELECT * FROM mkw_doku.my_table;
CREATE OR REPLACE VIEW mkw_doku."my_view1" AS 
    SELECT col2 FROM mkw_doku.my_table;
CREATE OR REPLACE VIEW mkw_doku."my_view2" AS 
    SELECT col2 FROM mkw_doku.my_table;

Both queries (with and without quotes) to my_view1 and "my_view1" return results from my_view1:

sql
select * from mkw_doku."my_view1";
select * from mkw_doku.my_view1;
select * from mkw_doku."my_view2";
select * from mkw_doku.my_view2;

Both queries to my_view2 and "my_view2" return an error (since my_view2 does not exist).

The DROP VIEW command works correctly.

sql
DROP VIEW mkw_doku."my_view1";
DROP VIEW mkw_doku.my_view1;
DROP VIEW mkw_doku."my_view2";
DROP VIEW mkw_doku.my_view2;

The following rules apply to both quoted and unquoted identifiers unless otherwise specified:

Danger: In MKW: Object length seems to be 200 characters, excluding folder_name. Correct?

RuntimeError: Failed to create table 'mkw_doku.t112345678t212345678t312345678t412345678t512345678t612345678t712345678t812345678t912345678t101234567t111234567t121234567t131234567t141234567t151234567t161234567t171234567t181234567t191234567t201234567t112345678': insert into "D_META_TABLES" ("CREATED_AT", "CUSTOMER_ID", "FOLDER_ID", "LABEL", "NAME", "PROJECT_ID", "UPDATED_AT") values (:1, :2, :3, :4, :5, :6, :7) returning "ID" into :8 - value too large for column "MS"."D_META_TABLES"."NAME" (actual: 210, maximum: 200)at mkw_doku.test_abfragen (1:1)

I. The length of identifier names must be between 1 and 128 bytes. If an identifier contains multiple parts separated by dots, each attribute can be up to 128 bytes long. Each dot and the surrounding quotes count as one byte. For example: folder_name."table_name"."col_name". The table name can be 128 bytes, and the column name can be 128 bytes long. Each of the quotes and dots is a single character, so the total length of the identifier in this example can be up to 261 bytes (2*128 + 4 (") + 1 (.)).

What are the correct values in MKW?

If COMPATIBLE is set to a value below 12.2, names must be between 1 and 30 bytes long, with the following exceptions:

If an identifier contains multiple parts separated by dots, each attribute can be up to 30 bytes long. Each dot and the surrounding quotes count as one byte. For example: folder_name."table_name"."col_name". The table name can be 30 bytes, and the column name can be 30 bytes long. Each of the quotes and dots is a single character, so the total length of the identifier in this example can be up to 65 bytes.

II. Unquoted identifiers must not be reserved SQL keywords. Quoted identifiers can be reserved words, although this is not recommended. The reserved word ROWID is an exception to this rule. The uppercase word ROWID cannot be used as an unquoted or quoted identifier for a column name. However, the word can be used as a quoted identifier that is not a column name, and it can be used with one or more lowercase letters (e.g., Rowid or rowid) as any quoted identifier, including a column name.

III. The SQL language contains other words with special meanings. These words include data types, function names, and keywords (the uppercase words in SQL statements like DIMENSION, SEGMENT, ALLOCATE, DISABLE, etc.). These words are not reserved. However, the database uses them internally in specific ways. Therefore, SQL statements can be harder to read and lead to unpredictable results if these words are used as names for objects and object parts.

What are they in MKW?

In particular, words that begin with SYS_ or ORA_ should not be used as schema object names, and the names of built-in SQL functions should not be used for schema objects or user-defined functions.

IV. Multibyte characters can be included in passwords.

V. Unquoted identifiers must begin with an alphanumeric character from the database character set. Quoted identifiers can begin with any character.

VI. Unquoted identifiers can only contain alphanumeric characters from the database character set, as well as the underscore (_), the dollar sign ($), and the pound sign (#). It is strongly discouraged to use $ and # in unquoted identifiers. Quoted identifiers can contain any characters and punctuation marks, including spaces. However, neither quoted nor unquoted identifiers can contain double quotes or the null character (\0).

Is this relevant for MKW?

VII. Within a namespace, no two objects can have the same name.

The following schema objects share a namespace:

Packages

Private synonyms

Sequences

Stand-alone procedures

Stand-alone stored functions

Tables

User-defined operators

User-defined types

Views

Each of the following schema objects has its own namespace:

Clusters

Constraints

Database triggers

Dimensions

Indexes

Materialized views (When creating a materialized view, the database creates an internal table with the same name. This table shares the same namespace as other tables in the schema. Therefore, a schema cannot contain both a table and a materialized view with the same name.)

Private database links

Since tables and sequences share the same namespace, a table and a sequence in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.

Each schema in the database has its own namespaces for the objects contained within it. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.

Each of the following non-schema objects also has its own namespace:

Editions

Parameter files (PFILEs) and server parameter files (SPFILEs)

Profiles

Public database links

Public synonyms

Tablespaces

User roles

Since the objects in these namespaces are not contained within schemas, these namespaces extend across the entire database.

VII. Unquoted identifiers are not case-sensitive. They are interpreted as uppercase. Quoted identifiers are case-sensitive. For example, the following identifiers can exist simultaneously: "PLANETS", "pLANETS", "planets", "Planets". The following objects are considered "equal" when declared or queried: planets, PLANETS, "PLANETS".

VIII. When the database stores or compares identifiers in uppercase, the uppercase form of each character in the identifiers is determined by applying the uppercase rules of the database character set.

The uppercase rules of the database character set can produce results that are considered incorrect in a particular natural language. For example, the lowercase sharp S ("ß"), used in German, has no uppercase form according to the uppercase rules of the database character set. It is not changed when an identifier is converted to uppercase, while the expected uppercase form in German is the sequence of two uppercase S ("SS"). Similarly, the uppercase form of the lowercase i according to the uppercase rules of the database character set is the uppercase I. However, the expected uppercase form in Turkish and Azerbaijani is the uppercase I with a dot above.

The uppercase rules of the database character set ensure that identifiers are interpreted the same way in any linguistic configuration of a session. If an identifier is to appear correctly in a particular natural language, it can be quoted to retain the lowercase form, or the linguistically correct uppercase form can be used whenever the identifier is used.

IX. Columns in the same table or view must have unique names.

© 2025 MetaKraftwerk GmbH. All Rights Reserved.