| Oracle® Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2) Part Number B14251-01 |
|
|
View PDF |
| Oracle® Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2) Part Number B14251-01 |
|
|
View PDF |
This chapter covers the following topics:
Unlike static SQL, which remains the same in each execution, dynamic SQL enables you to build SQL statements as character strings at runtime. The strings contain the text of a SQL statement or PL/SQL block and can also contain placeholders for bind arguments.
You can create general purpose, flexible applications with dynamic SQL because the full text of a SQL statement may be unknown at compilation. You can use dynamic SQL in several different development environments, including PL/SQL, Pro*C/C++, and Java.
For an example of an application that uses dynamic SQL, suppose that a reporting application in a data warehouse environment does not know a table name until runtime. These tables are named according to the starting month and year of the quarter, for example, inv_01_2003, inv_04_2003, inv_07_2003, inv_10_2003, inv_01_2004, and so on. You can use dynamic SQL in your reporting application to specify the table name at runtime.
In a different example, suppose that you want to run a complex query with a user-selectable sort order. Instead of coding the query twice with a different ORDER BY clause in each query, you can construct the query dynamically to include a specified ORDER BY clause.
For the sake of consistency, this chapter discusses dynamic SQL mainly from the perspective of PL/SQL. To process most dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. To process a multi-row query in a PL/SQL procedure, you use the OPEN-FOR, FETCH, and CLOSE statements.
Oracle Database enables you to implement dynamic SQL in a PL/SQL application in the following ways:
Using native dynamic SQL, which involves placing dynamic SQL statements directly into PL/SQL blocks
Calling procedures in the DBMS_SQL package
Although this chapter discusses PL/SQL support for dynamic SQL, you can call dynamic SQL from other languages:
If you use C/C++, you can call dynamic SQL with the Oracle Call Interface (OCI), or you can use the Pro*C/C++ precompiler to add dynamic SQL extensions to your C code.
If you use COBOL, you can use the Pro*COBOL precompiler to add dynamic SQL extensions to your COBOL code.
If you use Java, you can develop applications that use dynamic SQL with JDBC.
If you have a program that uses OCI, Pro*C/C++, or Pro*COBOL to execute dynamic SQL, consider switching to native dynamic SQL inside PL/SQL stored procedures and functions. The network round-trips required to perform dynamic SQL operations from client-side applications might hurt performance. Stored procedures can reside on the server, eliminating network overhead. You can call the PL/SQL stored procedures and stored functions from the OCI, Pro*C/C++, or Pro*COBOL application.
See Also:
Oracle Database PL/SQL Packages and Types Reference for details about the
DBMS_SQL package. To learn about calling Oracle Database stored procedures and stored functions from languages other than PL/SQL, consult the following resources:
Dynamic SQL and static SQL both have advantages and disadvantages. The full text of static SQL statements is known at compilation, which provides the following benefits:
Successful compilation verifies that the SQL statements reference valid database objects and that the necessary privileges are in place to access the objects.
Performance of static SQL is generally better than dynamic SQL.
Despite these advantages, static SQL has limitations that can be overcome with dynamic SQL, as in the following cases:
You do not know the full text of the SQL statements that must be executed in a PL/SQL procedure. These SQL statements may depend on user input or on processing work performed by the program.
You want to execute DDL statements and other SQL statements that are not supported in purely static SQL programs.
You want to write a program that can handle changes in data definitions without the need to recompile. Dynamic SQL is more flexible than static SQL because it enables you to write reusable code that can be adapted for different environments.
As a general rule you should use dynamic SQL only if you cannot use static SQL to accomplish your goals, or if using static SQL is too cumbersome. The following sections describe typical situations in which you should use dynamic SQL:
Only dynamic SQL can execute the following types of statements within PL/SQL program units:
Data definition language (DDL) statements such as CREATE, DROP, GRANT, and REVOKE
Session control language (SCL) statements such as ALTER SESSION and SET ROLE
The TABLE clause in the SELECT statement
The following native dynamic SQL example uses a SELECT statement with the TABLE clause.
Example 8-1 Using SELECT . . . TABLE in Dynamic SQL
-- Create an object t_emp and a datatype t_emplist as a table of type t_emp
CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20))
/
CREATE TYPE t_emplist AS TABLE OF t_emp
/
-- Create a table with a nested table of type t_emplist
CREATE TABLE dept_new (id NUMBER, emps t_emplist)
NESTED TABLE emps STORE AS emp_table;
-- Populate the dept_new table with data
INSERT INTO dept_new VALUES
(
10,
t_emplist
(
t_emp(1, 'SCOTT'),
t_emp(2, 'BRUCE')
)
);
-- Write a PL/SQL block that queries table dept_new and nested table emps
-- SELECT ... FROM ... TABLE is not allowed in static SQL in PL/SQL
DECLARE
v_deptid NUMBER;
v_ename VARCHAR2(20);
BEGIN
EXECUTE IMMEDIATE 'SELECT d.id, e.name
FROM dept_new d, TABLE(d.emps) e
WHERE e.id = 1'
INTO v_deptid, v_ename;
END;
/
See Also:
Oracle Database SQL Reference for information about DDL and SCL statementsYou can use dynamic SQL to create applications that execute dynamic queries whose full text is not available until runtime. Many types of applications need to use dynamic queries, including applications that do the following:
Allow users to input or choose query search or sorting criteria at runtime
Allow users to input or choose optimizer hints at run time
Query a database where the data definitions of tables are constantly changing
Query a database where new tables are created often
Many applications must interact with data that is generated periodically. For example, an application knows the table definitions at compile time, but not the table names. Dynamic SQL enables you to specify table names at runtime.
In the sample data warehouse application discussed in "What Is Dynamic SQL?", the system generates new tables every quarter. You could allow a user to specify the name of the table at runtime with a dynamic SQL query similar to the following sample procedure.
Example 8-2 Dynamically Specifying a Table Name
CREATE OR REPLACE PROCEDURE query_invoice
(p_month VARCHAR2, p_year VARCHAR2)
IS
TYPE cur_typ IS REF CURSOR;
v_inv_cursor cur_typ; -- Declare a cursor variable
v_inv_query VARCHAR2(200);
v_inv_num NUMBER;
v_inv_cust VARCHAR2(20);
v_inv_amt NUMBER;
BEGIN
-- Write dynamic query receiving month and year as parameters
-- and using these values to form the table name, for example, inv_APR_2004
v_inv_query := 'SELECT num, cust, amt FROM inv_'
|| p_month
|| '_'
|| p_year
|| ' WHERE v_inv_num = :g_id';
-- Open a cursor variable
OPEN v_inv_cursor FOR v_inv_query USING v_inv_num;
-- Fetch row into variables
LOOP
FETCH v_inv_cursor
INTO v_inv_num, v_inv_cust, v_inv_amt;
EXIT WHEN v_inv_cursor%NOTFOUND;
-- process row here
END LOOP;
CLOSE v_inv_cursor;
END;
/
You can use dynamic SQL to build a SQL statement that optimizes execution by concatenating hints into a dynamic SQL statement. This technique enables you change the hints based on your current database statistics without recompiling. The following sample procedure uses a variable called p_hint to allow users to pass a hint option to the SELECT statement.
Example 8-3 Concatenating Hints
CREATE OR REPLACE PROCEDURE query_emp
(p_hint VARCHAR2)
IS
TYPE cur_typ IS REF CURSOR;
v_emp_cursor cur_typ;
BEGIN
OPEN v_emp_cursor FOR 'SELECT '
|| p_hint
||' empno, ename, sal, job FROM emp WHERE empno = 7566'; -- process ...
CLOSE v_emp_cursor;
END;
/
In Example 8-3, the user can pass values such as the following for p_hint:
p_hint = '/*+ ALL_ROWS */' p_hint = '/*+ FIRST_ROWS */' p_hint = '/*+ CHOOSE */'
See Also:
Oracle Database Performance Tuning Guide to learn more about using hintsYou can use the EXECUTE IMMEDIATE statement to execute anonymous PL/SQL blocks. In this way you can add flexibility by constructing the contents of the block at runtime.
For example, suppose that you want to write an application that takes an event number and dispatches it to a handler for the event. The name of the handler is in the form EVENT_HANDLER_event_num, where event_num is the number of the event. One approach is to implement the dispatcher as a switch statement, where the code handles each event by making a static call to its appropriate handler. This code is not very extensible because the dispatcher code must be updated whenever a handler for a new event is added.
Example 8-4 Event Dispatching with Static SQL
CREATE OR REPLACE PROCEDURE event_handler_1
(p_handle NUMBER)
IS
BEGIN -- process event 1 RETURN; END;/CREATE OR REPLACE PROCEDURE event_handler_2
(p_handle NUMBER)
IS
BEGIN -- process event 2 RETURN; END;/CREATE OR REPLACE PROCEDURE event_handler_3
(p_handle NUMBER)
IS
BEGIN -- process event 3
RETURN;
END;
/
CREATE OR REPLACE PROCEDURE event_dispatcher
(p_event_num NUMBER, p_handle NUMBER)
IS
BEGIN
IF (p_event_num = 1) THEN
EVENT_HANDLER_1(p_handle);
ELSIF (p_event_num = 2) THEN
EVENT_HANDLER_2(p_handle);
ELSIF (p_event_num = 3) THEN
EVENT_HANDLER_3(p_handle);
END IF;
END;
/
By using native dynamic SQL, you can write a smaller, more flexible event dispatcher as shown in the following example.
By using the invoker's rights feature with dynamic SQL, you can build applications that issue dynamic SQL statements under the privileges and schema of the invoker. These two features—invoker's rights and dynamic SQL—enable you to build reusable application subcomponents that can operate on and access the invoker's data and modules.
See Also:
Oracle Database PL/SQL User's Guide and Reference to learn about invoker's rights and native dynamic SQLThis scenario shows you how to perform the following operations with native dynamic SQL:
Execute DDL and DML operations.
Execute single row and multiple row queries.
The database in this scenario is used for human resources. A master table named offices contains the list of all company locations. The offices table has the following definition:
Column Name Null? Type
LOCATION NOT_NULL VARCHAR2(200)
Multiple emp_location tables contain the employee information, where location is the name of city where the office is located. For example, a table named emp_houston contains employee information for the company's Houston office, whereas a table named emp_boston contains employee information for the company's Boston office.
Each emp_location table has the following definition:
Column Name Null? Type
EMPNO NOT_NULL NUMBER(4)
ENAME NOT_NULL VARCHAR2(10)
JOB NOT_NULL VARCHAR2(9)
SAL NOT_NULL NUMBER(7,2)
DEPTNO NOT_NULL NUMBER(2)
The following sections describe various native dynamic SQL operations that can be performed on the data in the hr database.
The following native dynamic SQL procedure gives a raise to all employees with a particular job title.
Example 8-6 Performing DML in Native Dynamic SQL
CREATE OR REPLACE PROCEDURE salary_raise
(p_raise_percent NUMBER, p_job VARCHAR2)
IS TYPE loc_array_type IS TABLE OF VARCHAR2(40)
INDEX BY binary_integer;
v_dml_str VARCHAR2 (200);
v_loc_array loc_array_type;
BEGIN
-- bulk fetch the list of office locations
SELECT location BULK COLLECT
INTO v_loc_array
FROM offices;
-- for each location, give a raise to employees with the given 'job'
FOR i IN v_loc_array.first..v_loc_array.last LOOP
v_dml_str := 'UPDATE emp_'
|| v_loc_array(i)
|| ' SET sal = sal * (1+(:p_raise_percent/100))'
|| ' WHERE p_job = :g_job_title';
EXECUTE IMMEDIATE v_dml_str USING p_raise_percent, p_job;
END LOOP;
END;
/
SHOW ERRORS;
The EXECUTE IMMEDIATE statement can perform DDL operations. For example, the following procedure adds an office location.
The following procedure uses the same concatenation technique to drop a table.
Example 8-7 Dropping a Table with Native Dynamic SQL
CREATE OR REPLACE PROCEDURE drop_location (p_loc VARCHAR2) IS BEGIN -- Drop the employee table for location 'p_loc', for example, emp_detroit EXECUTE IMMEDIATE 'DROP TABLE ' || 'emp_' || p_loc; -- Remove location from master table DELETE FROM offices WHERE location = p_loc; END; / SHOW ERRORS;
The EXECUTE IMMEDIATE statement can perform dynamic single-row queries. You can specify bind variables in the USING clause and fetch the resulting row into the target specified in the INTO clause of the statement. The following function retrieves the number of employees at a particular location performing a specified job.
Example 8-8 Performing Single-Row Queries in Native Dynamic SQL
CREATE OR REPLACE FUNCTION get_num_of_employees
(p_loc VARCHAR2, p_job VARCHAR2)
RETURN NUMBER
IS
v_query_str VARCHAR2(1000);
v_num_of_employees NUMBER;
BEGIN
-- Use concatenation to form the table name in the SELECT statement
v_query_str := 'SELECT COUNT(*) FROM emp_'
|| p_loc
|| ' WHERE job = :1';
-- Execute the query and put the result row in a variable
EXECUTE IMMEDIATE v_query_str
INTO v_num_of_employees
USING p_job;
RETURN v_num_of_employees;
END;
/
SHOW ERRORS;
The OPEN-FOR, FETCH, and CLOSE statements can perform dynamic multiple-row queries. For example, the following procedure lists all of the employees with a particular job at a specified location.
Example 8-9 Performing Multiple-Row Queries with Dynamic SQL
CREATE OR REPLACE PROCEDURE list_employees
(p_loc VARCHAR2, p_job VARCHAR2)
IS
TYPE cur_typ IS REF CURSOR;
-- Define a cursor variable
v_emp_cursor cur_typ;
v_query_str VARCHAR2(1000);
v_emp_name VARCHAR2(20);
v_emp_num NUMBER;
BEGIN
-- Use concatenation to form the SELECT statement
v_query_str := 'SELECT ename, empno FROM emp_'
|| p_loc
|| ' WHERE job = :g_job_title';
-- Open a cursor variable for the query
OPEN v_emp_cursor FOR v_query_str USING p_job;
-- Loop through each row to find employees who perform the specified job
LOOP
-- Fetch the employee name and ID into variables
FETCH v_emp_cursor INTO v_emp_name, v_emp_num;
EXIT WHEN v_emp_cursor%NOTFOUND;
-- Process row here
END LOOP;
CLOSE v_emp_cursor;
END;
/
SHOW ERRORS;
Oracle Database provides two methods for using dynamic SQL within PL/SQL: native dynamic SQL and the DBMS_SQL package. Each method has advantages and disadvantages. The following sections provide detailed information about the advantages of both methods.
Native dynamic SQL enables you to place dynamic SQL statements directly into PL/SQL code. These dynamic statements include the following:
Queries and DML statements
PL/SQL anonymous blocks
DDL statements
Transaction control statements
Session control statements
To process most native dynamic SQL statements, use the EXECUTE IMMEDIATE statement. To process a multi-row SELECT statement, use OPEN-FOR, FETCH, and CLOSE statements.
Note:
To use native dynamic SQL, you must set theCOMPATIBLE initialization parameter to 8.1.0 or higher.As an alternative to native dynamic SQL, the DBMS_SQL package offers a PL/SQL API to execute dynamic SQL statements. For example, the DBMS_SQL package contains procedures to do the following:
Open a cursor
Parse a cursor
Supply binds
Programs that use the DBMS_SQL package make calls to this package to perform dynamic SQL operations.
See Also:
Oracle Database PL/SQL User's Guide and Reference to learn about native dynamic SQL
Oracle Database PL/SQL Packages and Types Reference to learn about the DBMS_SQL package
Oracle Database Upgrade Guide to learn about the COMPATIBLE initialization parameter
Native dynamic SQL provides the following advantages over the DBMS_SQL package:
Because native dynamic SQL is integrated with SQL, you can use it in the same way that you use static SQL within PL/SQL code. Native dynamic SQL code is typically more compact and readable than equivalent code that uses the DBMS_SQL package.
With the DBMS_SQL package you must call many procedures and functions in a strict sequence, which means that even simple operations require extensive code. You can avoid this complexity by using native dynamic SQL instead.
Table 8-1 illustrates the difference in the amount of code required to perform the same operation with the DBMS_SQL package and native dynamic SQL.
Table 8-1 Code Comparison of DBMS_SQL Package and Native Dynamic SQL
| DBMS_SQL Package | Native Dynamic SQL |
|---|---|
CREATE OR REPLACE PROCEDURE insert_into_table
(p_table_name VARCHAR2, p_deptnumber NUMBER,
p_deptname VARCHAR2, p_location VARCHAR2)
IS
v_cur_hdl INTEGER;
v_stmt_str VARCHAR2(200);
v_rows_processed BINARY_INTEGER;
BEGIN
v_stmt_str := 'INSERT INTO '
|| p_table_name
|| ' VALUES (:g_deptno, :g_dname, :g_loc)';
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str,
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE (v_cur_hdl,':g_deptno',
p_deptnumber);
DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':g_dname',
p_deptname);
DBMS_SQL.BIND_VARIABLE (v_cur_hdl, ':g_loc',
p_location);
v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/
|
CREATE OR REPLACE PROCEDURE insert_into_table
(p_table_name VARCHAR2, p_deptnumber NUMBER,
p_deptname VARCHAR2, p_location VARCHAR2)
IS
v_stmt_str VARCHAR2(200);
BEGIN
v_stmt_str := 'INSERT INTO '
|| p_table_name
|| ' VALUES (:g_deptno, :g_dname, :g_loc)';
EXECUTE IMMEDIATE v_stmt_str
USING p_deptnumber, p_deptname,
p_location;
END;
/
|
Native dynamic SQL in PL/SQL performs comparably to static SQL because the PL/SQL interpreter has built-in support. Programs that use native dynamic SQL are much faster than programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent DBMS_SQL calls. Of course, performance gains may vary depending on your application.
Native dynamic SQL bundles the statement preparation, binding, and execution steps into a single operation, which minimizes the data copying and procedure call overhead and improves performance.
The DBMS_SQL package is based on a procedural API and incurs high procedure call and data copy overhead. Each time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for use during execution. Each time you execute a fetch, the data is copied into the space managed by the DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead.
When using either native dynamic SQL or the DBMS_SQL package, you can improve performance by using bind variables because bind variables allow Oracle Database to share a single cursor for multiple SQL statements.
In Example 8-10 the native dynamic SQL code uses a parameter instead of a bind variable to construct the SQL statement.
Example 8-10 Using Native Dynamic SQL Without Bind Variables
CREATE OR REPLACE PROCEDURE del_dept (p_department_id departments.department_id%TYPE) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM departments WHERE department_id = ' || TO_CHAR(p_department_id); END; / SHOW ERRORS;
For each distinct p_department_id parameter, the procedure creates a new cursor, which causes resource contention and poor performance. Instead, you can construct the SQL statement by using a bind variable, as shown in Example 8-11.
Example 8-11 Using Native Dynamic SQL with Bind Variables
CREATE OR REPLACE PROCEDURE del_dept
(p_department_id departments.department_id%TYPE)
IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM departments WHERE department_id = :1'
USING p_department_id;
END;
/
SHOW ERRORS;
In Example 8-11 the same cursor is reused for different values of the bind my_deptno, which improves performance and scalability.
Native dynamic SQL supports all of the types supported by static SQL in PL/SQL, including user-defined types such as user-defined objects, collections, and REFs. The DBMS_SQL package does not support these user-defined types.
Note:
TheDBMS_SQL package provides limited support for arrays. Refer to the Oracle Database PL/SQL Packages and Types Reference for information.Native dynamic SQL and static SQL both support fetching into records, but the DBMS_SQL package does not. With native dynamic SQL, the rows resulting from a query can be directly fetched into PL/SQL records. In Example 8-12 the rows from a query are fetched into the v_emp_rec variable.
Example 8-12 Using Native Dynamic SQL to Fetch into Records
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record emp%ROWTYPE;
v_stmt_str VARCHAR2(200);
v_e_job emp.job%TYPE;
BEGIN
v_stmt_str := 'SELECT * FROM emp WHERE job = :1';
-- in a multi-row query
OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
LOOP
FETCH v_emp_cursor INTO emp_record;
EXIT WHEN v_emp_cursor%NOTFOUND;
END LOOP;
CLOSE v_emp_cursor;
-- in a single-row query
EXECUTE IMMEDIATE v_stmt_str INTO emp_record USING 'PRESIDENT';
END;
/
The DBMS_SQL package provides the following advantages over native dynamic SQL:
The DBMS_SQL package is supported in client-side programs, but native dynamic SQL is not. Every call to the DBMS_SQL package from the client-side program translates to a PL/SQL remote procedure call (RPC). These calls occur when you need to do any of the following:
Bind a variable
Define a variable
Execute a statement
Native dynamic SQL does not support statements with an unknown number of inputs or outputs. The DBMS_SQL package does not have this limitation. One consequence is that you can use the DESCRIBE_COLUMNS procedure in the DBMS_SQL package to describe columns for a cursor opened and parsed through DBMS_SQL. This feature is similar to the DESCRIBE command in SQL*Plus. Native dynamic SQL does not have a DESCRIBE facility.
See Also:
Oracle Database PL/SQL Packages and Types Reference for an example of usingDESCRIBE_COLUMNS to create a query in a situation where the SELECT list is not known until runtimeThe DBMS_SQL package supports SQL statements larger than 32 KB. Native dynamic SQL does not.
The PARSE procedure in the DBMS_SQL package parses a SQL statement once. After the initial parsing, you can use the statement multiple times with different sets of bind arguments.
Native dynamic SQL prepares a SQL statement each time the statement is used, which typically involves parsing, optimization, and plan generation. Although the extra prepare operations incur a small performance penalty, the decrease in speed is typically outweighed by the performance benefits of native dynamic SQL.
The following examples illustrate the code differences necessary to complete operations with the DBMS_SQL package and native dynamic SQL. Specifically, the following types of examples are presented:
Query
DML operation
DML returning operation
In general, the native dynamic SQL code is more readable and compact, which can improve developer productivity.
The following example includes a dynamic query statement with one bind variable (:g_jobname) and two select columns (ename and sal):
v_stmt_str := 'SELECT ename, sal
FROM emp
WHERE job = :g_jobname';
This example queries for employees with the job description SALESMAN in the job column of the emp table. Table 8-2 shows sample code that accomplishes this query using the DBMS_SQL package and native dynamic SQL.
Table 8-2 Querying Using the DBMS_SQL Package and Native Dynamic SQL
| DBMS_SQL Query Operation | Native Dynamic SQL Query Operation |
|---|---|
DECLARE
v_stmt_str VARCHAR2(200);
v_cur_hdl INT;
v_rows_processed INT;
v_name VARCHAR2(10);
v_salary INT;
BEGIN
v_cur_hdl := DBMS_SQL.OPEN_CURSOR; -- open cursor
v_stmt_str := 'SELECT ename, sal
FROM emp
WHERE job = :g_jobname';
DBMS_SQL.PARSE(v_cur_hdl,v_stmt_str,DBMS_SQL.NATIVE);
-- Supply binds (bind by name)
DBMS_SQL.BIND_VARIABLE(v_cur_hdl, 'g_jobname',
'SALESMAN');
-- Describe defines
DBMS_SQL.DEFINE_COLUMN(v_cur_hdl, 1, v_name, 200);
DBMS_SQL.DEFINE_COLUMN(v_cur_hdl, 2, v_salary);
-- Execute
v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
LOOP
-- Fetch a row
IF DBMS_SQL.FETCH_ROWS(v_cur_hdl) > 0 THEN
-- Fetch columns from the row
DBMS_SQL.COLUMN_VALUE(v_cur_hdl, 1, v_name);
DBMS_SQL.COLUMN_VALUE(v_cur_hdl, 2, v_salary);
-- Process
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl); -- close cursor
END;
/
|
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
v_stmt_str VARCHAR2(200);
v_name VARCHAR2(20);
v_salary NUMBER;
BEGIN
v_stmt_str := 'SELECT ename, sal
FROM emp
WHERE job = :1';
OPEN v_emp_cursor FOR v_stmt_str
USING 'SALESMAN';
LOOP
FETCH v_emp_cursor
INTO v_name, v_salary;
EXIT WHEN v_emp_cursor%NOTFOUND;
-- Process data
END LOOP;
CLOSE v_emp_cursor;
END;
/
|
The following example includes a dynamic INSERT statement for a table with three columns.
v_stmt_str := 'INSERT INTO dept_new VALUES (:g_deptno, :g_dname, :g_loc)';
This example inserts a new row for which the column values are in the PL/SQL variables deptnumber, deptname, and location. Table 8-3 shows sample code that accomplishes this task with the DBMS_SQL package and native dynamic SQL.
Table 8-3 DML Operation Using the DBMS_SQL Package and Native Dynamic SQL
| DBMS_SQL DML Operation | Native Dynamic SQL DML Operation |
|---|---|
DECLARE
v_stmt_str VARCHAR2(200);
v_cur_hdl NUMBER;
v_deptnumber NUMBER := 99;
v_deptname VARCHAR2(20);
v_location VARCHAR2(10);
v_rows_processed NUMBER;
BEGIN
v_stmt_str := 'INSERT INTO dept
VALUES (:g_deptno, :g_dname, :g_loc)';
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(v_cur_hdl, v_stmt_str,
DBMS_SQL.NATIVE);
-- Supply binds
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_deptno', v_deptnumber);
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_dname', v_deptname);
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_loc', v_location);
v_rows_processed := DBMS_SQL.EXECUTE(v_cur_hdl);
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/
|
DECLARE
v_stmt_str VARCHAR2(200);
v_deptnumber NUMBER := 99;
v_deptname VARCHAR2(20);
v_location VARCHAR2(10);
BEGIN
v_stmt_str := 'INSERT INTO dept
VALUES (:g_deptno, :g_dname, :g_loc)';
EXECUTE IMMEDIATE v_stmt_str
USING v_deptnumber, v_deptname,
v_location;
END;
/
|
The following example uses a dynamic UPDATE statement to update the location of a department, then returns the name of the department:
v_stmt_str := 'UPDATE dept_new
SET loc = :g_newloc
WHERE deptno = :g_deptno
RETURNING dname INTO :g_dname';
Table 8-4 shows sample code that accomplishes this operation using both the DBMS_SQL package and native dynamic SQL.
Table 8-4 DML Returning Operation Using the DBMS_SQL Package and Native Dynamic SQL
| DBMS_SQL DML Returning Operation | Native Dynamic SQL DML Returning Operation |
|---|---|
DECLARE
deptname_array DBMS_SQL.VARCHAR2_TABLE;
v_cur_hdl INT;
v_stmt_str VARCHAR2(200);
v_location VARCHAR2(20);
v_deptnumber NUMBER := 10;
v_rows_procsd NUMBER;
BEGIN
v_stmt_str := 'UPDATE dept
SET loc = :g_newloc
WHERE deptno = :g_deptno
RETURNING dname INTO :g_dname';
v_cur_hdl := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE
(v_cur_hdl, v_stmt_str, DBMS_SQL.NATIVE);
-- Supply binds
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_newloc', v_location);
DBMS_SQL.BIND_VARIABLE
(v_cur_hdl, ':g_deptno', v_deptnumber);
DBMS_SQL.BIND_ARRAY
(v_cur_hdl, ':g_dname', deptname_array);
-- Execute cursor
v_rows_procsd := DBMS_SQL.EXECUTE(v_cur_hdl);
-- Get RETURNING column into OUT bind array
DBMS_SQL.VARIABLE_VALUE
(v_cur_hdl, ':g_dname', deptname_array);
DBMS_SQL.CLOSE_CURSOR(v_cur_hdl);
END;
/
|
DECLARE
deptname_array DBMS_SQL.VARCHAR2_TABLE;
v_stmt_str VARCHAR2(200);
v_location VARCHAR2(20);
v_deptnumber NUMBER := 10;
v_deptname VARCHAR2(20);
BEGIN
v_stmt_str := 'UPDATE dept
SET loc = :g_newloc
WHERE deptno = :g_deptno
RETURNING dname INTO :g_dname';
EXECUTE IMMEDIATE v_stmt_str
USING v_location, v_deptnumber, OUT
v_deptname;
END;
/
|
SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements. The purpose of this technique is to gain unauthorized access to a database in order to query or manipulate restricted data. This section describes SQL injection vulnerabilities in PL/SQL and explains how you can guard against them.
This section contains the following topics:
Although SQL injection techniques differ, they all exploit a single vulnerability: string input is not correctly validated and is concatenated into a dynamic SQL statement. For the purposes of this discussion, SQL injection attacks can be classified as follows:
The following sections describe these techniques. To try the examples in your sample database, run the script in Example 8-13.
Example 8-13 Setup for Injection Examples
CONNECT hr/hr
SET SERVEROUTPUT ON
SET LINESIZE 150
SET ECHO OFF
DROP TABLE user_pwd;
CREATE TABLE user_pwd( username VARCHAR2(100), password VARCHAR2(100) );
INSERT INTO user_pwd VALUES('whitehat', 'secret');
DROP TABLE delemp;
CREATE TABLE delemp AS SELECT * FROM employees;
COMMIT;
SQL modification involves deliberately altering a dynamic SQL statement so that it executes in a way unintended by the application developer. Typically, the user retrieves unauthorized data by changing the WHERE clause of a query or by inserting a UNION ALL clause. The classic example of this technique is bypassing password authentication by making a WHERE clause always TRUE.
Suppose a Web form prompts a user to enter a username and password. When the user clicks Submit, the form invokes a PL/SQL stored procedure that concatenates the username and password entered in the form to build a dynamic SQL statement. The procedure executes the query to authenticate the user.
Example 8-14 illustrates an authentication procedure that you can test in SQL*Plus. The ckpwd procedure uses concatenation to build a SQL query of the user_pwd table. If the user enters a username and password stored in the table, then the execution of the query retrieves a single row and the user is authenticated. The ckpwd procedure also displays the concatenated query so that you can see which query is executed.
Example 8-14 ckpwd Procedure
CREATE OR REPLACE PROCEDURE ckpwd (p_user IN VARCHAR2, p_pass IN VARCHAR2)
IS
v_query VARCHAR2(100);
v_output NUMBER;
BEGIN
v_query := q'{SELECT COUNT(*) FROM user_pwd }'
|| q'{WHERE username = '}'
|| p_user
|| q'{' AND password = '}'
|| p_pass
|| q'{'}';
DBMS_OUTPUT.PUT_LINE(CHR(10)||'Built the following query:'||CHR(10)||v_query);
EXECUTE IMMEDIATE v_query
INTO v_output;
IF v_output = 1 THEN
DBMS_OUTPUT.PUT_LINE(CHR(10)||p_user||' is authenticated');
ELSE
DBMS_OUTPUT.PUT_LINE(CHR(10)||'access denied');
END IF;
END;
/
Suppose that the user whitehat enters the password secret in a Web form. You can simulate this scenario by invoking the code shown in Example 8-15 in SQL*Plus to authenticate whitehat (sample output included).
Example 8-15 Authenticating a User with the ckpwd Procedure
BEGIN
ckpwd
(
p_user => q'{whitehat}',
p_pass => q'{secret}'
);
END;
/
Built the following query:
SELECT COUNT(*) FROM user_pwd WHERE username = 'whitehat' AND password = 'secret'
whitehat is authenticated
A malicious user could exploit the concatenation vulnerability and enter the username x in the Web-based form and the text shown in Example 8-16 as a password.
You can simulate this scenario by executing the code in Example 8-17 in SQL*Plus (sample output included).
Example 8-17 Performing Statement Modification with the ckpwd Procedure
BEGIN
ckpwd
(
p_user => q'{x}',
p_pass => q'{x' OR 'x' = 'x}'
);
END;
/
Built the following query:
SELECT COUNT(*) FROM user_pwd WHERE username = 'x' AND password = 'x' OR 'x' = 'x'
x is authenticated
By using the cleverly constructed password in Example 8-16, you alter the concatenated SQL statement so that the OR condition always returns TRUE. Thus, the query of the user_pwd table always succeeds no matter which username is entered.
Statement injection occurs when a user appends one or more new SQL statements to a dynamically generated SQL statement. Anonymous PL/SQL blocks are vulnerable to this technique.
Suppose a Web form prompts a user to enter a username and password. When the user clicks Submit, the form invokes a PL/SQL stored procedure that concatenates the username and password entered in the form into an anonymous PL/SQL block. The procedure then executes the anonymous block to authenticate the user.
Example 8-18 illustrates an authentication procedure that you can test in SQL*Plus. The call_ckpwd procedure uses concatenation to build an anonymous block that invokes the ckpwd procedure from Example 8-14. If the user enters a username and password stored in the user_pwd table, then the execution of the block retrieves a single row and the user is authenticated. The call_ckpwd procedure also prints the concatenated text so that you can see which block is executed.
Example 8-18 call_ckpwd Procedure
CREATE OR REPLACE PROCEDURE call_ckpwd (p_user IN VARCHAR2, p_pass IN VARCHAR2)
IS
v_block VARCHAR2(100);
BEGIN
v_block := q'{BEGIN ckpwd( '}'
|| p_user
|| q'{' , '}'
|| p_pass
|| q'{' ); END; }';
DBMS_OUTPUT.PUT_LINE(CHR(10)||
'Built the following anonymous block:'||CHR(10)||v_block);
EXECUTE IMMEDIATE v_block;
END;
/
Suppose that the user whitehat enters the password secret in a Web-based form. You can simulate this scenario by invoking the call_ckpwd procedure shown in Example 8-19 in SQL*Plus (sample output included).
Example 8-19 Authenticating a User with the call_ckpwd Procedure
BEGIN
call_ckpwd
(
p_user => q'{whitehat}',
p_pass => q'{secret}'
);
END;
/
Built the following anonymous block:
BEGIN ckpwd( 'whitehat' , 'secret' ); END;
Built the following query:
SELECT COUNT(*) FROM user_pwd WHERE username = 'whitehat' AND password = 'secret'
whitehat is authenticated
If whitehat turns bad, then he could enter the string shown in Example 8-20 as the password in a Web form.
Example 8-20 Bogus Password Entered in Form
secret'); DELETE FROM hr.delemp WHERE UPPER('x') = UPPER('x
You can simulate this technique by invoking the call_ckpwd procedure shown in Example 8-21 in SQL*Plus (sample output included).
Example 8-21 Performing Statement Injection with the call_ckpwd Procedure
BEGIN
call_ckpwd
(
p_user => q'{whitehat}',
p_pass => q'{secret'); DELETE FROM hr.delemp WHERE UPPER('x') = UPPER('x}'
);
END;
/
Built the following anonymous block:
BEGIN ckpwd( 'whitehat' , 'secret' ); DELETE FROM hr.delemp WHERE UPPER('x') = UPPER('x'); END;
Built the following query:
SELECT COUNT(*) FROM user_pwd WHERE username = 'whitehat' AND password = 'secret'
whitehat is authenticated
The bogus password in Example 8-20 causes the system to authenticate whitehat and silently execute the injected DELETE statement. A query of the delemp table shows that the injected statement silently removed all rows from the table:
SELECT * FROM delemp; no rows selected
If you use dynamic SQL in your PL/SQL applications, then you must check the input text to ensure that it is exactly and only what is expected. You have the following useful techniques at your disposal:
"Improving Performance Through Bind Variables" shows how you can use bind variables to improve performance in dynamic SQL. Besides improving performance, binding placeholders renders your PL/SQL code immune to SQL injection attacks.
The ckpwd procedure shown in Example 8-14 used concatenation instead of bind variables. Example 8-22 shows how you could rewrite the procedure to use bind variables instead of concatenation.
Example 8-22 ckpwd_bind Procedure
CREATE OR REPLACE PROCEDURE ckpwd_bind (p_user IN VARCHAR2, p_pass IN VARCHAR2)
IS
v_query VARCHAR2(100);
v_output NUMBER;
BEGIN
v_query :=
q'{SELECT COUNT(*) FROM user_pwd WHERE username = :1 AND password = :2}';
DBMS_OUTPUT.PUT_LINE(CHR(10)||'Built the following query:'||CHR(10)||v_query);
EXECUTE IMMEDIATE v_query
INTO v_output
USING p_user, p_pass;
IF v_output = 1 THEN
DBMS_OUTPUT.PUT_LINE(CHR(10)||p_user||' is authenticated');
ELSE
DBMS_OUTPUT.PUT_LINE(CHR(10)||'access denied');
END IF;
END;
/
If the user tries to pass the bogus password shown in Example 8-16 to the ckpwd_bind procedure, then the technique fails to authenticate the user. You can execute the block shown in Example 8-23 in SQL*Plus to test the revised version of the code (sample output included).
Example 8-23 Preventing Statement Modification with the ckpwd_bind Procedure
BEGIN
ckpwd_bind
(
p_user => q'{x}',
p_pass => q'{x' OR 'x' = 'x}'
);
END;
/
Built the following query:
SELECT COUNT(*) FROM user_pwd WHERE username = :1 AND password = :2
access denied
The same binding technique fixes the vulnerable call_ckpwd procedure shown in Example 8-18. By using bind variables exclusively in your code, you avoid concatenating SQL statements and thereby prevent malicious users from altering or injecting additional statements. Oracle database uses the value of the bind variable exclusively and does not interpret its contents in any way. This technique is the most effective way to prevent SQL injection in PL/SQL programs.
A program should always validate user input to ensure that it is what is intended. For example, if the user is passing in a department number for a DELETE statement, then check the validity of this department number by querying the departments table. Similarly, if a user enters the name of a table to be deleted, check that this table exists by querying the ALL_TABLES view.
