8-2. Executing a Multiple Row Query That Is Unknown at Compile Time

Problem

Your application requires a database table to be queried, but the filters for the WHERE clause are not known until runtime. You have no idea how many rows will be returned by the query.

Solution #1

Create a native dynamic query using a SQL string that will be built at application runtime. Declare the query using REF CURSOR, execute it by issuing an OPEN statement, and loop through the records using a standard loop, fetching the fields within each iteration of the loop. This technique is illustrated via the code in the following example:

DECLARE   emp_qry                 VARCHAR2(500);   TYPE                    cur_type IS REF CURSOR;   cur                     cur_type; ...

Get Oracle and PL/SQL Recipes: A Problem-Solution Approach now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.