0134-0136

Previous Table of Contents Next

Page 134

Note that indexes are 1-based and that each VARRAY has two properties: count and size. The count property returns the number of non-null items, and size returns the maximum number of items the VARRAY can contain.

VARRAYs are useful in processing small collections in PL/SQL code. Restrictions limit their usefulness , though. As mentioned previously, array indexes cannot be referenced in DML or SQL statements. The types of VARRAYs that can be defined also are limited. A VARRAY type cannot be based on a type that also contains a VARRAY or any other aggregate type, such as a PL/SQL table, for example.

REF

A second built-in datatype introduced in Oracle8 is the REF. A REF is, in essence, a pointer to a user -defined object type. Suppose an object of type customer_type and a table to store the type customers are defined as the following:

 CREATE TYPE customer_type (ID         NUMBER(10),     LastName     VARCHAR2(20),     FirstName    VARCHAR2(20)); CREATE TABLE customers OF customer_type; 

Object types are discussed in greater detail in the section titled "Object Types." For now, the object type was defined to demonstrate that a customer REF can be stored in a table, as shown here:

 CREATE TABLE invoice (invoice_num  NUMBER(10) PRIMARY KEY,     invoice_date DATE,     custref     REF customer_type,     total_amt    NUMBER(10, 2),     status_code  NUMBER(1),     details         invoice_details); 

A select statement against the invoice table can be issued to retrieve a customer REF, but to access the actual customer data, the DEREF operator is used to return a customer object. The simple procedure that follows demonstrates these concepts:

 CREATE OR REPLACE PROCEDURE ShowCust(InvNum IN NUMBER) AS BEGIN DECLARE     cust    customer_type; BEGIN     SELECT DEREF(custref) INTO cust FROM invoice WHERE invoice_num = InvNum;     dbms_output.put_line(cust.ID);     dbms_output.put_line(cust.LastName);     dbms_output.put_line(cust.FirstName); END; END ShowCust;. 

Using REFs can provide performance improvements in PL/SQL programs, particularly when the REFs reference large objects and are used as parameters to functions and procedures.

Page 135

LOBs

Oracle8 introduces several new built-in types for dealing with large objects (LOBs), such as full documents, video images, digital audio, and so on. The four types of LOBs follow:

• BLOB: Unstructured binary data
• CLOB: Single-byte character data
• NCLOB: Fixed-width single-byte or multibyte character data that corresponds to the national character set defined for the database
• BFILE: Unstructured binary data stored outside the database in a file system

LOBs are not stored with other tables' data, and you can use a different tablespace and storage specification for each LOB column in a table definition. Oracle8, unlike its predecessors, enables you to define multiple LOB columns for a single table, as shown here:

 CREATE TABLE emp_data (empno     NUMBER(10) PRIMARY KEY,     picture BLOB,     bio     CLOB,     hr_file BFILE) TABLESPACE ts_emp_data LOB (picture) STORE AS (TABLESPACE ts_pics storage (initial 200MB next 200M) CHUNK 16K), LOB (bio) STORE AS (TABLESPACE ts_large_text storage (initial 2MB next 2M) CHUNK 2K); 

Note that BFILE data is not stored in Oracle data files, so no storage specification can be defined for this type. The CHUNK keyword is used to specify the amount of data to be read or written for piece-wise operations on the column.

The special EMPTY_BLOB(), EMPTY_CLOB(), and EMPTY_NCLOB() functions are provided to initialize internal lob types to empty, because LOB columns cannot be written to if they contain NULL values. BFILE data is stored externally, so columns of this type can safely be initialized to NULL. The BFILENAME() function is used to initialize a BFILE column to point to a specific file in the external file system. The following inserts into the emp_data table illustrate the use of these functions:

 INSERT INTO emp_data VALUES (1001, EMPTY_BLOB(), EMPTY_CLOB(), NULL); INSERT INTO emp_data VALUES (1002, EMPTY_BLOB(), EMPTY_CLOB(),              BFILENAME(`F:\hrfiles', `1002.doc')); 

Internal LOB types are accessed through locators and cannot be manipulated directly through SQL or DML. The DBMS_LOB package, which is discussed in the section titled "DBMS_LOB Package," contains routines for manipulating LOB columns.

Deferred Constraint Checking

Deferred constraint checking is new in Oracle8; it provides increased flexibility in managing referential integrity for complex transactions. Deferred constraint checking allows referential integrity to be broken by individual DML statements, assuming that the transaction as a whole

Page 136

will resolve any integrity problems caused by individual statements. This capability minimizes the importance of the order in which DML statements are executed for a particular transaction. Consider the emp and dept tables of the famous Scott schemathese come standard with Oracle as practice tables. The deptno column of emp has a foreign key constraint referencing deptno in the dept table. In previous versions of Oracle, the following transaction would fail on the very first statement:

 INSERT INTO emp VALUES(9001, `SMITH', `MANAGER', 7839, '15-AUG-90', 4000, null, 50); INSERT INTO dept VALUES(50, `IS', `PORTLAND'); 

In Oracle8, foreign key constraint checking can be deferred, allowing this transaction to complete successfully. Constraints are defined by using the keyword DEFERRABLE or NONDEFERRABLE (the default). Deferrable constraints are INITIALLY DEFERRED or INITIALLY IMMEDIATE, allowing the behavior of deferred constraints to be controlled at the session level. Constraints cannot be modified, so to redefine the behavior of the emp table foreign key constraint, the following commands are issued:

 ALTER TABLE emp DROP CONSTRAINT fk_deptno; ALTER TABLE emp ADD CONSTRAINT fk_deptno     FOREIGN KEY (deptno) REFERENCES dept(deptno) DEFERRABLE INITIALLY IMMEDIATE; 

The sample transaction then could be applied using the following statements:

 ALTER SESSION SET CONSTRAINTS=DEFERRED; INSERT INTO emp VALUES(9001, `SMITH', `MANAGER', 7839, '15-AUG-90', 4000, null, 50); INSERT INTO dept VALUES(50, `IS', `PORTLAND'); 

Note that the ALTER SESSION statement in the example sets the mode for all deferrable constraints. Keep in mind that deferred constraint checking changes the timing of Oracle Call Interface (OCI) errors and PL/SQL exceptions. Extending this example, the following transaction does not report any errors until a commit is issued:

 INSERT INTO emp VALUES(9002, `JONES', `P/A', 9001, '15-AUG-90', 4000, null, 60); INSERT INTO emp VALUES(9003, `DILBERT', `P/A', 9001, '15-AUG-90', 4000, null, 60); 

Deferred constraint checking can simplify the development of complex transactions with numerous dependencies and can increase transaction throughput in some cases. The disadvantage of this approach is that it allows processing to continue until a commit is issued. This method can waste processing time for long transactions that fail because of problems in early statements and can make it difficult to isolate the offending statement(s).

Object Types

Object types are the basis for the new object-relational features incorporated in Oracle8. An object type is an extension of the user-defined type, allowing methods to be encapsulated with data elements into a single logical unit. The definition of an object type serves as a template but does not allocate any storage. Objects are stored physically as rows or columns of a table. Consider the following simple type declaration:

Previous Table of Contents Next