Oracle9i Application Developer's Guide - Object-Relational Features Release 1 (9.0.1) Part Number A88878-01 |
|
This chapter explains how Oracle objects work in combination with the rest of the database, and how to perform DML and DDL operations on them. It contains the following major sections:
Privileges for object types exist at the system level and the schema object level.
Oracle defines the following system privileges for object types:
CREATE TYPE
enables you to create object types in your own schema
CREATE ANY TYPE
enables you to create object types in any schema
ALTER ANY TYPE
enables you to alter object types in any schema
DROP ANY TYPE
enables you to drop named types in any schema
EXECUTE ANY TYPE
enables you to use and reference named types in any schema
UNDER ANY TYPE
enables you to create subtypes under any non-final object types
UNDER ANY VIEW
enables you to create subviews under any object view
The CONNECT
and RESOURCE
roles include the CREATE TYPE
system privilege. The DBA role includes all of the above privileges.
Two schema object privileges apply to object types:
EXECUTE
on an object type enables you to use the type to:
EXECUTE
lets you invoke the type's methods, including the constructor.
Method execution and the associated permissions are the same as for stored PL/SQL procedures.
UNDER
enables you to create a subtype/subview under the type/view on which the privilege is granted
The UNDER
privilege on a subtype or subview can be granted only if the grantor has the UNDER
privilege on the direct supertype or superview WITH GRANT OPTION
.
The phrase WITH HIERARCHY OPTION
grants a specified object privilege on all subobjects of the object. This option is meaningful only with the SELECT
object privilege granted on an object view in an object view hierarchy. In this case, the privilege applies to all subviews of the view on which the privilege is granted.
In addition to the permissions detailed in the previous sections, you need specific privileges to:
You must have the EXECUTE ANY TYPE
system privilege, or you must have the EXECUTE
object privilege for any type you use in defining a new type or table. You must have received these privileges explicitly, not through roles.
If you intend to grant access to your new type or table to other users, you must have either the required EXECUTE
object privileges with the GRANT
option or the EXECUTE ANY TYPE
system privilege with the option WITH ADMIN OPTION
. You must have received these privileges explicitly, not through roles.
Assume that three users exist with the CONNECT
and RESOURCE
roles: USER1
, USER2
, and USER3
.
USER1 performs the following DDL in the USER1 schema:
CREATE TYPE type1 AS OBJECT ( attr1 NUMBER ); CREATE TYPE type2 AS OBJECT ( attr2 NUMBER ); GRANT EXECUTE ON type1 TO user2; GRANT EXECUTE ON type2 TO user2 WITH GRANT OPTION;
USER2 performs the following DDL in the USER2 schema:
CREATE TABLE tab1 OF user1.type1; CREATE TYPE type3 AS OBJECT ( attr3 user1.type2 ); CREATE TABLE tab2 (col1 user1.type2 );
The following statements succeed because USER2
has EXECUTE
on USER1
's TYPE2
with the GRANT
option:
GRANT EXECUTE ON type3 TO user3; GRANT SELECT on tab2 TO user3;
However, the following grant fails because USER2
does not have EXECUTE
on USER1.TYPE1
with the GRANT
option:
GRANT SELECT ON tab1 TO user3;
USER3 can successfully perform the following actions:
CREATE TYPE type4 AS OBJECT (attr4 user2.type3); CREATE TABLE tab3 OF type4;
While object types only make use of EXECUTE
privilege, object tables use all the same privileges as relational tables:
SELECT
lets you access an object and its attributes from the table.
UPDATE
lets you modify attributes of objects in the table.
INSERT
lets you add new objects to the table.
DELETE
lets you delete objects from the table.
Similar table and column privileges regulate the use of table columns of object types.
Selecting columns of an object table does not require privileges on the type of the object table. Selecting the entire row object, however, does.
Consider the following schema:
CREATE TYPE emp_type as object ( eno NUMBER, ename CHAR(31), eaddr addr_t ); CREATE TABLE emp OF emp_type;
and the following two queries:
SELECT VALUE(e) FROM emp e; SELECT eno, ename FROM emp;
For either query, Oracle checks the user's SELECT
privilege for the emp
table. For the first query, the user needs to obtain the emp_type
type information to interpret the data. When the query accesses the emp_type
type, Oracle checks the user's EXECUTE
privilege.
Execution of the second query, however, does not involve named types, so Oracle does not check type privileges.
Additionally, using the schema from the previous section, USER3
can perform the following queries:
SELECT tab1.col1.attr2 from user2.tab1 tab1; SELECT t.attr4.attr3.attr2 FROM tab3 t;
Note that in both selects by USER3
, USER3
does not have explicit privileges on the underlying types, but the statement succeeds because the type and table owners have the necessary privileges with the GRANT
option.
Oracle checks privileges on the following requests, and returns an error if the requestor does not have the privilege for the action:
SELECT
privilege on the object table containing the object and EXECUTE
privilege on the object type. (For more information about the OCI object cache, see "OCI Tips and Techniques for Objects".)
UPDATE
privilege on the destination object table. Flushing a new object causes Oracle to check INSERT
privilege on the destination object table.
DELETE
privilege on the destination table.
EXECUTE
privilege on the corresponding object type.
Oracle does not provide column level privileges for object tables.
Types can depend upon each other for their definitions. For example, you might want to define object types employee
and department
in such a way that one attribute of employee
is the department the employee belongs to and one attribute of department
is the employee who manages the department.
Types that depend on each other in this way, either directly or through intermediate types, are called mutually dependent. A diagram of mutually dependent types, with arrows representing the dependencies, always reveals a path of arrows starting and ending at one of the types.
To define such a circular dependency, you must use REF
s for at least one segment of the circle.
For example, you can define the following types:
CREATE TYPE department; CREATE TYPE employee AS OBJECT ( name VARCHAR2(30), dept REF department, supv REF employee ); CREATE TYPE emp_list AS TABLE OF employee; CREATE TYPE department AS OBJECT ( name VARCHAR2(30), mgr REF employee, staff emp_list );
This is a legal set of mutually dependent types and a legal sequence of SQL DDL statements. Oracle compiles it without errors. The first statement:
CREATE TYPE department;
is optional. It makes the compilation proceed without errors. It establishes department
as an incomplete object type. A REF
to an incomplete object type compiles without error, so the compilation of employee
proceeds.
When Oracle reaches the statement that completes the definition of department
, all of the components of department
have compiled successfully, so the compilation finishes without errors.
Without the optional declaration of department
as an incomplete type, employee
compiles with errors. Oracle then automatically adds employee
to its library of schema objects as an incomplete object type. This makes the declarations of emp_list
and department
compile without errors. When employee
is recompiled after emp_list
and department
are complete, employee
compiles without errors and becomes a complete object type.
Incomplete types also enable you to create types that contain REF
attributes to a subtype, which has not yet been created. To create such a supertype, first create an incomplete type of the subtype to be referenced. Create the complete subtype after you create the supertype.
A subtype is just a specialized version of its direct supertype and consequently has an explicit dependency on it. To avoid leaving behind subtypes that lack a supertype, a supertype cannot be dropped unless all its subtypes are dropped first.
Once you have declared an incomplete object type, you must complete it as an object type. You cannot, for example, declare it to be a table type or an array type. The only alternative is to drop the type.
This is also true if Oracle has made the type an incomplete object type for you--as it did when EMPLOYEE
failed to compile in the previous section.
A substitutable table or column of type T
is dependent not only on T
but on all subtypes of T
as well. This is because a hidden column is added to the table for each attribute added in a subtype of T
. The hidden columns are added even if the substitutable table or column contains no data of that subtype.
So, for example, a persons table of type Person_typ
is dependent not only on Person_typ
but also on the Person_typ
subtypes Student_typ
and PartTimeStudent_typ
.
If you attempt to drop a subtype that has a dependent type, table, or column, the DROP TYPE
statement returns an error and aborts. For example, trying to drop PartTimeStudent_typ
will raise an error because of the dependent persons
table.
If dependent tables or columns exist but contain no data of the type that you want to drop, you can use the VALIDATE
keyword to drop the type. The VALIDATE
keyword causes Oracle to check for actual stored instances of the specified type and to drop the type if none are found. Hidden columns associated with attributes unique to the type are removed as well.
For example, the first DROP TYPE
statement below will fail because PartTimeStudent_typ
has a dependent table (persons
). But if persons
contains no instances of PartTimeStudent_typ
(and no other dependent table or column does, either), the VALIDATE
keyword will cause the second DROP TYPE
statement to succeed:
DROP TYPE PartTimeStudent_typ; -- Error due to presence of Persons table DROP TYPE PartTimeStudent_typ VALIDATE; -- Succeeds if there are no stored -- instances of PartTimeStudent_typ
The DROP TYPE
statement also has a FORCE
option that causes the type to be dropped even though it may have dependent types or tables. The FORCE
option should be used only with great care, as any dependent types or tables that do exist are marked invalid and become inaccessible when the type is dropped. Data in a table that is marked invalid because a type it depends on has been dropped can never be accessed again. The only action that can be performed on such a table is to drop it.
This section describes several Oracle tools that provide support for Oracle objects.
JDeveloper is a full-featured, integrated development environment for creating multitier Java applications. It enables you to develop, debug, and deploy Java client applications, dynamic HTML applications, web and application server components and database stored procedures based on industry-standard models.
JDeveloper provides powerful features in the following areas:
JDeveloper runs on Windows NT. It provides a standard GUI based Java development environment that is well integrated with Oracle's Application Server and Database.
Supporting standard EJB and CORBA deployment architectures, Oracle Business Components for Java simplifies the development, delivery, and customization of Java business applications for the enterprise. Oracle Business Components for Java is an application component framework providing developers a set of reusable software building blocks that manage all the common facilities required to:
JPublisher is a utility, written entirely in Java, that generates Java classes to represent the following user-defined database entities in your Java program:
JPublisher enables you to specify and customize the mapping of database object types, reference types, and collection types (varrays or nested tables) to Java classes, in a strongly typed paradigm.
The Export and Import utilities move data into and out of Oracle databases. They also back up or archive data and aid migration to different releases of the Oracle RDBMS.
Export and Import support object types. Export writes object type definitions and all of the associated data to the dump file. Import then re-creates these items from the dump file.
The definition statements for derived types are exported. On an Import, a subtype may be created before the supertype definition has been imported. In this case, the subtype will be created with compilation errors, which may be ignored. The type will be revalidated after its supertype is created.
View definitions for all views belonging to a view hierarchy are exported
The SQL*Loader utility moves data from external files into tables in an Oracle database. The files may contain data consisting of basic scalar datatypes, such as INTEGER
, CHAR
, or DATE
, as well as complex user-defined datatypes such as row and column objects (including objects that have object, collection, or REF
attributes), collections, and LOBs. Currently, SQL*Loader supports single-level collections only: you cannot yet use SQL*Loader to load multi-level collections, that is, collections whose elements are, or contain, other collections.
SQL*Loader uses control files, which contain SQL*Loader Data Definition Language (DDL) statements, to describe the format, content, and location of the datafile(s).
SQL*Loader provides two approaches to loading data:
SQL INSERT
statement and a bind array buffer to load data into database tables
Direct path loading does not use a SQL interface and thus avoids the overhead of processing the associated SQL statements. Consequently, direct path loading tends to provide much better performance than conventional path loading.
Either approach can be used to load data of supported object and collection datatypes.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|