Oracle PL/SQL by Example, Fifth Edition (2015)

---

Next, consider an example of a cursor-based record.

For Example ch16_2a.sql

DECLARE
CURSOR student_cur IS
SELECT first_name, last_name, registration_date
FROM student
WHERE rownum <= 4;

student_rec student_cur%ROWTYPE;
BEGIN
OPEN student_cur;
LOOP
FETCH student_cur INTO student_rec;
EXIT WHEN student_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE
('Name: '||student_rec.first_name||' '||student_rec.last_name);
DBMS_OUTPUT.PUT_LINE
('Registration Date: '||to_char(student_rec.registration_date, 'MM/DD/YYYY'));
END LOOP;
END;

The student_rec record has the same structure as the rows returned by the student_cur cursor. As a result, similar to the previous example, there is no need to reference the individual fields when data is fetched from the cursor to the record.

When run, this example produces the following output:

Name: George Kocka
Registration Date: 02/08/2007
Name: Janet Jung
Registration Date: 02/08/2007
Name: Kathleen Mulroy
Registration Date: 02/08/2007
Name: Joel Brendler
Registration Date: 02/08/2007

Because a cursor-based record is defined based on the rows returned by a select statement of a cursor, its declaration must be proceeded by a cursor declaration. In other words, a cursor-based record is dependent on a particular cursor and cannot be declared prior to its cursor.

Consider a modified version of the previous example. The cursor-based record variable is declared before the cursor (changes are shown in bold). In turn, when run, this example causes a syntax error.

For Example ch16_2b.sql

DECLARE
student_rec student_cur%ROWTYPE;

CURSOR student_cur IS
SELECT first_name, last_name, registration_date
FROM student
WHERE rownum <= 4;

BEGIN
OPEN student_cur;
LOOP
FETCH student_cur INTO student_rec;
EXIT WHEN student_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE
('Name: '||student_rec.first_name||' '||student_rec.last_name);
DBMS_OUTPUT.PUT_LINE
('Registration Date: '|| to_char(student_rec.registration_date, 'MM/DD/YYYY'));
END LOOP;
END;

This example produces the following erroneous output:

ORA-06550: line 2, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 2, column 16:
PL/SQL: Item ignored
ORA-06550: line 12, column 30:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 12, column 7:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 21:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 15, column 7:
PL/SQL: Statement ignored
ORA-06550: line 18, column 40:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 17, column 7:
PL/SQL: Statement ignored

User-Defined Records

So far, you have seen how to create records based on a table or a cursor. However, you may need to create a record that is not based on any table or any one cursor. For such situations, PL/SQL provides a user-defined record type that allows you to have complete control over the record structure.

The general syntax for creating a user-defined record is shown in Listing 16.1 (the reserved words and phrases surrounded by brackets are optional).

Listing 16.1 User-Defined Record Type

TYPE type_name IS RECORD
(field_name1 datatype1 [NOT NULL] [ := DEFAULT EXPRESSION],
field_name2 datatype2 [NOT NULL] [ := DEFAULT EXPRESSION],
...
field_nameN datatypeN [NOT NULL] [ := DEFAULT EXPRESSION]);

record_name TYPE_NAME;

First, a record structure is defined using the TYPE statement, where type_name is the name of the record type that is used in the second step to declare the actual record. Enclosed in the parentheses are declarations of each record field with its name and data type. You may also specify aNOT NULL constraint and/or assign a default value. Second, the actual record is declared based on the type specified in the previous step. Consider the following example.

For Example ch16_3a.sql

DECLARE
TYPE time_rec_type IS RECORD
(curr_date DATE,
curr_day VARCHAR2(12),
curr_time VARCHAR2(8) := '00:00:00');

time_rec TIME_REC_TYPE;
BEGIN
SELECT sysdate
INTO time_rec.curr_date
FROM dual;

time_rec.curr_day := TO_CHAR(time_rec.curr_date, 'DAY');
time_rec.curr_time := TO_CHAR(time_rec.curr_date, 'HH24:MI:SS');

