11.9 Types

11.9.1 Boolean

You can declare a BOOLEAN PL/SQL variable type. There is no BOOLEAN database column type.

Booleans are easy to work with. Often the existence of a value logically relates to a Boolean variable. For the student's demo, a student can have several majors (e.g., Biology). One valid lookup value is: Undeclared. Suppose you consider Undeclared to be FALSE and any other major to be TRUE. Logically this equates to: having a major is TRUE. Having an undeclared major is false. Such a scenario can be common in application code.

It helps to have a package that maps Boolean values to a 1 and 0. For example, the following package is a general purpose package that transforms a Boolean to and from a zero or one.

 CREATE OR REPLACE PACKAGE bool IS     FUNCTION to_int (B BOOLEAN) RETURN NATURAL;     FUNCTION to_bool(N NATURAL) RETURN BOOLEAN; END bool; CREATE OR REPLACE PACKAGE BODY  bool IS     FUNCTION to_int (B BOOLEAN) RETURN NATURAL IS     BEGIN         IF B THEN RETURN 1; ELSE RETURN 0; END IF;     END to_int;     FUNCTION to_bool(N NATURAL) RETURN BOOLEAN IS     BEGIN         IF N=1 THEN RETURN TRUE; ELSE RETURN FALSE; END IF;     END to_bool; END bool; 

A demonstration of the preceding package is a query to get the first major from the MAJOR_LOOKUP table and saves a 1 or 0 using a DECODE statement. The NATURAL number flag is converted to a Boolean using the package.

 DECLARE     major            NATURAL;     a_declared_major BOOLEAN; BEGIN     SELECT DECODE (major_desc, 'Undeclared', 0, 1)       INTO major       FROM major_lookup WHERE ROWNUM = 1;     a_declared_major := bool.to_bool(major); END; 

11.9.2 Scalar Types

You should declare PL/SQL variable types from table column types whenever possible. This makes the code flexible. It is also a form of self-documenting code. The following procedure has one parameter. It is a variable-length string.

 PROCEDURE process(arg IN VARCHAR2); 

The next procedure is a small improvement and uses a more meaningful formal parameter name .

 PROCEDURE process(state IN VARCHAR2); 

This is best because it suggests that the parameter passed is to be a value with the dimension of the STATE_DESC column in the STATE_LOOKUP table.

 PROCEDURE process(state IN state_lookup.state_desc%TYPE); 

There is no impact on the PL/SQL code if the dimension of the STATE_DESC should change. If the column STATE_DESC in the table STATE_LOOKUP is altered , the PL/SQL code will be recompiled, but this is minor compared to editing many PL/SQL programs and changing the dimension of a VARCHAR2.

As mentioned, the use of %TYPE provides built-in documentation. The STATE_LOOKUP table has two columns , each are VARCHAR2. Using the %TYPE makes it clear which column value the procedure is expecting.

Using %TYPE also aids the process of impact analysis. Suppose you want to change the STATE_LOOKUP table. The USER_DEPENDENCIES view will show this procedure as being dependent on the STATE_LOOKUP table. Because the procedure merely references the column with a %TYPE attribute, the procedure is formally dependent on this table. Using %TYPE will enhance impact analysis any time you need to assess what code will be affected with a table change.

Some common PL/SQL predefined types include the following:

CHAR		A CHAR variable stores a constant-length string. An optional length denotes the number of bytes with a maximum of 32767 bytes. Subtypes are STRING and CHARACTER.
	variable CHAR - stores a single character. variable CHAR(10); -- stores 10 characters
		The Oracle error ORA 6502 is raised if you assign a string that exceeds the dimension of the CHAR variable. This error ORA 6502 is mapped to an exception VALUE_ERROR. Assigning a string less than the dimension right pads the variable with blanks. Keep in mind that you can right pad a VARCHAR2 variable with the RPAD function.
	DECLARE C CHAR(3); -- maximum of 3 characters X VARCHAR2(30); BEGIN C := 'ABCDE'; -- raises VALUE_ERROR C := 'A'; -- C is: A and 2 blanks X := RPAD('A', 3); -- X is: A and 2 blanks, -- also X is equal to C EXCEPTION WHEN VALUE_ERROR THEN dbms_output.put_line('Value error'); END;
