Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 5 of 11
element_list::=
To recompile the specification and/or body, or to change the specification of an object type by adding new object member subprogram specifications.
You cannot change the existing properties (attributes, member subprograms, map or order functions) of an object type, but you can add new member subprogram specifications.
The object type must be in your own schema and you must have CREATE TYPE
or CREATE ANY TYPE
system privilege, or you must have ALTER ANY TYPE
system privileges.
schema |
is the schema that contains the type. If you omit schema, Oracle assumes the type is in your current schema. |
|
type |
is the name of an object type, a nested table type, or a rowid type. |
|
|
compiles the object type specification and body. This is the default if neither
If recompiling the type results in compilation errors, then Oracle returns an error and the type remains invalid. You can see the associated compiler error messages with the SQL*Plus command |
|
|
|
compiles only the object type specification. |
|
|
compiles only the object type body. |
|
instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger. |
|
|
adds new member subprogram specifications. This clause is valid only for object types, not for nested table or varray types. |
|
invoker_rights_clause |
specifies whether the member functions and procedures of the object type execute with the privileges and in the schema of the user who owns the object type or with the privileges and in the schema of This clause also determines how Oracle resolves external names in queries, DML operations, and dynamic SQL statements in the member functions and procedures of the type. Restriction: You can specify this clause only for an object type, not for a nested table or varray type. See Also: PL/SQL User's Guide and Reference. |
|
|
|
specifies that the member functions and procedures of the object type execute with the privileges of
This clause also specifies that external names in queries, DML operations, and dynamic SQL statements resolve in the schema of |
|
|
Note: You must specify this clause to maintain invoker-rights status for the type if you created it with this status. Otherise the status will revert to definer rights. |
|
|
specifies that the member functions and procedures of the object type execute with the privileges of the owner of the schema in which the functions and procedures reside, and that external names resolve in the schema where the member functions and procedures reside. This is the default. |
attribute |
is an object attribute name. Attributes are data items with a name and a type specifier that form the structure of the object. |
|
|
specifies a function or procedure subprogram associated with the object type which is referenced as an attribute. For a description of the difference between member and static methods, and for examples, see "CREATE TYPE". For information about overloading subprogram names within a package, see the PL/SQL User's Guide and Reference. You must specify a corresponding method body in the object type body for each procedure or function specification. See "CREATE TYPE BODY". |
|
|
procedure_spec |
is the specification of a procedure subprogram. |
|
function_spec |
is the specification of a function subprogram. |
pragma_clause |
is a complier directive that denies member functions read/write access to database tables, packaged variables, or both, and thereby helps to avoid side effects. See Also: Oracle8i Application Developer's Guide - Fundamentals. |
|
|
method |
is the name of the |
|
|
specifies that the pragma should be applied to all methods in the type for which a pragma has not been explicitly specified. |
|
|
specifies the constraint writes no database state (does not modify database tables). |
|
|
specifies the constraint writes no package state (does not modify packaged variables). |
|
|
specifies the constraint reads no database state (does not query database tables). |
|
|
specifies the constraint reads no package state (does not reference package variables). |
|
|
specifies that the restrictions listed in the pragma are not actually to be enforced, but are simply trusted to be true. |
MAP|ORDER MEMBER function_spec |
||
|
|
specifies a member function ( |
|
|
If the argument to the map method is null, the map method returns null and the method is not invoked. |
|
|
An object specification can contain only one map method, which must be a function. The result type must be a predefined SQL scalar type, and the map function can have no arguments other than the implicit SELF argument. |
|
|
Note: If type_name will be referenced in queries involving sorts (through |
|
|
specifies a member function ( |
|
|
If either argument to the order method is null, the order method returns null and the method is not invoked.
When instances of the same object type definition are compared in an |
|
|
An object specification can contain only one |
|
You can declare either a |
|
|
If you do not declare either method, you can compare object instances only for equality or inequality. Instances of the same type definition are equal only if each pair of their corresponding attributes is equal. No comparison method needs to be specified to determine the equality of two object types. See Also: "Object Values" for more information about object value comparisons. |
In the following example, member function QTR
is added to the type definition of DATA_T
.
CREATE TYPE data_t AS OBJECT ( year NUMBER, MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER ); CREATE TYPE BODY data_t IS MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS BEGIN RETURN (year + invent); END; END; ALTER TYPE data_t REPLACE AS OBJECT ( year NUMBER, MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER, MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR ); CREATE OR REPLACE TYPE BODY data_t IS MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS MEMBER FUNCTION qtr(der_qtr DATE) RETURN CHAR IS BEGIN RETURN (year + invent); END; BEGIN RETURN 'FIRST'; END; END;
The following example creates and then recompiles type LOAN_T
:
CREATE TYPE loan_t AS OBJECT ( loan_num NUMBER, interest_rate FLOAT, amount FLOAT, start_date DATE, end_date DATE ); ALTER TYPE loan_t COMPILE;
The following example compiles the type body of LINK2
.
CREATE TYPE link1 AS OBJECT (a NUMBER); CREATE TYPE link2 AS OBJECT (a NUMBER, b link1, MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER); CREATE TYPE BODY link2 AS MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER IS t13 link1; BEGIN t13 := link1(13); dbms_output.put_line(t13.a); RETURN 5; END; END; CREATE TYPE link3 AS OBJECT (a link2); CREATE TYPE link4 AS OBJECT (a link3); CREATE TYPE link5 AS OBJECT (a link4); ALTER TYPE link2 COMPILE BODY;
The following example compiles the type specification of LINK2
.
CREATE TYPE link1 AS OBJECT (a NUMBER); CREATE TYPE link2 AS OBJECT (a NUMBER, b link1, MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER); CREATE TYPE BODY link2 AS MEMBER FUNCTION p(c1 NUMBER) RETURN NUMBER IS t14 link1; BEGIN t14 := link1(14); dbms_output.put_line(t14.a); RETURN 5; END; END; CREATE TYPE link3 AS OBJECT (a link2); CREATE TYPE link4 AS OBJECT (a link3); CREATE TYPE link5 AS OBJECT (a link4); ALTER TYPE link2 COMPILE SPECIFICATION;
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|