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

8-6. Returning Data from a Dynamic Query into a Record

Problem

You are writing a block of code that will need to use dynamic SQL to execute a query because the exact SQL string is not known until runtime. The query needs to return the entire contents of the table row so that all columns of data can be used. You want to return the columns into a record variable.

Solution

Create a native dynamic SQL query to accommodate the SQL string that is unknown until runtime. FETCH the data using BULK COLLECT into a table of records. Our solution example shows rows from the jobs table being fetched into records, after which the individual record columns of data can be worked with. The following code block demonstrates this technique:

CREATE OR REPLACE PROCEDURE ...