Oracle and PL/SQL Recipes: A Problem-Solution Approach

2-4. Returning Queried Data into a PL/SQL Record

Problem

Instead of retrieving only a select few columns via a database query, you'd rather return the entire matching row. It can be a time-consuming task to replicate each of the table's columns in your application by creating a local variable for each along with selecting the correct datatypes. Although you can certainly make use of the %TYPE attribute while declaring the variables, you'd rather retrieve the entire row into a single object. Furthermore, you'd like the object that the data is going to be stored into to have the ability to assume the same datatypes for each of the columns being returned just as you would by using the %TYPE attribute.

Solution

Make use of the %ROWTYPE attribute ...