CREATE TYPE BODY Statement

The CREATE TYPE BODY defines or implements the member methods defined in the object type specification. You create object types with the CREATE TYPE and the CREATE TYPE BODY statements. The CREATE TYPE Statement specifies the name of the object type, its attributes, methods, and other properties. The CREATE TYPE BODY statement contains the code for the methods that implement the type.

For each method specified in an object type specification for which you did not specify the call_spec, you must specify a corresponding method body in the object type body.

Note:

If you create a SQLJ object type, then specify it as a Java class.

Prerequisites

Every member declaration in the CREATE TYPE specification for object types must have a corresponding construct in the CREATE TYPE or CREATE TYPE BODY statement.

To create or replace a type body in your own schema, you must have the CREATE TYPE or the CREATE ANY TYPE system privilege. To create an object type in another user's schema, you must have the CREATE ANY TYPE system privilege. To replace an object type in another user's schema, you must have the DROP ANY TYPE system privilege.

Syntax

create_type_body ::=

create_type_body
Description of the illustration create_type_body.gif

(subprog_decl_in_type ::=, map_order_func_declaration ::=)

subprog_decl_in_type ::=

subprog_decl_in_type
Description of the illustration subprog_decl_in_type.gif

proc_decl_in_type ::=

proc_decl_in_type
Description of the illustration proc_decl_in_type.gif

(declare_section ::=, body ::=, call_spec ::=)

func_decl_in_type ::=

func_decl_in_type
Description of the illustration func_decl_in_type.gif

(declare_section ::=, body ::=, call_spec ::=)

constructor_declaration ::=

constructor_declaration
Description of the illustration constructor_declaration.gif

map_order_func_declaration ::=

map_order_func_declaration
Description of the illustration map_order_func_declaration.gif

call_spec ::=

call_spec
Description of the illustration call_spec.gif

Java_declaration ::=

Java_declaration
Description of the illustration java_declaration.gif

C_declaration ::=

C_declaration
Description of the illustration c_declaration.gif

Keyword and Parameter Descriptions

OR REPLACE

Specify OR REPLACE to re-create the type body if it already exists. Use this clause to change the definition of an existing type body without first dropping it.

Users previously granted privileges on the re-created object type body can use and reference the object type body without being granted privileges again.

You can use this clause to add new member subprogram definitions to specifications added with the ALTER TYPE ... REPLACE statement.

schema

Specify the schema to contain the type body. If you omit schema, then the database creates the type body in your current schema.

type_name

Specify the name of an object type.

subprog_decl_in_type

Specify the type of function or procedure subprogram associated with the object type specification.

You must define a corresponding method name and optional parameter list in the object type specification for each procedure or function declaration. For functions, you also must specify a return type.

proc_decl_in_type, func_decl_in_type Declare a procedure or function subprogram.

constructor_declaration Declare a user-defined constructor subprogram. The RETURN clause of a constructor function must be RETURN SELF AS RESULT. This setting indicates that the most specific type of the value returned by the constructor function is the same as the most specific type of the SELF argument that was passed in to the constructor function.

See Also:

declare_section

Declares items that are local to the procedure or function.

body

Procedure or function statements.

call_spec Specify the call specification that maps a Java or C method name, parameter types, and return type to their SQL counterparts.

The Java_declaration string identifies the Java implementation of the method.

See Also:

map_order_func_declaration

You can declare either one MAP method or one ORDER method, regardless of how many MEMBER or STATIC methods you declare. If you declare either a MAP or ORDER method, then you can compare object instances in SQL.

If you do not declare either method, then 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.

MAP MEMBER Clause

Specify MAP MEMBER to declare or implement a MAP member function that returns the relative position of a given instance in the ordering of all instances of the object. A MAP method is called implicitly and specifies an ordering of object instances by mapping them to values of a predefined scalar type. PL/SQL uses the ordering to evaluate Boolean expressions and to perform comparisons.

If the argument to the MAP method is null, then the MAP method returns null and the method is not invoked.

An object type body can contain only one MAP method, which must be a function. The MAP function can have no arguments other than the implicit SELF argument.

ORDER MEMBER Clause

Specify ORDER MEMBER to specify an ORDER member function that takes an instance of an object as an explicit argument and the implicit SELF argument and returns either a negative integer, zero, or a positive integer, indicating that the implicit SELF argument is less than, equal to, or greater than the explicit argument, respectively.

If either argument to the ORDER method is null, then the ORDER method returns null and the method is not invoked.

When instances of the same object type definition are compared in an ORDER BY clause, the database invokes the ORDER MEMBER func_decl_in_type.

An object specification can contain only one ORDER method, which must be a function having the return type NUMBER.

func_decl_in_type  Declare a function subprogram. See CREATE PROCEDURE Statement and CREATE FUNCTION Statement for the full syntax with all possible clauses.

AS EXTERNAL  AS EXTERNAL is an alternative way of declaring a C method. This clause has been deprecated and is supported for backward compatibility only. Oracle recommends that you use the call_spec syntax with the C_declaration.

Examples

Several examples of creating type bodies appear in the Examples section of CREATE TYPE Statement. For an example of re-creating a type body, see Adding a Member Function: Example.

Related Topics