Reference schema objects

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

Reference objects in your schema

This topic provides an example to illustrate how OceanBase Database resolves references to objects in an SQL statement.

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

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

Based on the context of the SQL statement, departments could be:

• A table in your schema

• A view in your schema

• A private synonym for a table or view

• A public synonym

OceanBase Database first tries to resolve the referenced object in the namespace of your schema and then considers namespaces outside of your schema. In this example, the resolution process of OceanBase Database is as follows:

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

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 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 private synonym that can be resolved to a table or a view. If departments is a sequence, OceanBase Database returns an error.

3. If the object has not been found in any namespace, OceanBase Database searches the namespace that contains public synonyms. If the object is found in the namespace that contains public synonyms, OceanBase Database tries to execute the SQL statement on the object. If the 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 that represents a sequence, OceanBase Database returns an error.

If a public synonym depends on any tables or user-defined types, you cannot create an object with the same name as the synonym in the same schema as the dependent objects.

Conversely, if a synonym does not depend on any tables or user-defined types, you can create an object with the same name as the synonym in the same schema as the dependent objects. OceanBase Database invalidates all dependent objects and verifies them again when they are accessed next time.

Reference objects in other schemas

If you want to reference an object in another schema, you must add the name of the schema to the object name. The syntax is as follows:

schema.object

The following example shows how to drop the employees table in the hr schema:

DROP TABLE hr.employees;