VARCHAR2		A VARCHAR2 stores a variable length string. The maximum is 4000 bytes. Most data in a database is character data. Use %TYPE when declaring PL/SQL variables that are populated with database data. Otherwise use VARCHAR2. You can store larger character strings with the CLOB datatype. There are many string functions that support manipulation of VARCHAR2 types. See Section 11.13, "String Manipulation Functions."
NUMBER		Use NUMBER to store fixed or floating-point numbers . You specify the precision with the notation: NUMBER[(precision, scale)] Precision is the total number of decimal places. Scale is the number of those places to the right of the decimal point. A scale can be negative. This indicates significance of digits to the left of the decimal point. If you want to store 5-digit numbers and always store the number rounded to hundredths, the precision is 3 and the scale is “2.
		NUMBER(3,-2)	This has a format of 99900.
		Numbers are rounded to fit the precision. A value too large will raise a VALUE_ERROR exception.
		To declare a type test score that includes two decimal places to the right and with numbers that range from zero to 100:
		NUMBER(5,2)	This has a number format 999.99.
		NUMBER(3)	This stores any three-digit whole number.
		NUMBER(1,-2)	This stores 100, 200, up to 900.
		You can control rounding when inserting values to a NUMBER type. There are a variety of powerful functions: ROUND, TRUNC, CEIL, FLOOR. Refer to Section 11.15, "Numeric Functions."
DATE		A DATE type stores the DAY and TIME in a single variable. Refer to Section 11.17, "Date Functions," for DATE manipulation.

Use integer-based numbers when there is no fractional part. Using a NUMBER type to store basic integers requires additional space. NUMBER types use floating precision that is unnecessary for integer variables.

PLS_INTEGER	Use this to store whole numbers. The range is:
	“2**31 to 2**31
BINARY_INTEGER	This is another integer but performance is better with PLS_INTEGER types.
POSITIVE	Use this to store numbers 1 and greater. A VALUE_ERROR exception is raised if you assign a number less than 1.
NATURAL	Use this to store numbers zero and greater. A VALUE_ERROR exception is raised if you assign a number less than zero.

11.9.3 Records

Records are composite structures that allow multiple pieces of data to be passed as a single parameter. Rather than pass name, birth date, and age to a procedure, pass a single record with the components : name, birth date, and age.

You must first declare a record type definition; then declare an object of that type.

You declare the record type with the syntax:

 TYPE record_type_name IS RECORD(component_1 component_1_type [NOT NULL := expression], component_2 component_2_type [NOT NULL := expression], component_3 component_3_type [NOT NULL := expression]); 

Common scalar component types include DATE, NUMBER, and VARCHAR2. To declare a record type using these scalar types:

 TYPE record_type IS RECORD(name       VARCHAR2(30),     address    VARCHAR2(30),     birth_date DATE,     age        NUMBER(3)); 

A record component type can be derived from a column type. For example:

 TYPE record_type IS RECORD(name    students.student_name%TYPE,     major   major_lookup.major_desc%TYPE); 

You declare a record structure based on the record type. The record declaration comes after the type definition. The following declares a record type, then fetches into that record. The block ends with printing the components of the record.

 DECLARE     TYPE record_type IS RECORD(name     students.student_name%TYPE,     major    major_lookup.major_desc%TYPE);     student_rec record_type; BEGIN    SELECT student_name, major_desc      INTO student_rec      FROM students a, major_lookup b     WHERE student_id='A101' AND a.college_major=b.major;     dbms_output.put_line(student_rec.name);     dbms_output.put_line(student_rec.major); END; 

You address record components using a dot to reference the components. For example:

 student_rec.name := 'Ricky'; student_rec.major := 'Biology'; 

Records can be assigned to one another provided they are derived from the same record type definition.

You can preset default component values in the type definition. The following declares a record type and presets each component value. All record declarations using this type have each component initialized to the current day and zero, respectively.

 TYPE record_type IS RECORD(birth_date DATE      := TRUNC(SYSDATE),     age        NUMBER(3) := 0); 

The type definition initializes BIRTH_DATE using the built-in function SYSDATE. You can use any function, including your PL/SQL function from an existing package.

