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;
emp_first employees.first_name%TYPE;
emp_last employees.last_name%TYPE;
email employees.email%TYPE;
dept_id employees.department_id%TYPE := &department_id;
BEGIN
-- DEPARTMENT_ID WILL NOT UNIQUELY DEFINE ANY ONE EMPLOYEE
emp_qry := 'SELECT FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES ' ||
' WHERE DEPARTMENT_ID = :id';
OPEN cur FOR emp_qry USING dept_id;
LOOP
FETCH cur INTO emp_first, emp_last, email;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_first || ' ' || emp_last || ' - ' || email);
END LOOP;
CLOSE cur;
END;
This example accepts a DEPARTMENT_ID as input, and it uses a bind variable to substitute the value within the SQL string. Although the actual SQL string in this example does not require the use of a dynamic query, it is a useful example to demonstrate the technique.
Solution #2
This same procedure can also be performed using the DBMS_SQL package. Although the native dynamic SQL solution is easier to understand and implement, the DBMS_SQL alternative offers some different options that are not available when using the native method. The following example is a sample of a procedure that performs the same functionality as Solution #1 of this recipe. However, the procedure in the following example uses the DBMS_SQL package to parse and execute the dynamic query rather than native dynamic SQL.
CREATE OR REPLACE PROCEDURE obtain_emp_detail(dept_id IN NUMBER) IS
emp_qry VARCHAR2(500);
emp_first employees.first_name%TYPE := NULL;
emp_last employees.last_name%TYPE := NULL;
email employees.email%TYPE := NULL;
cursor_name INTEGER;
row_ct INTEGER;
BEGIN
emp_qry := 'SELECT FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES ' ||
' WHERE DEPARTMENT_ID = :id';
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, emp_qry, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':id', dept_id);
DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, emp_first, 20);
DBMS_SQL.DEFINE_COLUMN(cursor_name, 2, emp_last, 25);
DBMS_SQL.DEFINE_COLUMN(cursor_name, 3, email, 25);
row_ct := DBMS_SQL.EXECUTE(cursor_name);
LOOP
IF DBMS_SQL.FETCH_ROWS(cursor_name) > 0 THEN
DBMS_SQL.COLUMN_VALUE (cursor_name, 1, emp_first);
DBMS_SQL.COLUMN_VALUE (cursor_name, 2, emp_last);
DBMS_SQL.COLUMN_VALUE (cursor_name, 3, email);
DBMS_OUTPUT.PUT_LINE(emp_first || ' ' || emp_last || ' - ' || email);
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE USED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
END;
How It Works
The use of native dynamic SQL in this solution is more or less equivalent to that which was performed in the previous recipe. The largest difference lies in the use of the REF CURSOR as opposed to the EXECUTE IMMEDIATE statement. The REF CURSOR is used to create a cursor using a dynamic SQL string.
Cursor variables can be either weakly typed or strongly typed. The cursor variable demonstrated in the solution to this example of a weakly typed REF CURSOR, since the SQL string is not known until runtime. A strongly typed cursor variable must be known at runtime. In this sense, a strongly typed cursor variable is very similar to a regular cursor.
The REF CURSOR type must be declared first, and then the actual cursor variable that will be used in your code should be declared using the REF CURSOR as its type. Next you have the OPEN statement. To tell Oracle what SQL to use for the cursor, the OPEN statement should include a FOR clause indicating the SQL string that the cursor should use. If there are any variables to bind into the query, the optional USING clause should follow at the end of the OPEN statement.
The subsequent cursor loop should work with the REF CURSOR in the same manner that you would use with regular cursor variables. Always FETCH the current record or its contents into a local record or separate local variables. Next, perform the tasks that need to be completed. Lastly, ensure that you include an EXIT statement to indicate that the loop should be terminated after the last record has been processed. The final step in the process is to close the cursor. After the cursor has been closed, it can be assigned a new SQL string since you are working with weakly typed REF CURSORs.
As you can see, the example of using DBMS_SQL in Solution #2 of this recipe as opposed to the example in Recipe 8-1 differs only because of the addition of a LOOP construct. Instead of displaying only one value, this example will loop through all the records that are returned from the query, and the loop will exit when there are no remaining rows in the result. The example in Recipe 8-1 could entail the same loop construct as the one shown in Solution #2 of this recipe, but it is only expected to return one row since the query is based upon a primary and unique key value.
The choice for using DBMS_SQL as opposed to native dynamic SQL (NDS) depends on what you are trying to achieve. DBMS_SQL will allow you to use a SQL string that is greater than 32KB in size, whereas native dynamic SQL will not. However, there are other options for creating large SQL text strings and parsing them with native dynamic SQL. Please see Recipe 8-11 for more details.