O'Reilly logo

Oracle and PL/SQL Recipes: A Problem-Solution Approach by Matt Arena, Josh Juneau

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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; ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required