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

8-1. Executing a Single Row Query That Is Unknown at Compile Time

Problem

You need to query the database for a single row of data matched by the primary key value. However, you are unsure of what columns will need to be returned at runtime.

Solution #1

Use a native dynamic query to retrieve the columns of data that are determined by your application at runtime. After you determine what columns need to be returned, create a string that contains the SQL that is needed to query the database. The following example demonstrates the concept of creating a dynamic SQL query and then using native dynamic SQL to retrieve the single row that is returned. The following code is a complete rewrite of Solution #1 to this recipe using native dynamic SQL.

CREATE ...