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 OR REPLACE PROCEDURE obtain_emp_detail(emp_info IN VARCHAR2) IS
emp_qry VARCHAR2(500);
emp_first employees.first_name%TYPE;
emp_last employees.last_name%TYPE;
email employees.email%TYPE;
valid_id_count NUMBER := 0;
valid_flag BOOLEAN := TRUE;
temp_emp_info VARCHAR2(50);
BEGIN
emp_qry := 'SELECT FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES ';
IF emp_info LIKE '%@%' THEN
temp_emp_info := substr(emp_info,0,instr(emp_info,'@')-1);
emp_qry := emp_qry || 'WHERE EMAIL = :emp_info';
ELSE
SELECT COUNT(*)
INTO valid_id_count
FROM employees
WHERE employee_id = emp_info;
IF valid_id_count > 0 THEN
temp_emp_info := emp_info;
emp_qry := emp_qry || 'WHERE EMPLOYEE_ID = :id';
ELSE
valid_flag := FALSE;
END IF;
END IF;
IF valid_flag = TRUE THEN
EXECUTE IMMEDIATE emp_qry
INTO emp_first, emp_last, email
USING temp_emp_info;
DBMS_OUTPUT.PUT_LINE(emp_first || ' ' || emp_last || ' - ' || email);
ELSE
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE SPECIFIED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
END IF;
END;
At runtime, the procedure creates a SQL query based upon the criteria that are passed into the procedure by the invoking program. That query is then executed using the EXECUTE IMMEDIATE statement along with the argument that will be substituted into the query WHERE clause.
Solution #2
Use the DBMS_SQL package to create a query based upon criteria that are specified at runtime. The example in this solution will query the employee table and retrieve data based upon the parameter that has been passed into the procedure. The procedure will accept either a primary key ID or an employee e-mail address. The SQL statement that will be used to query the database will be determined at runtime based upon what type of argument is used.
CREATE OR REPLACE PROCEDURE obtain_emp_detail(emp_info IN VARCHAR2) IS
emp_qry VARCHAR2(500);
emp_first employees.first_name%TYPE := NULL;
emp_last employees.last_name%TYPE := NULL;
email employees.email%TYPE := NULL;
valid_id_count NUMBER := 0;
valid_flag BOOLEAN := TRUE;
temp_emp_info VARCHAR2(50);
cursor_name INTEGER;
row_ct INTEGER;
BEGIN
emp_qry := 'SELECT FIRST_NAME, LAST_NAME, EMAIL FROM EMPLOYEES ';
IF emp_info LIKE '%@%' THEN
temp_emp_info := substr(emp_info,0,instr(emp_info,'@')-1);
emp_qry := emp_qry || 'WHERE EMAIL = :emp_info';
ELSE
SELECT COUNT(*)
INTO valid_id_count
FROM employees
WHERE employee_id = emp_info;
IF valid_id_count > 0 THEN
temp_emp_info := emp_info;
emp_qry := emp_qry || 'WHERE EMPLOYEE_ID = :emp_info';
ELSE
valid_flag := FALSE;
END IF;
END IF;
IF valid_flag = TRUE THEN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, emp_qry, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':emp_info', temp_emp_info);
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);
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);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE SPECIFIED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
END IF;
DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('THE INFORMATION YOU HAVE SPECIFIED DOES ' ||
'NOT MATCH ANY EMPLOYEE RECORD'),
END;
How It Works #1
Native dynamic SQL allows you to form a string of SQL text and then execute it via the EXECUTE IMMEDIATE statement. This is very useful when the columns, table names, or WHERE clause text is not known at runtime. The program can build the SQL string as it needs to, and then the EXECUTE IMMEDIATE statement will execute it. The format for the EXECUTE IMMEDIATE statement is as follows:
EXECUTE IMMEDIATE sql_string
[INTO variable_name1[, variable_name2, . . .]
USING variable_name1[, variable_name2, . . .]];
The EXECUTE IMMEDIATE statement requires only one parameter, which is a SQL string to execute. The remainder of the statement is optional. The INTO clause lists all the variables that a SQL query would return values into. The variables should be listed in the same order within the SQL string as they are listed within the INTO clause. The USING clause lists all the variables that will be bound to the SQL string at runtime. Bind variables are arguably one of the most valuable features of the PL/SQL language. Each variable listed in the USING clause is bound to a bind variable within the SQL string. The order in which the variables are listed in the USING clause is the same order in which they will be bound within the string. Take a look at the following example that uses two bind variables:
EXECUTE IMMEDIATE 'select email from employees ' ||
'where last_name =:last ' ||
'and first_name = :first'
INTO v_email
USING v_last, v_first;
In the example query, the variables contained within the USING clause are bound in order to the bind variables within the SQL string. Bind variables are the cornerstone to developing robust, secure, and well-performing software.
How It Works #2
The DBMS_SQL package can also be used to perform the same task. Each of the different techniques, native dynamic SQL and DBMS_SQL, have their advantages and disadvantages. The major difference between the use of DBMS_SQL and native dynamic SQL is how the dynamic SQL string is executed. In this example, DBMS_SQL package functions are used to process the SQL rather than EXECUTE IMMEDIATE. As you can see, the code is quite a bit lengthier than using EXECUTE IMMEDIATE, and it essentially returns the same information. In this case, DBMS_SQL is certainly not the best choice. DBMS_SQL can become useful in situations where you do not know the SELECT list until runtime or when you are unsure of which variables must be bound to a SELECT or DML statement. On the other hand, you must use native dynamic SQL if you intend to use the cursor variable attributes %FOUND, %NOTFOUND, %ISOPEN, or %ROWCOUNT when working with your cursor.
Note Native dynamic SQL was introduced in Oracle 9i, because DBMS_SQL was overly complex for many of the routine tasks that programmers perform. We consider use of native dynamic SQL as the technique of choice for working with dynamic SQL. Use DBMS_SQL only when you have a specific need to do so.