Composite Types Used In 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

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;

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