DBMS_OUTPUT.PUT_LINE ('Date: '||to_char(time_rec.curr_date, 'MM/DD/YYYY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE ('Day: '||time_rec.curr_day);
DBMS_OUTPUT.PUT_LINE ('Time: '||time_rec.curr_time);
END;

In this example, time_rec_type is a user-defined record type that contains three fields. The last field, curr_time, has been initialized to a particular value. Here, time_rec is a user-defined record based on the time_rec_type. Unlike in the previous examples, each record field is assigned a value individually. When run, the script produces the following output:

Date: 05/20/2014 10:26:32
Day: TUESDAY
Time: 10:26:32

As mentioned earlier, when declaring a record type, you may specify a NOT NULL constraint for individual fields. Such fields must be initialized. The following example causes a syntax error because a record field has not been initialized after a NOT NULL constraint has been defined on it.

For Example ch16_4a.sql

DECLARE
TYPE sample_type IS RECORD
(field1 NUMBER(3),
field2 VARCHAR2(3) NOT NULL);
sample_rec sample_type;

BEGIN
sample_rec.field1 := 10;
sample_rec.field2 := 'ABC';

DBMS_OUTPUT.PUT_LINE ('sample_rec.field1 = '||sample_rec.field1);
DBMS_OUTPUT.PUT_LINE ('sample_rec.field2 = '||sample_rec.field2);
END;

The preceding example produces this output:

ORA-06550: line 4, column 8:
PLS-00218: a variable declared NOT NULL must have an initialization assignment

Now consider the correct version of this example (modified statements are highlighted in bold).

For Example ch16_4b.sql

DECLARE
TYPE sample_type IS RECORD
(field1 NUMBER(3),
field2 VARCHAR2(3) NOT NULL := 'ABC'); -- initialize a NOT NULL field

sample_rec sample_type;

BEGIN
sample_rec.field1 := 10;

DBMS_OUTPUT.PUT_LINE ('sample_rec.field1 = '||sample_rec.field1);
DBMS_OUTPUT.PUT_LINE ('sample_rec.field2 = '||sample_rec.field2);
END;

This version of the example produces the following output:

sample_rec.field1 = 10
sample_rec.field2 = ABC

Record Compatibility

You have seen that a record is defined by its name, structure, and type. Actually, two records may have the same structure yet be of a different type. In such a case, certain restrictions apply to the operations between the different record types. Consider the following example:

For Example ch16_5a.sql

DECLARE
TYPE name_type1 IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30));
TYPE name_type2 IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30));

name_rec1 name_type1;
name_rec2 name_type2;
BEGIN
name_rec1.first_name := 'John';
name_rec1.last_name := 'Smith';
name_rec2 := name_rec1; -- illegal assignment
END;

In this example, both records have the same structure, but each record is of a different type. As a result, these records are not compatible with each other on the record level. In other words, an aggregate assignment statement

name_rec2 := name_rec1; -- illegal assignment

will cause an error:

ORA-06550: line 15, column 17:
PLS-00382: expression is of wrong type
ORA-06550: line 15, column 4:
PL/SQL: Statement ignored

To assign name_rec1 to name_rec2, you can assign each field of name_rec1 to the corresponding field of name_rec2, or you can declare name_rec2 so that it has the same data type as name_rec1 (changes are shown in bold).

For Example ch16_5b.sql

DECLARE
TYPE name_type1 IS RECORD
(first_name VARCHAR2(15),
last_name VARCHAR2(30));

name_rec1 name_type1;
name_rec2 name_type1;
BEGIN
name_rec1.first_name := 'John';
name_rec1.last_name := 'Smith';
name_rec2 := name_rec1; -- no longer illegal assignment
END;

The assignment restriction just mentioned applies to user-defined records. In other words, you can assign a table-based or cursor-based record to a user-defined record as long as they have the same structure. Consider the following example:

For Example ch16_6a.sql

DECLARE
CURSOR course_cur IS
SELECT *
FROM course
WHERE rownum < 2;

TYPE course_type IS RECORD
(course_no NUMBER(38)
,description VARCHAR2(50)
,cost NUMBER(9,2)
,prerequisite NUMBER(8)
,created_by VARCHAR2(30)
,created_date DATE
,modified_by VARCHAR2(30)
,modified_date DATE);

course_rec1 course%ROWTYPE; -- table-based record
course_rec2 course_cur%ROWTYPE; -- cursor-based record
course_rec3 course_type; -- user-defined record
BEGIN
-- Populate table-based record
SELECT *
INTO course_rec1
FROM course
WHERE course_no = 10;

-- Populate cursor-based record
OPEN course_cur;
LOOP
FETCH course_cur INTO course_rec2;
EXIT WHEN course_cur%NOTFOUND;
END LOOP;

-- Assign COURSE_REC2 to COURSE_REC1 and COURSE_REC3
course_rec1 := course_rec2;
course_rec3 := course_rec2;

DBMS_OUTPUT.PUT_LINE (course_rec1.course_no||' - '||course_rec1.description);
DBMS_OUTPUT.PUT_LINE (course_rec2.course_no||' - '||course_rec2.description);
DBMS_OUTPUT.PUT_LINE (course_rec3.course_no||' - '||course_rec3.description);
END;

In this example, each record is of a different type; however, they are compatible with one another because all of the records have the same structure. As a result, this example does not cause any syntax errors and produces the following output:

10 - Technology Concepts
10 - Technology Concepts
10 - Technology Concepts

Lab 16.2: Nested Records