The ideal use of a record is to reduce parameters into and out of procedures. Consider a function that must return the name and college major of a student. The following illustrates a simple interface that evolves when records are used. The function STUDENT returns a single record based on a primary key STUDENT_ID.

 CREATE OR REPLACE PACKAGE students_pkg IS     TYPE student_type IS RECORD(name     students.student_name%TYPE,     major    major_lookup.major_desc%TYPE);     FUNCTION student(ID IN students.student_id%TYPE)         RETURN student_type; END students_pkg; CREATE OR REPLACE PACKAGE BODY students_pkg IS     FUNCTION student (ID IN students.student_id%TYPE)         RETURN student_type     IS         r student_type;     BEGIN         SELECT student_name, major_desc           INTO r           FROM students a, major_lookup b          WHERE student_id='A101' AND                a.college_major=b.major;         RETURN r;     END student; END students_pkg; 

The caller of the procedure can use the package function in an assignment statement. The program that calls the function can reference each component within an expression. The following block merely calls DBMS_OUTPUT to print the name and major of a particular college student ”simply by referencing the returned record component.

 BEGIN     dbms_output.put_line(students_pkg.student('A101').name);     dbms_output.put_line(students_pkg.student('A101').major); END; 

Records can be nested. A record component can be another record. A record component can be a record whose type is derived from a database table row type. For example, the following illustrates a record with two components. The first is a student ID; the second component is a record structure. The subcomponent names of STUDENT_ADVISOR are the column names of the PROFESSORS table. This could store a student ID and all relevant information about that student's academic advisor.

 TYPE record_type IS RECORD(student          students.student_id%TYPE,     student_advisor  professors%ROWTYPE); 

The following illustrates code that creates a type with a component from the STUDENTS_PKG. This new record has two components. The first is a student ID. The second component is derived from the type declaration in the package specification.

 DECLARE     TYPE my_student_type IS RECORD(ID         VARCHAR2(10),         student    students_pkg.student_type);     stud my_student_type; BEGIN     stud.ID := 'A101';     stud.student := students_pkg.student(stud.ID);     dbms_output.put_line(stud.student.name); END; 

A record component can be an index-by table. The following declares an index-by table to contain student test scores. The record type definition has two components: student ID and a structure for test scores. A record structure, STUDENT_TEST_SCORES, encapsulates the student and text scores into a single structure. Index-by tables are covered in the following section.

 TYPE table_type IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER; TYPE student_test_scores_type IS RECORD(student     students.student_id%TYPE,     test_scores table_type); student_test_scoure student_test_scores_type; 

11.9.4 %ROWTYPE

You can declare a record structure based on the columns of an existing table. You do not declare a type definition. Use %ROWTYPE when coding procedures and functions that perform DML on tables. A following procedure adds a student and has one record parameter.

 CREATE OR REPLACE PACKAGE students_pkg IS     PROCEDURE add_student(rec IN students%ROWTYPE); END students_pkg; 

Addressing components is the same as a record. The record component names are the table column names. The datatype of each component is the same type as the column in the table. The following references two components in the record.

 dbms_output.put_line(rec.student_id); dbms_output.put_line(rec.student_name); 

The STUDENTS table must exist before a reference to students %ROWTYPE will compile.

Oracle recognizes that the package is now dependent on the STUDENTS table. One benefit to using %ROWTYPE and %TYPE is the dependency information you retrieve from the USER_DEPENDENCIES view.

11.9.5 Index-By Tables

Index-by tables are unconstrained indexed liner structures, similar to arrays. They are originally sparse. You can populate table position 20 and position 30 using only the memory required for those two slots.

Index-by tables are constructed with the following syntax:

 TYPE  table_name  IS TABLE OF  component_type  [NOT NULL] INDEX BY BINARY_INTEGER; 

For example, the following declares a table type that can be used later to declare a table of DATE type variables.

 TYPE date_table_type IS TABLE OF DATE INDEX BY BINARY_INTEGER; 

You can constrain the elements to NOT NULL with the syntax:

 TYPE date_table_type IS TABLE OF DATE NOT NULL INDEX BY BINARY_INTEGER; 

Common scalar component types include DATE, NUMBER, and VARCHAR2. The following declares an index-by table. You reference table elements with parentheses enclosing an integer or any expression that evaluates to an integer.

 DECLARE     TYPE table_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;     tab table_type; BEGIN     tab(1) := 1000;     tab(4) := 200; END; 

