Explain user-defined object types in PL/SQL

How it works

CREATE TYPE ... AS OBJECT defines a schema-level, reusable structured type — attributes plus, optionally, member methods (functions/procedures operating on an instance) — usable as a column type, a PL/SQL variable type, or the element type of a collection (as pipelined table functions rely on). It's Oracle's object-relational feature for modeling a real-world entity as one named type instead of a loose set of separately declared fields.

CREATE OR REPLACE TYPE address_t AS OBJECT (
  street VARCHAR2(100),
  city   VARCHAR2(50),
  zip    VARCHAR2(10),
  MEMBER FUNCTION full_address RETURN VARCHAR2
);
/
CREATE OR REPLACE TYPE BODY address_t AS
  MEMBER FUNCTION full_address RETURN VARCHAR2 IS
  BEGIN
    RETURN street || ', ' || city || ' ' || zip;
  END;
END;
/

DECLARE
  v_addr address_t := address_t('1 Main St', 'Springfield', '00000');
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_addr.full_address());
END;
/

Related Questions