Reference schema objects

When you reference an object in an SQL statement, OceanBase Database considers the context of the SQL statement, finds the object in the namespace, and then performs the specified operation on the object. If the named object cannot be found in the namespace, an error is returned.

Reference objects in your schema

This topic describes how OceanBase Database parses object references in SQL statements.

Execute the following SQL statement to add a row to the departments table:

INSERT INTO departments
VALUES (280, 'ENTERTAINMENT_CLERK', 206, 1700);

Based on the context of the SQL statement, departments may be any of the following objects:

• A table in your schema

• A view in your schema

• A synonym for a table or view

• A public synonym

OceanBase Database first tries to parse the referenced object in the namespace of your schema, and then considers namespaces that are outside of your schema. In this example, OceanBase Database parses the referenced object by performing the following steps:

1. OceanBase Database first tries to locate the object in the namespace of your schema. If the object is a proprietary synonym, OceanBase Database finds the object represented by the synonym. The object can belong to your schema, another schema, or another database. The object can be another synonym. In this case, OceanBase Database will locate the object that the synonym represents.

2. If the object is found in the namespace, OceanBase Database tries to execute the SQL statement on the object. In this example, OceanBase Database tries to add a row of data to departments. If the data type of the object is not the type required by the SQL statement, OceanBase Database returns an error. In this example, departments must be a table, a view, or a proprietary synonym that can be parsed to a table or a view. If departments is a sequence, OceanBase Database returns an error.

3. If the object is not found in any namespaces, OceanBase Database searches for it in namespaces that contain public synonyms. If the object is found in a namespace that contains public synonyms, OceanBase Database tries to execute the SQL statement on the object. If the data type of the object is not the type required by the SQL statement, OceanBase Database returns an error. In this example, if departments is a public synonym representing a sequence, OceanBase Database returns an error.

If the public synonym contains any dependent tables or user-defined types, in the same schema as that of the dependent object, you cannot create an object with the same name as that of the synonym.

If a synonym does not have any dependent tables or user-defined types, in the same schema as that of the dependent object, you can create an object with the same name as the synonym. OceanBase Database invalidates all dependent objects and revalidates them the next time you access them.

Reference objects in another schema

If the object to be referenced belongs to another schema, you must add the corresponding schema name before the object name. The syntax is as follows:

schema.object

The following example drops the employees table from the hr schema:

DROP TABLE hr.employees;