A table element can be a record structure. The following table definition derives the table element from the STUDENTS_PKG shown in Section 11.9.3, "Records." For this code, each table element is a record structure based on the STUDENT_TYPE record of the package. A student record is fetched using the package and stored into the table at slot 101.

 DECLARE     TYPE table_type IS TABLE OF students_pkg.student_type     INDEX BY BINARY_INTEGER;     tab table_type;     student_id students.student_id%TYPE := 'A101';     location binary_integer; BEGIN     location := to_number(substr(student_id,2));     tab(location) := students_pkg.student(student_id);     dbms_output.put_line(tab(location).name);     dbms_output.put_line(tab(location).major);     -- NO_DATA_FOUND     dbms_output.put_line('d'tab(location+1).name);     dbms_output.put_line(tab(location).major); END; 

The exception NO_DATA_FOUND is raised if you read from a table location to which no data has been assigned. The NO_DATA_FOUND exception is a motivation for either packing data in sequence and keeping track of where the data is located or using the built-in table attributes. These attributes allow your code to sequence through sparse data beginning with the FIRST and ending with the LAST element. You avoid reading empty slots with the EXISTS attribute. The built-in attributes are:

COUNT	Returns the number of locations with data.
FIRST	Smallest integer location containing data.
LAST	Greatest integer location with data.
PRIOR	Returns the integer to the prior location containing data.
NEXT	Returns the integer of the next location containing data.
EXISTS	Guards against reading empty cells .
DELETE	Deletes an element and frees memory.

The following illustrates table attributes. If a table is empty, FIRST and LAST evaluate to NULL. PRIOR and NEXT can also return NULL. PRIOR evaluates to NULL if you pass an integer argument whose value is before the first table index. PRIOR evaluates to NULL if you pass an integer argument whose value comes after the last table index.

The following block loads two slots. The block includes DBMS_OUTPUT calls using NEXT and PRIOR. The value displayed is shown as a comment next to each PUT_LINE call.

 DECLARE     TYPE table_type IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;     tab table_type; BEGIN     tab(1) := 1000;     tab(4) := 200;     dbms_output.put_line(tab.COUNT);     FOR I in tab.FIRST..tab.LAST LOOP         IF tab.EXISTS(I) THEN             dbms_output.put_line(tab(I));         ELSE             dbms_output.put_line('no data in slot 'I);         END IF;     END LOOP;     dbms_output.put_line(tab.next(0));  -- 1     dbms_output.put_line(tab.prior(0)); -- null     dbms_output.put_line(tab.next(1));  -- 4     dbms_output.put_line(tab.next(2));  -- 4     dbms_output.put_line(tab.next(4));  -- null     dbms_output.put_line(tab.prior(5)); -- 4 END; 

11.9.6 Varrays and Nested Tables

Within PL/SQL, a VARRAY is a constrained array structure. Its size is constrained, whereas index-by tables are unbounded. A VARRAY is created with a maximum dimension.

You can declare index-by table types and record types in a package specification and declare procedures and functions to operate on objects of such types. This use of composite structures enhances the PL/SQL code. It means programs can pass a single record with nested components, including nested index-by tables, rather numerous parameters. You cannot store a record structure or an index-by composite structure as an object in the database. However, composite structure objects are supported. The following illustrates the use of varrays and nested tables.

The following declares a VARRAY of numbers and uses the constructor function to initialize values. First the type is defined. This is a VARRAY object type capable of storing up to four numbers. Then an object, MY_NUMBERS, of this type is declared. The maximum size is four, but less than four numbers can by assigned to the varray. COUNT is a built-in attribute that evaluates to the number of elements in the array. The varray indexing starts at 1 and is dense ”the array is populated in sequence.

 DECLARE     TYPE my_numbers_type IS VARRAY(4) OF NUMBER;     my_numbers my_numbers_type; BEGIN     my_numbers := my_numbers_type(1,2,3);     FOR I IN 1..my_numbers.COUNT LOOP         dbms_output.put_line(my_numbers(I));     END LOOP; END; 

Scientific measurements are seldom a single number. A scientist will find it convenient if a database table element can be an (X,Y) coordinate. Start with declaring a TYPE that represents an (X,Y) coordinate. The following is the DDL that creates such a type in the database. This is a new object you will find in USER_OBJECTS. The object_name is POINT_TYPE and the object_type is TYPE.

 CREATE OR REPLACE TYPE point_type AS OBJECT (x  NUMBER,     y  NUMBER); 

The POINT_TYPE provides a lot of flexibility. Prior to this we would likely create a record structure with (X,Y) components and then create an index-by table type with the record type as the element stored in the table. Creation of POINT_TYPE as an object in the database will lead to storing points as single POINT_TYPE entities in the database. We won't have to create a table with a column for X and a column for Y. Rather, we will create a table with a column of type POINT_TYPE. Furthermore, we will shortly store an array of points as a single entity.

