what is the difference between oracle "create or replace type" and "type type_name is..." syntax

Question

hi guys, i'm a pl/sql newbie. now i have a question about oracle type. i saw there are two types of type :

CREATE OR REPLACE TYPE "TYPE_NAME1" AS OBJECT
(
    temp_trans_id           number(10),
    trans_id                number(10),
    resion_id               number(10)
)

or

type new_type_name is record(
    column1 number,
    column2 varchar2(50)
);
variable_name new_type_name;

what's the difference? great thanks.

Answer 1

A record type is a type that can be used like a record. It has a set of typed fields, but that's about it. An Object Type is rather different. It also has a set of fields, but it can also contain executable methods that will act in the context of an instance of your object (and yes, you can also have static methods). It is similar to an Object in Java. Some (but certainly not all) differences from other object-oriented systems I've seen:

• No interfaces
• No private methods

In your example, TYPE_NAME1 and new_type_name seem very similar because for the Object Type (TYPE_NAME1), you have not taken advantage of anything specific to Object Types. See the Oracle page on Object Types for more info.

Answer 2

In addition to the previous answer, there are significant differences in the scope of the declaration.

When you declare a type in PL/SQL, such as the record type in your example, it is only usable from PL/SQL. If it is declared locally to a procedure or function, then it can only be used within that subprogram; if it is declared in a package body it can only be used within that package; if it is declared in a package header it can be used by any PL/SQL code that has execute access to the package. In no way can it be referenced in SQL statements, even ones embedded in the PL/SQL code.

When you create an object type, or other schema-level type definitions such as nested tables, it is usable within both SQL and PL/SQL. As a very simple example, you can base a table definition on the object definition:

SQL> CREATE OR REPLACE TYPE "TYPE_NAME1" AS OBJECT
  2  (
  3      temp_trans_id           number(10),
  4      trans_id                number(10),
  5      resion_id               number(10)
  6  )
  7  /

Type created.

SQL> create table type_name1_tab of type_name1;

Table created.

SQL> desc type_name1_tab
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TEMP_TRANS_ID                                      NUMBER(10)
 TRANS_ID                                           NUMBER(10)
 RESION_ID                                          NUMBER(10)

See here for documentation on the CREATE TYPE statement and references to further documentation on the various uses of object types.