Oracle® Database Application Developer's Guide - Object-Relational Features 10g Release 2 (10.2) Part Number B14260-01 |
|
|
View PDF |
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
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 RESOURCE
role includes the CREATE
TYPE
system privilege. The DBA role includes all of these privileges.
Two schema object privileges apply to object types:
EXECUTE
on an object type enables you to use the type to:
Define a table.
Define a column in a relational table.
Declare a variable or parameter of the named type.
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 or subview under the type or 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:
Create types or tables that use types created by other users.
Grant use of your new types or tables to other users.
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 CREATE
SESSION
and RESOURCE
roles: USER1
, USER2
, and USER3
.
USER1
performs the following DDL in the USER1
schema:
CONNECT user1/user1 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:
CONNECT user2/user2 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 -- incorrect statement;
USER3
can successfully perform the following actions:
CONNECT user3/user3 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:
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 and queries in Example 6-1:
Example 6-1 SELECT Privileges on Type Access
CREATE TYPE emp_type as object ( eno NUMBER, ename VARCHAR2(36)); / CREATE TABLE emp OF emp_type; GRANT SELECT on emp TO user1; 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 described in "Example: Privileges on Object Types", USER3
can perform the following queries:
SELECT t.col1.attr2 from user2.tab2 t; 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:
Pinning an object in the object cache using its REF
value causes Oracle to check SELECT
privilege on the object table containing the object and EXECUTE
privilege on the object type.
See Also:
Oracle Call Interface Programmer's Guide for tips and techniques for using OCI program effectively with objectsModifying an existing object or flushing an object from the object cache, causes Oracle to check UPDATE
privilege on the destination object table. Flushing a new object causes Oracle to check INSERT
privilege on the destination object table.
Deleting an object causes Oracle to check DELETE
privilege on the destination table.
Invoking a method causes Oracle to check 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. In a diagram that uses arrows to show the dependency relationships among a set of types, connections among mutually dependent types form a loop.
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 types show in Example 6-2.
Example 6-2 Creating Dependent Object 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.
Notice that the code in Example 6-2 creates the type department
twice. The first statement:
CREATE TYPE department;
is an optional, incomplete declaration of department
that serves as a placeholder for the REF
attribute of employee
to point to. The declaration is incomplete in that it omits the AS
OBJECT
phrase and lists no attributes or methods. These are specified later in the full declaration that completes the type. In the meantime, department
is created as an incomplete object type. This enables the compilation of employee
to proceed without errors.
To complete an incomplete type, you execute a CREATE
TYPE
statement that specifies the attributes and methods of the type, as shown at the end of the example. Complete an incomplete type after all the types that it refers to are created.
If you do not create incomplete types as placeholders, types that refer to the missing types still compile, but the compilation proceeds with errors.
For example, if department
did not exist at all, Oracle would create it as an incomplete type and compile employee
with errors. Then employee
would be recompiled the next time that some operation attempts to access it. This time, if all the types it depends on are created and its dependencies are satisfied, it will compile without errors.
Incomplete types also enable you to create types that contain REF
attributes to a subtype that 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 ensure that subtypes are not left behind after a supertype is dropped, all subtypes must be dropped first: a supertype cannot be dropped until all its subtypes are dropped.
When all the types that an incomplete type refers to have been created, there is no longer any need for the incomplete type to remain incomplete, and you should complete the declaration of the type. Completing the type recompiles it and enables the system to release various locks.
You must complete an incomplete object type as an object type: you cannot complete an object type as a collection type (a nested table type or an array type). The only alternative to completing a type declaration is to drop the type.
You must also complete any incomplete types that Oracle creates for you because you did not explicitly create them yourself. The example in the preceding section explicitly creates department
as an incomplete type. If department
were not explicitly created as an incomplete type, Oracle would create it as one so that the employee
type can compile (with errors). You must complete the declaration of department
as an object type whether you or Oracle declared it as an incomplete type.
If a type was created with compilation errors, and you attempt an operation on it, such as creating tables or inserting rows, you may receive an error. You need to recompile type typename
before attempting the operation. To manually recompile a type, execute an ALTER
TYPE
typename
COMPILE
statement. After you have successfully compiled the type, attempt the operation again.
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 part_time_student_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 part_time_student_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 in the following example fails because part_time_student_typ
has a dependent table (persons
). But if persons
contains no instances of part_time_student_typ
(and no other dependent table or column does, either), the VALIDATE
keyword causes the second DROP
TYPE
statement to succeed:
-- Following generates an error due to presence of Persons table DROP TYPE part_time_student_typ -- incorrect statement; -- Following succeeds if there are no stored instances of part_time_student_typ DROP TYPE part_time_student_typ VALIDATE;
Note:
Oracle recommends that you always use theVALIDATE
option while dropping subtypes.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.
See "Type Evolution" for information about how to alter a type.
Just as you can create synonyms for tables, views, and various other schema objects, you can also define synonyms for object types.
Synonyms for types have the same advantages as synonyms for other kinds of schema objects: they provide a location-independent way to reference the underlying schema object. An application that uses public type synonyms can be deployed without alteration in any schema of a database without having to qualify a type name with the name of the schema in which the type was defined.
See Also:
Oracle Database Administrator's Guide for more information on synonyms in generalYou create a type synonym with a CREATE
SYNONYM
statement. For example, these statements create a type typ1
and then create a synonym for it:
-- For the synonym examples, CREATE SYNONYM and CREATE PUBLIC SYNONYM -- are granted to USER1 CREATE TYPE typ1 AS OBJECT (x number); / CREATE SYNONYM syn1 FOR typ1;
Synonyms can be created for collection types, too. The following example creates a synonym for a nested table type:
CREATE TYPE typ2 AS TABLE OF NUMBER; / CREATE SYNONYM syn2 FOR typ2;
You create a public synonym by using the PUBLIC
keyword:
CREATE TYPE shape AS OBJECT ( name VARCHAR2(10) );
/
CREATE PUBLIC SYNONYM pub_shape FOR shape;
The REPLACE
option enables you to have the synonym point to a different underlying type. For example, the following statement causes syn1
to point to type typ2
instead of the type it formerly pointed to:
CREATE OR REPLACE SYNONYM syn1 FOR typ2;
You can use a type synonym anywhere that you can refer to a type. For instance, you can use a type synonym in a DDL statement to name the type of a table column or type attribute. In Example 6-3, synonym syn1
is used to specify the type of an attribute in type typ3
:
Example 6-3 Using a Type Synonym in a Create Statement
CREATE TYPE typ1 AS OBJECT (x number); / CREATE SYNONYM syn1 FOR typ1; CREATE TYPE typ3 AS OBJECT ( a syn1 ); /
The next example shows a type synonym syn1
used to call the constructor of the object type typ1
, for which syn1
is a synonym. The statement returns an object instance of typ1
:
SELECT syn1(0) FROM dual;
In the following example, syn2
is a synonym for a nested table type. The example shows the synonym used in place of the actual type name in a CAST
expression:
SELECT CAST(MULTISET(SELECT eno FROM USER3.EMP) AS syn2) FROM dual;
Type synonyms can be used in the following kinds of statements:
DML statements: SELECT
, INSERT
, UPDATE
, DELETE
, FLASHBACK
TABLE
, EXPLAIN
PLAN
, and LOCK
TABLE
DDL statements: AUDIT
, NOAUDIT
, GRANT
, REVOKE
, and COMMENT
If a type or table has been created using type synonyms, the DESCRIBE
command will show the synonyms in place of the types they represent. Similarly, catalog views, such as USER_TYPE_ATTRS
, that show type names will show the associated type synonym names in their place.
You can query the catalog view USER_SYNONYMS
to find out the underlying type of a type synonym.
A type that directly or indirectly references a synonym in its type declaration is a dependent of that synonym. Thus, in the following example, type typ3
is a dependent type of synonym syn1
.
CREATE TYPE typ3 AS OBJECT ( a syn1 ); /
Other kinds of schema objects that reference synonyms in their DDL statements also become dependents of those synonyms. An object that depends on a type synonym depends on both the synonym and on the synonym's underlying type.
A synonym's dependency relationships affect your ability to drop or rename the synonym. Dependent schema objects are also affected by some operations on synonyms. The following sections describe these various ramifications.
You can replace a synonym only if it has no dependent tables or valid user defined types. Replacing a synonym is equivalent to dropping it and then re-creating a new synonym with the same name.
You drop a synonym with the DROP
SYNONYM
statement as shown in Example 6-4.
You cannot drop a type synonym if it has table or valid object types as dependents unless you use the FORCE
option. The FORCE
option causes any columns that directly or indirectly depend on the synonym to be marked unused, just as if the actual types of the columns were dropped. (A column indirectly depends on a synonym if, for instance, the synonym is used to specify the type of an attribute of the declared type of the column.)
Any dependent schema objects of a dropped synonym are invalidated. They can be revalidated by creating a local object of the same name as the dropped synonym or by creating a new public synonym with same name.
Dropping the underlying base type of a type synonym has the same effect on dependent objects as dropping the synonym.
You can rename a type synonym with the RENAME
statement. Renaming a synonym is equivalent to dropping it and then re-creating it with a new name. You cannot rename a type synonym if it has dependent tables or valid object types. The following example fails because synonym syn1
has a dependent object type:
RENAME syn1 TO syn3 -- invalid statement;
You cannot create a local schema object that has the same name as a public synonym if the public synonym has a dependent table or valid object type in the local schema where you want to create the new schema object. Nor can you create a local schema object that has the same name as a private synonym in the same schema.
For instance, in the following example, table shape_tab
is a dependent table of public synonym pub_shape
because the table has a column that uses the synonym in its type definition. Consequently, the attempt to create a table that has the same name as public synonym pub_shape
, in the same schema as the dependent table, fails:
-- Following uses public synonym pub_shape CREATE TABLE shape_tab ( c1 pub_shape ); -- Following is not allowed CREATE TABLE pub_shape ( c1 NUMBER ) -- invalid statement;
When tuning objects, the following items need to be addressed:
How objects and object views consume CPU and memory resources during runtime
How to monitor memory and CPU resources during runtime
How to manage large numbers of objects
Some of the key performance factors are the following:
DBMS_STATS
package to collect statistics
tkprof
to profile execution of SQL commands
EXPLAIN
PLAN
to generate the query plans
See Also:
Oracle Database Performance Tuning Guide for details on measuring and tuning the performance of your applicationThis section describes several Oracle tools that provide support for Oracle objects.
JDeveloper
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:
Oracle Business Components for Java
Web Application Development
Java Client Application Development
Java in the Database
Component-Based Development with JavaBeans
Simplified Database Access
Visual Integrated Development Environment
Java Language Support
JDeveloper runs on Windows platforms. It provides a standard GUI based Java development environment that is well integrated with Oracle Application Server and Database.
Business Components for Java (BC4J)
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:
Author and test business logic in components which integrate with relational databases
Reuse business logic through multiple SQL-based views of data
Access and update the views from servlets, JavaServer Pages (JSPs), and thin-Java Swing clients
Customize application functionality in layers without requiring modification of the delivered application
JPublisher
JPublisher is a utility, written entirely in Java, that generates Java classes to represent the following user-defined database entities in your Java program:
Database object types
Database reference (REF
) types
Database collection types (varrays or nested tables)
PL/SQL packages
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.
See Also:
Oracle Database JPublisher User's GuideThis section describes several Oracle utilities that provide support for Oracle objects.
Import/Export of Object Types
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.
Types 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.
Object View Hierarchies View definitions for all views belonging to a view hierarchy are exported
SQL*Loader
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 multilevel 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 datafiles.
SQL*Loader provides two approaches to loading data:
Conventional path loading, which uses the SQL
INSERT
statement and a bind array buffer to load data into database tables
Direct path loading, which uses the Direct Path Load API to write data blocks directly to the database on behalf of the SQL*Loader client.
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.
See Also:
Oracle Database Utilities for instructions on how to use SQL*Loader