PL/SQL   «Prev 

Understanding Object Tables in Oracle

Using REF

For nested tables and varrays declared within PL/SQL, the element type of the table or varray can be any PL/SQL data type except REF CURSOR.

Using Cursor Variables (REF CURSORs)

Like a cursor, a cursor variable points to the current row in the result set of a multiple-row query. A cursor variable is more flexible because it is not tied to a specific query. You can open a cursor variable for any query that returns the right set of columns. You pass a cursor variable as a parameter to local and stored subprograms. Opening the cursor variable in one subprogram, and processing it in a different subprogram, helps to centralize data retrieval. This technique is also useful for multi-language applications, where a PL/SQL subprogram might return a result set to a subprogram written in a different language, such as Java or Visual Basic.
Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as an input host variable (bind variable) to PL/SQL. Application development tools such as Oracle Forms, which have a PL/SQL engine, can use cursor variables entirely on the client side. Or, you can pass cursor variables back and forth between a client and the database server through remote subprogram calls

First, we create an object CUSTOMER_TYPE, which has information about a customer, including the customer's name, address, and phone numbers.

Now that we have the CUSTOMER_TYPE object, we will reference it with SALE_HEADER table by using the REF pointer.