Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Basic Elements of Oracle SQL, 10 of 10
This section tells you how to refer to schema objects and their parts in the context of a SQL statement. This section shows you:
The following diagram shows the general syntax for referring to an object or a part:
where:
object |
is the name of the object. |
schema |
is the schema containing the object. The schema qualifier allows you to refer to an object in a schema other than your own. You must be granted privileges to refer to objects in other schemas. If you omit schema, Oracle assumes that you are referring to an object in your own schema. |
|
Only schema objects can be qualified with schema. Schema objects are shown with list item 9. Nonschema objects, also shown with list item 9, cannot be qualified with schema because they are not schema objects. (An exception is public synonyms, which can optionally be qualified with "PUBLIC". The quotation marks are required.) |
part |
is a part of the object. This identifier allows you to refer to a part of a schema object, such as a column or a partition of a table. Not all types of objects have parts. |
dblink |
applies only when you are using Oracle's distributed functionality. This is the name of the database containing the object. The dblink qualifier lets you refer to an object in a database other than your local database. If you omit dblink, Oracle assumes that you are referring to an object in your local database. Not all SQL statements allow you to access objects on remote databases. |
You can include spaces around the periods separating the components of the reference to the object, but it is conventional to omit them.
When you refer to an object in a SQL statement, Oracle considers the context of the SQL statement and locates the object in the appropriate namespace. After locating the object, Oracle performs the statement's operation on the object. If the named object cannot be found in the appropriate namespace, Oracle returns an error.
The following example illustrates how Oracle resolves references to objects within SQL statements. Consider this statement that adds a row of data to a table identified by the name DEPT:
INSERT INTO dept VALUES (50, 'SUPPORT', 'PARIS');
Based on the context of the statement, Oracle determines that DEPT can be:
Oracle always attempts to resolve an object reference within the namespaces in your own schema before considering namespaces outside your schema. In this example, Oracle attempts to resolve the name DEPT as follows:
To refer to objects in schemas other than your own, prefix the object name with the schema name:
schema.object
For example, this statement drops the EMP table in the schema SCOTT:
DROP TABLE scott.emp
To refer to objects in databases other than your local database, follow the object name with the name of the database link to that database. A database link is a schema object that causes Oracle to connect to a remote database to access an object there. This section tells you:
You create a database link with the CREATE
DATABASE
LINK
statement described in Chapter 7, "SQL Statements". The statement allows you to specify this information about the database link:
Oracle stores this information in the data dictionary.
When you create a database link, you must specify its name. Database link names are different from names of other types of objects. They can be as long as 128 bytes and can contain periods (.) and the "at" sign (@).
The name that you give to a database link must correspond to the name of the database to which the database link refers and the location of that database in the hierarchy of database names. The following syntax diagram shows the form of the name of a database link:
dblink::=
where:
The combination database.domain is sometimes called the "service name".
Oracle uses the username and password to connect to the remote database. The username and password for a database link are optional.
The database connect string is the specification used by Net8 to access the remote database. For information on writing database connect strings, see the Net8 documentation for your specific network protocol. The database string for a database link is optional.
Database links are available only if you are using Oracle's distributed functionality. When you issue a SQL statement that contains a database link, you can specify the database link name in one of these forms:
Oracle performs these tasks before connecting to the remote database:
GLOBAL_NAME
data dictionary view.)
GLOBAL_NAMES
parameter is TRUE
, Oracle verifies that the database.domain portion of the database link name matches the complete global name of the remote database. If this condition is true, Oracle proceeds with the connection, using the username and password chosen in Step 2. If not, Oracle returns an error.
You can disable the requirement that the database.domain portion of the database link name must match the complete global name of the remote database by setting to FALSE the initialization parameter GLOBAL_NAMES
or the GLOBAL_NAMES
parameter of the ALTER
SYSTEM
or ALTER
SESSION
statement.
To reference object type attributes or methods in a SQL statement, you must fully qualify the reference with a table alias. Consider the following example:
CREATE TYPE person AS OBJECT (ssno VARCHAR(20), name VARCHAR (10)); CREATE TABLE emptab (pinfo person);
In a SQL statement, reference to the SSNO attribute must be fully qualified using a table alias, as illustrated below:
SELECT e.pinfo.ssno FROM emptab e; UPDATE emptab e SET e.pinfo.ssno = '510129980' WHERE e.pinfo.name = 'Mike';
To reference an object type's member method that does not accept arguments, you must provide "empty" parentheses. For example, assume that AGE is a method in the person type that does not take arguments. In order to call this method in a SQL statement, you must provide empty parentheses as shows in this example:
SELECT e.pinfo.age() FROM emptab e WHERE e.pinfo.name = 'Mike';
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|