The following PL/SQL block declares a type that is a VARRAY of elements where each element is a POINT_TYPE. This type is called POINTS_VARRAY_TYPE. The variable, POINTS, is an object of this type. This is similar to the preceding PL/SQL block except that the varray consists not of numbers but (X,Y) coordinates.

POINT_TYPE is now an object in the database. Because POINT_TYPE is now defined in the database, we can declare a VARRAY type of POINT_TYPE elements. We can also declare POINT_TYPE variables (in the following PL/SQL block) such as A_POINT and PT.

 DECLARE     TYPE   points_varray_type IS VARRAY(10) OF point_type;     points points_varray_type := points_varray_type();     a_point   point_type;     pt        point_type; BEGIN     a_point := point_type(3,4);     points := points_varray_type(point_type(1,2),                                  point_type(2,3), a_point,                                  point_type(5,4));     FOR I IN 1..points.COUNT LOOP         pt := points(I);         dbms_output.put_line('x='pt.x' y='pt.y);     END LOOP; END; 

We can create additional database objects using the existing POINT_TYPE declaration. The following creates two more object types. The first is a varray type. The second is a nested table type. Each of the following two DDL statements creates an object in the database. Each is dependent on the existing database object, POINT_TYPE.

 CREATE OR REPLACE TYPE points_varray_type                        AS                        VARRAY(10) OF point_type; CREATE OR REPLACE TYPE points_nested_table_type                        AS                        TABLE OF point_type; 

In review, we started with a point type, POINT_TYPE, and a PL/SQL-type definition that allows us to manipulate a VARRAY of points within a PL/SQL program. The varray and nested table types allow for database table creation to consist of columns that consist of point arrays.

The following table is used to store environmental measurement data. The varray and nested table are each used to illustrate the differences. The VARRAY column is stored as packed data within the table. Use a VARRAY for small amounts of data for which you know the maximum dimension. Use nested tables for larger data elements and unknown dimensions.

 CREATE TABLE environment_data (sample_ID             NUMBER(3),  points_varray         points_varray_type,  points_nested_table   points_nested_table_type) NESTED TABLE  points_nested_table STORE AS points_nested_tab; 

The ENVIRONMENT_DATA table can now store an array of points. An array of (X,Y) points can represent the collection of data for a single sampling. The following PL/SQL block declares an object based on the newly created data dictionary types. The types POINTS_VARRAY_TYPE and POINTS_NESTED_TABLE_TYPE enable us to create database tables with columns of each type. Additionally, we can declare PL/SQL variables of these same types.

The first INSERT statement inserts a two-point array for the varray and nested table. Following this initial insert, the varray and nested table structures are populated with a series of points. The varray has five points; the nested table has four points. These point array structures are the second insert. Following that, the structures are read from the database with a SELECT statement. The key to this working is the types that have been created in the database; these same types are used for database columns and PL/SQL structures.

 DECLARE     a_points_varray points_varray_type                     := points_varray_type();     a_points_nested_table points_nested_table_type; BEGIN     INSERT INTO environment_data         (sample_ID,  points_varray, points_nested_table)     VALUES         (1,          points_varray_type                  (point_type(3,4),point_type(3,5)),          points_nested_table_type                  (point_type(1,2),point_type(5,9)));     a_points_varray := points_varray_type                                (point_type(1,2),                                 point_type(2,3),                                 point_type(7,3),                                 point_type(2,8),                                 point_type(5,4));     a_points_nested_table :=  points_nested_table_type                                (point_type(1,2),                                 point_type(4,0),                                 point_type(7,3),                                 point_type(5,9));     INSERT INTO environment_data         (sample_ID, points_varray, points_nested_table)     VALUES         (2, a_points_varray, a_points_nested_table);     SELECT  points_varray, points_nested_table       INTO  a_points_varray, a_points_nested_table       FROM  environment_data      WHERE  sample_ID = 1; END; 

11.9.7 Objects

The material in this section is based on the nested table defined in the previous section.

You can build objects, with member functions, using types you create. We created the type POINTS_NESTED_TABLE TYPE. This can be used as a database table column type. It can be used as a PL/SQL variable type. We can create an object from this type. Such an object would be an array of points with member functions. A motivation for this would be the need to perform functions on the array points. For example, there is a constant need to examine the minimum X coordinate, maximum X coordinate, and average point. The following creates an object type. This is like the specification of the object. It declares member functions. The body is a second part.

