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 the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
