Oracle %Type Attributes

PL/SQL objects (such as variables and constants) and database objects (such as columns and tables) include certain attributes that can be used to simplify variable and constant declarations.

• %Type attribute: The %Type attribute provides the data type of a variable, constant, or column. Variable and constant declarations using the %Type attribute are treated like declarations that explicitly state the datatype. They can also be initialized.

Note that a NOT NULL column constraint does not apply to variables declared using %TYPE. Click the link below to hear more about using an %Type attribute.
Type Attributes
In the example below, you declare a variable v_prod_id of the same type as PRODUCT_ID, a field within the PRODUCT table:

v_prod_id product.product_id%TYPE;

Such a declaration has two advantages:

1. The exact data type of PRODUCT_ID need not be known.
2. If the Oracle definition of PRODUCT_ID is changed, the data type of v_prod_id changes accordingly at runtime.

In addition to the %ROWTYPE declaration, you can use the %TYPE declaration to inherit datatype information. If you use the %ROWTYPE declaration, the variable inherits the column and datatype information for all the columns in the cursor's result set. If you use the %TYPE declaration, the variable only inherits the definition of the column used to define it.
You can even base %TYPE definitions on cursors, as shown in the following example:

cursor rad_cursor is
select * from RADIUS_VALS;
rad_val rad_cursor%ROWTYPE;
rad_val_radius rad_val.Radius%TYPE;

In the preceding listing, the rad_val variable inherits the datatypes of the result set of the rad_cursor cursor. The rad_val_radius variable inherits the datatype of the Radius column within the rad_val variable.
The advantage of datatype anchoring using %ROWTYPE and %TYPE definitions is that it makes the datatype definitions in your PL/SQL code independent of the underlying data structures. If the RADIUS_VALS Radius column is changed from a NUMBER(5) datatype to a NUMBER(4,2) datatype, you do not need to modify your PL/SQL code; the datatype assigned to the associated variables will be determined dynamically at runtime.

The %ROWTYPE attribute is useful if a record variable must be declared that has the same structure as a row within a table or view, or as a row fetched from a cursor. The row is represented as a record whose fields have the same names and datatypes as the columns within the table or view. An %ROWTYPE declaration cannot include an initialization clause. To declare a record that can store an entire row from the PRODUCT table, %ROWTYPE can be used as follows: product_rec product%ROWTYPE; The column values are stored within individual fields of the record. You can reference a specific field by using the following syntax:

product_rec.product_id

You can assign a value to a field by using the following syntax:

product_rec.product_id := 11;

The following section discusses more about the correct use of composite datatypes within PL/SQL.

The correct composite datatypes for the scenarios are:

1. Need to store 10 product names: Use PL/SQL table
2. Need to store name , sale_price , current_inventory_count for a single product: Use PL/SQL record
3. Need to store product_name , but the size of the column in the database is unknown: Use %TYPE
4. Need to store the details of all the columns within the PRODUCT table: Use %ROWTYPE

PL/SQL composite data types
Composite types have internal components that can be manipulated individually, such as the elements of an array, record, or table. Oracle TimesTen In-Memory Database supports the following composite data types:

1. Associative array (index-by table)
2. Nested table
3. Varray
4. Record

Associative arrays, nested tables, and varrays are also referred to as collections. The following sections discuss the use of composite data types:

1. Using collections in PL/SQL
2. Using records in PL/SQL
3. Using associative arrays from applications

Oracle gives you the ability to call PL/SQL from other languages such as C, Java, Perl, PHP, or any number of other places. This seems like a reasonable request, but if you have ever worked with cross-language environments, you may be aware of some of the intricacies involved with connecting different systems with language-specific datatypes, specifically composite datatypes like

1. arrays,
2. records, and
3. objects and,
4. differing parameter semantics or vendor extensions

to standard application programming interfaces (APIs) like Microsoft's ODBC
I will show a simple example of calling PL/SQL from the outside world. Let us say that I have written a PL/SQL function that accepts an ISBN expressed as a string and returns the corresponding book title:

/* File on web: booktitle.fun */
FUNCTION booktitle (isbn_in IN VARCHAR2)
RETURN VARCHAR2
IS
l_title books.title%TYPE;
CURSOR icur IS SELECT title FROM books WHERE isbn = isbn_in;
BEGIN
OPEN icur;
FETCH icur INTO l_title;
CLOSE icur;
RETURN l_title;
END;

In SQL*Plus, I could call this in several different ways. The shortest way would be as follows:

SQL> EXEC DBMS_OUTPUT.PUT_LINE(booktitle('0-596-00180-0'))
Learning Oracle PL/SQL
PL/SQL procedure successfully completed.

A composite variable has internal components, which you can access individually. You can pass entire composite variables to subprograms as parameters. PL/SQL has two kinds of composite variables, collections and records
The next lesson concludes this module.