The DDL to create this object is the following.

 CREATE OR REPLACE TYPE points_object_type AS OBJECT (points  points_nested_table_type,     MEMBER FUNCTION sample_size RETURN NUMBER,     MEMBER FUNCTION point_text RETURN VARCHAR2,     MEMBER FUNCTION min_x RETURN NUMBER,     MEMBER FUNCTION max_x RETURN NUMBER,     MEMBER FUNCTION avg_x RETURN NUMBER,     MEMBER FUNCTION best_point RETURN point_type,     MEMBER procedure add_to(v point_type)); 

The object body is created with the following.

 CREATE OR REPLACE TYPE body points_object_type AS     MEMBER FUNCTION sample_size RETURN NUMBER IS     BEGIN         RETURN points.count;     END sample_size;     MEMBER FUNCTION point_text RETURN VARCHAR2     IS         s varchar2(1000);     BEGIN         FOR i IN 1..points.COUNT LOOP             s := s  '('points(i).x','points(i).x')';         END LOOP;         RETURN s;     END point_text;     MEMBER FUNCTION min_x RETURN NUMBER     IS         result NUMBER := null;     BEGIN         FOR i IN 1..points.COUNT LOOP             result :=                 least(nvl(result,points(i).x),points(i).x);         END LOOP;         return result;     END min_x;     MEMBER FUNCTION max_x RETURN NUMBER     IS         result NUMBER;     BEGIN         FOR i IN 1..points.COUNT LOOP             result :=                 greatest(nvl(result,points(i).x),points(i).x);         END LOOP;         return result;     END max_x;     MEMBER FUNCTION avg_x RETURN NUMBER     IS         result NUMBER := 0;     BEGIN         FOR i IN 1..points.COUNT LOOP             result := result + points(i).x;         END LOOP;         return (result/points.count);     END avg_x;     MEMBER FUNCTION best_point RETURN point_type     IS         pt point_type;     BEGIN         pt := point_type(points(1).x,points(points.COUNT).y);         RETURN pt;     END;     MEMBER procedure add_to(v point_type) IS     BEGIN         points.EXTEND;         points (points.count) := v;     exception         when others then points := points_nested_table_type(v);     END add_to; END; 

The member functions are frequently short and simple. They only operate on a nested table structure consisting of a series of (X,Y) points. Building the member functions into the object can reduce the size and complexity of the application code. The following PL/SQL block illustrates a new ENVIRONMENT_DATA table. In this table, the column is not a nested table, but rather an object of type POINTS_OBJECT_TYPE.

 CREATE TABLE environment_data (sample_ID       NUMBER(3),  points_object   points_object_type) NESTED TABLE  points_object.points STORE AS points_object_tab; 

The following PL/SQL block declares an object of type POINTS_OBJECT_TYPE. Methods are used to populate and select from the object. The first method used is ADD_TO. This extends the nested table by adding a point. Four points are added to the object. An INSERT statement adds the object to the database table. The object is selected from the table, back into the original object declaration.

The calls to DBMS_OUTPUT print 2, 8, and 5.75 for the min, max, and average values respectively. The BEST_POINT method evaluates to the minumum X and maximum Y coordinate and prints X=2 Y=3.

 DECLARE     point_obj points_object_type :=         points_object_type(points_nested_table_type());     best_point point_type; BEGIN     point_obj.add_to(point_type(2,3));     point_obj.add_to(point_type(6,1));     point_obj.add_to(point_type(7,3));     point_obj.add_to(point_type(8,3));     INSERT INTO environment_data(sample_ID, points_object)     VALUES         (1, point_obj);     SELECT points_object     INTO   point_obj     FROM   environment_data     WHERE  sample_ID = 1;     dbms_output.put_line(point_obj.min_x);     dbms_output.put_line(point_obj.max_x);     dbms_output.put_line(point_obj.avg_x);     best_point := point_obj.best_point;     dbms_output.put_line         ('x='best_point.x' y='best_point.y); END; 

11.9.8 Large Objects (LOBs)

Large objects refer to whole documents or images that need to be stored as column values in a table. Employee photographic images are examples of column values in an employee database. These binary graphic images are not manipulated but you can use PL/SQL to read images from one table and store into another. You can load images from host files into a database table. Binary images are stored as a BLOB in the database. The BLOB is also a PL/SQL type. Character objects are stored in the database as a CLOB. There is also a PL/SQL CLOB type. A BFILE is a database and PL/SQL type used to reference a file. All manipulation of large objects is through the Oracle DBMS_LOB package.

