O'Reilly logo

Learning Oracle PL/SQL by Steven Feuerstein, Bill Pribyl

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

9.7. Still More PL/SQL Features

This is the part of the book where you get to see a wealth of PL/SQL capabilities in only a few pages. The examples here are not full-fledged tutorials; they are only meant to let you in on more of the features you may decide to use as you progress as a PL/SQL programmer.

These are the features I'll run through in this section:

  • Object types

  • Large objects

  • XML

  • The code profiler

  • (Pipelined) table functions

  • More built-in packages and types

9.7.1. Object Types

Earlier in this chapter, I showed how you can create your own user-defined types that will hold collections. Oracle Version 8.0 and later offer another way to add custom datatypes to your application. In PL/SQL, an object type is a database construct that lets you define a data structure and a set of related operations upon it. The object type can have multiple attributes, in the same way that a record can have multiple fields. Those "operations" are really just a name for procedures or functions you define in the object type; the procedures and functions are also known as methods.

An example of an object type designed to hold information about library patrons is:

CREATE TYPE lib_patron_t AS OBJECT ( id NUMBER, username VARCHAR2(60), encrypted_password RAW(16), account_creation_date DATE, email_address VARCHAR2(2000), STATIC FUNCTION make (username IN VARCHAR2, plaintext_password IN VARCHAR2, email_address IN VARCHAR2, requestor_id IN NUMBER) RETURN lib_patron_t, MEMBER PROCEDURE save, MEMBER PROCEDURE ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required