The following illustrates a scenario in which a table is used to store large character documents. The document is the DOC column. The ID column is a primary key used to identify a document.

The table to store documents is:

 CREATE TABLE doc (doc_id   NUMBER(5) PRIMARY KEY,      document CLOB); 

We start with inserting an empty document using the Oracle built-in function EMPTY_CLOB. Later, the empty document can be referenced in PL/SQL. This will require declaring a CLOB type object that acts like a pointer to the empty document. To insert the empty document (using 1 for this DOC ID.)

 INSERT INTO DOC (doc_id,document) VALUES (1, empty_clob()); COMMIT; 

Consider the need to load host files into the DOC table. Assume the host files are large character documents and exist in a host directory called D:\DOCS.

We do not use host directory pathnames in the code. Oracle maintains logical to physical mappings in the view DBA_DIRECTORIES. You must create the logical connection with a CREATE DIRECTORY statement. After that, you code using the logical name. Create a directory in the database with the following.

 CREATE OR REPLACE DIRECTORY SAMPLE_DOCS AS 'D:\DOCS'; 

To do this you need the CREATE ANY DIRECTORY privilege.

Assume you want to load this file into the DOC table:

 users_manual.pdf 

The following PL/SQL block uses the PL/SQL function BFILENAME to reference the file content. BFILENAME establishes the variable THE_BFILE as a reference to the file. After the BFILENAME call, the empty document is selected using the primary key of 1 from the preceding INSERT statement. Both the CLOB and BFILE are accessed in a manner similar to the way a pointer references memory. The content is not physically local to the procedure. It is referenced and accessed through the DBMS_LOB packages. The LOADFROMFILE copies the content. Then both references are closed. At completion, the host file content exists in the DOC table.

 DECLARE     the_bfile   BFILE;     the_clob    CLOB;     bfile_size  PLS_INTEGER;     clob_size   PLS_INTEGER;     v_directory VARCHAR2(30) := 'SAMPLE_DOCS';     v_filename  VARCHAR2(30) := 'users_manual.pdf '; BEGIN     the_bfile := BFILENAME(v_directory, v_filename);     SELECT document INTO the_clob FROM doc WHERE doc_id = 1     FOR UPDATE OF doc.document NOWAIT;     -- open the clob for update.     dbms_lob.open(lob_loc => the_clob,                   open_mode => dbms_lob.lob_readwrite);     -- open the bfile     dbms_lob.fileopen (file_loc => the_bfile,                        open_mode => dbms_lob.file_readonly);     -- what is the size of the bfile     bfile_size := dbms_lob.getlength(the_bfile);     -- load from file     dbms_lob.loadfromfile (dest_lob => the_clob,                            src_lob => the_bfile,                            amount => bfile_size);     -- check size after load.     clob_size := dbms_lob.getlength(the_clob);     -- close file     dbms_lob.fileclose(file_loc => the_bfile);     -- close blob     dbms_lob.close(lob_loc => the_clob);     commit; END; 

The following block extracts the CLOB object and prints it using DBMS_OUTPUT. Use a small file for this exercise. CLOBs are not intended to be printed using DBMS_OUTPUT. The Oracle REPLACE function, in the last line of this block, replaces a line feed with a dash. Otherwise, DBMS_OUTPUT prints the first line. This script reads the first 200 characters of the CLOB stored in the DOC table.

 DECLARE     the_clob        CLOB;     clob_size       PLS_INTEGER;     max_size        PLS_INTEGER := 200;     amount_to_read  PLS_INTEGER;     offSet          PLS_INTEGER:= 1;     vbuf            VARCHAR2(200) := null; BEGIN     SELECT document INTO the_clob FROM doc WHERE doc_id=1;     dbms_lob.open(the_clob, dbms_lob.lob_readonly);     clob_size := dbms_lob.getlength(the_clob);     amount_to_read := least(clob_size, max_size);     dbms_lob.read(the_clob, amount_to_read, offset, vbuf);     dbms_lob.close(the_clob);     dbms_output.put_line(replace(vbuf,chr(10),'-')); END; 

This will print the first 200 characters of the CLOB, which you loaded from a BFILE, and which you first read with BFILENAME.