Oracle® Database PL/SQL Language Reference 11g Release 1 (11.1) Part Number B28370-02 |
|
|
View PDF |
Dynamic SQL is a programming methodology for generating and executing SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must execute DDL statements, or when you do not know at compilation time the full text of a SQL statement or the number or datatypes of its input and output variables.
PL/SQL provides two ways to write dynamic SQL:
Native dynamic SQL, a PL/SQL language (that is, native) feature for building and executing dynamic SQL statements
DBMS_SQL
package, an API for building, executing, and describing dynamic SQL statements
Native dynamic SQL code is easier to read and write than equivalent code that uses the DBMS_SQL
package, and runs noticeably faster (especially when it can be optimized by the compiler). However, to write native dynamic SQL code, you must know at compile time the number and datatypes of the input and output variables of the dynamic SQL statement. If you do not know this information at compile time, you must use the DBMS_SQL
package.
When you need both the DBMS_SQL
package and native dynamic SQL, you can switch between them, using the "DBMS_SQL.TO_REFCURSOR Function" and "DBMS_SQL.TO_CURSOR_NUMBER Function".
Topics:
In PL/SQL, you need dynamic SQL in order to execute the following:
SQL whose text is unknown at compile time
For example, a SELECT
statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE
clause in which the number of subclauses is unknown at compile time.
SQL that is not supported as static SQL
That is, any SQL construct not included in "Description of Static SQL".
If you do not need dynamic SQL, use static SQL, which has the following advantages:
Successful compilation verifies that static SQL statements reference valid database objects and that the necessary privileges are in place to access those objects.
Successful compilation creates schema object dependencies.
For information about schema object dependencies, see Oracle Database Concepts.
For information about using static SQL statements with PL/SQL, see Chapter 6, "Using Static SQL".
Native dynamic SQL processes most dynamic SQL statements by means of the EXECUTE
IMMEDIATE
statement.
If the dynamic SQL statement is a SELECT
statement that returns multiple rows, native dynamic SQL gives you the following choices:
Use the EXECUTE
IMMEDIATE
statement with the BULK
COLLECT
INTO
clause.
Use the OPEN-FOR
, FETCH
, and CLOSE
statements.
The SQL cursor attributes work the same way after native dynamic SQL INSERT
, UPDATE
, DELETE
, and single-row SELECT
statements as they do for their static SQL counterparts. For more information about SQL cursor attributes, see "Managing Cursors in PL/SQL".
Topics:
The EXECUTE
IMMEDIATE
statement is the means by which native dynamic SQL processes most dynamic SQL statements.
If the dynamic SQL statement is self-contained (that is, if it has no placeholders for bind arguments and the only result that it can possibly return is an error), then the EXECUTE
IMMEDIATE
statement needs no clauses.
If the dynamic SQL statement includes placeholders for bind arguments, each placeholder must have a corresponding bind argument in the appropriate clause of the EXECUTE
IMMEDIATE
statement, as follows:
If the dynamic SQL statement is a SELECT
statement that can return at most one row, put out-bind arguments (defines) in the INTO
clause and in-bind arguments in the USING
clause.
If the dynamic SQL statement is a SELECT
statement that can return multiple rows, put out-bind arguments (defines) in the BULK
COLLECT
INTO
clause and in-bind arguments in the USING
clause.
If the dynamic SQL statement is a DML statement without a RETURNING
INTO
clause, other than SELECT
, put all bind arguments in the USING
clause.
If the dynamic SQL statement is a DML statement with a RETURNING
INTO
clause, put in-bind arguments in the USING
clause and out-bind arguments in the RETURNING
INTO
clause.
If the dynamic SQL statement is an anonymous PL/SQL block or a CALL
statement, put all bind arguments in the USING
clause.
If the dynamic SQL statement invokes a subprogram, ensure that every bind argument that corresponds to a placeholder for a subprogram parameter has the same parameter mode as that subprogram parameter (as in Example 7-1) and that no bind argument has a datatype that SQL does not support (such as BOOLEAN
in Example 7-2).
The USING
clause cannot contain the literal NULL
. To work around this restriction, use an uninitialized variable where you want to use NULL
, as in Example 7-3.
For syntax details of the EXECUTE
IMMEDIATE
statement, see "EXECUTE IMMEDIATE Statement".
Example 7-1 Invoking a Subprogram from a Dynamic PL/SQL Block
-- Subprogram that dynamic PL/SQL block invokes: CREATE PROCEDURE create_dept ( deptid IN OUT NUMBER, dname IN VARCHAR2, mgrid IN NUMBER, locid IN NUMBER ) AS BEGIN
deptid := departments_seq.NEXTVAL; INSERT INTO departments VALUES (deptid, dname, mgrid, locid); END; / DECLARE plsql_block VARCHAR2(500); new_deptid NUMBER(4); new_dname VARCHAR2(30) := 'Advertising'; new_mgrid NUMBER(6) := 200; new_locid NUMBER(4) := 1700; BEGIN -- Dynamic PL/SQL block invokes subprogram: plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;'; /* Specify bind arguments in USING clause. Specify mode for first parameter. Modes of other parameters are correct by default. */ EXECUTE IMMEDIATE plsql_block USING IN OUT new_deptid, new_dname, new_mgrid, new_locid; END; /
Example 7-2 Unsupported Datatype in Native Dynamic SQL
DECLARE
FUNCTION f (x INTEGER)
RETURN BOOLEAN
AS
BEGIN
...
END f;
dyn_stmt VARCHAR2(200);
b1 BOOLEAN;
BEGIN
dyn_stmt := 'BEGIN :b := f(5); END;';
-- Fails because SQL does not support BOOLEAN datatype:
EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
END;
If the dynamic SQL statement represents a SELECT
statement that returns multiple rows, you can process it with native dynamic SQL as follows:
Use an OPEN-FOR
statement to associate a cursor variable with the dynamic SQL statement. In the USING
clause of the OPEN-FOR
statement, specify a bind argument for each placeholder in the dynamic SQL statement.
The USING
clause cannot contain the literal NULL
. To work around this restriction, use an uninitialized variable where you want to use NULL
, as in Example 7-3.
For syntax details, see "OPEN-FOR Statement".
Use the FETCH
statement to retrieve result set rows one at a time, several at a time, or all at once.
For syntax details, see "FETCH Statement".
Use the CLOSE
statement to close the cursor variable.
For syntax details, see "CLOSE Statement".
Example 7-4 lists all employees who are managers, retrieving result set rows one at a time.
Example 7-4 Native Dynamic SQL with OPEN-FOR, FETCH, and CLOSE Statements
DECLARE TYPE EmpCurTyp IS REF CURSOR; v_emp_cursor EmpCurTyp; emp_record employees%ROWTYPE; v_stmt_str VARCHAR2(200); v_e_job employees.job%TYPE; BEGIN -- Dynamic SQL statement with placeholder: v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j'; -- Open cursor & specify bind argument in USING clause: OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER'; -- Fetch rows from result set one at a time: LOOP FETCH v_emp_cursor INTO emp_record; EXIT WHEN v_emp_cursor%NOTFOUND; END LOOP; -- Close cursor: CLOSE v_emp_cursor; END; /
If you repeat placeholder names in dynamic SQL statements, be aware that the way placeholders are associated with bind arguments depends on the kind of dynamic SQL statement.
Topics:
If the dynamic SQL statement does not represent an anonymous PL/SQL block or a CALL
statement, repetition of placeholder names is insignificant. Placeholders are associated with bind arguments in the USING
clause by position, not by name.
For example, in the following dynamic SQL statement, the repetition of the name :x
is insignificant:
sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';
In the corresponding USING
clause, you must supply four bind arguments. They can be different; for example:
EXECUTE IMMEDIATE sql_stmt USING a, b, c, d;
The preceding EXECUTE
IMMEDIATE
statement executes the following SQL statement:
INSERT INTO payroll VALUES (a, b, c, d)
To associate the same bind argument with each occurrence of :x
, you must repeat that bind argument; for example:
EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;
The preceding EXECUTE
IMMEDIATE
statement executes the following SQL statement:
INSERT INTO payroll VALUES (a, a, b, a)
If the dynamic SQL statement represents an anonymous PL/SQL block or a CALL
statement, repetition of placeholder names is significant. Each unique placeholder name must have a corresponding bind argument in the USING
clause. If you repeat a placeholder name, you do not need to repeat its corresponding bind argument. All references to that placeholder name correspond to one bind argument in the USING
clause.
In Example 7-5, all references to the first unique placeholder name, :x
, are associated with the first bind argument in the USING
clause, a
, and the second unique placeholder name, :y
, is associated with the second bind argument in the USING
clause, b
.
Example 7-5 Repeated Placeholder Names in Dynamic PL/SQL Block
CREATE PROCEDURE calc_stats (
w NUMBER,
x NUMBER,
y NUMBER,
z NUMBER )
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
/
DECLARE
a NUMBER := 4;
b NUMBER := 7;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
EXECUTE IMMEDIATE plsql_block USING a, b; -- calc_stats(a, a, b, a)
END;
/
The DBMS_SQL
package defines an entity called a SQL cursor number. Because the SQL cursor number is a PL/SQL integer, you can pass it across call boundaries and store it. You can also use the SQL cursor number to obtain information about the SQL statement that you are executing.
You must use the DBMS_SQL
package to execute a dynamic SQL statement when you don't know either of the following until run-time:
SELECT
list
What placeholders in a SELECT
or DML statement must be bound
In the following situations, you must use native dynamic SQL instead of the DBMS_SQL
package:
The dynamic SQL statement retrieves rows into records.
You want to use the SQL cursor attribute %FOUND
, %ISOPEN
, %NOTFOUND
, or %ROWCOUNT
after issuing a dynamic SQL statement that is an INSERT
, UPDATE
, DELETE
, or single-row SELECT
statement.
For information about native dynamic SQL, see "Using Native Dynamic SQL".
When you need both the DBMS_SQL
package and native dynamic SQL, you can switch between them, using the following:
Note:
You can invokeDBMS_SQL
subprograms remotely.See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about theDBMS_SQL
package, including instructions for executing a dynamic SQL statement that has an unknown number of input or output variables ("Method 4").The DBMS_SQL
.TO_REFCURSOR
function converts a SQL cursor number to a weakly-typed variable of the PL/SQL datatype REF
CURSOR
, which you can use in native dynamic SQL statements.
Before passing a SQL cursor number to the DBMS_SQL
.TO_REFCURSOR
function, you must OPEN
, PARSE
, and EXECUTE
it (otherwise an error occurs).
After you convert a SQL cursor number to a REF
CURSOR
variable, DBMS_SQL
operations can access it only as the REF
CURSOR
variable, not as the SQL cursor number. For example, using the DBMS_SQL
.IS_OPEN
function to see if a converted SQL cursor number is still open causes an error.
Example 7-6 uses the DBMS_SQL
.TO_REFCURSOR
function to switch from the DBMS_SQL
package to native dynamic SQL.
Example 7-6 Switching from DBMS_SQL Package to Native Dynamic SQL
CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200); / CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER; / CREATE OR REPLACE PROCEDURE do_query_1 ( placeholder vc_array, bindvars vc_array, sql_stmt VARCHAR2 ) IS TYPE curtype IS REF CURSOR; src_cur curtype; curid NUMBER; bindnames vc_array; empnos numlist; depts numlist; ret NUMBER; isopen BOOLEAN; BEGIN -- Open SQL cursor number: curid := DBMS_SQL.OPEN_CURSOR; -- Parse SQL cursor number: DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE); bindnames := placeholder; -- Bind arguments: FOR i IN 1 .. bindnames.COUNT LOOP DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i)); END LOOP; -- Execute SQL cursor number: ret := DBMS_SQL.EXECUTE(curid); -- Switch from DBMS_SQL to native dynamic SQL: src_cur := DBMS_SQL.TO_REFCURSOR(curid); FETCH src_cur BULK COLLECT INTO empnos, depts; -- This would cause an error because curid was converted to a REF CURSOR: -- isopen := DBMS_SQL.IS_OPEN(curid); CLOSE src_cur; END; /
The DBMS_SQL
.TO_CURSOR
function converts a REF
CURSOR
variable (either strongly or weakly typed) to a SQL cursor number, which you can pass to DBMS_SQL
subprograms.
Before passing a REF
CURSOR
variable to the DBMS_SQL
.TO_CURSOR
function, you must OPEN
it.
After you convert a REF
CURSOR
variable to a SQL cursor number, native dynamic SQL operations cannot access it.
After a FETCH
operation begins, passing the DBMS_SQL
cursor number to the DBMS_SQL
.TO_REFCURSOR
or DBMS_SQL
.TO_CURSOR
function causes an error.
Example 7-7 uses the DBMS_SQL
.TO_CURSOR
function to switch from native dynamic SQL to the DBMS_SQL
package.
Example 7-7 Switching from Native Dynamic SQL to DBMS_SQL Package
CREATE OR REPLACE PROCEDURE do_query_2 (sql_stmt VARCHAR2) IS TYPE curtype IS REF CURSOR; src_cur curtype; curid NUMBER; desctab DBMS_SQL.DESC_TAB; colcnt NUMBER; namevar VARCHAR2(50); numvar NUMBER; datevar DATE; empno NUMBER := 100; BEGIN -- sql_stmt := SELECT ... FROM employees WHERE employee_id = :b1'; -- Open REF CURSOR variable: OPEN src_cur FOR sql_stmt USING empno; -- Switch from native dynamic SQL to DBMS_SQL package: curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur); DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab); -- Define columns: FOR i IN 1 .. colcnt LOOP IF desctab(i).col_type = 2 THEN DBMS_SQL.DEFINE_COLUMN(curid, i, numvar); ELSIF desctab(i).col_type = 12 THEN DBMS_SQL.DEFINE_COLUMN(curid, i, datevar); -- statements ELSE DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50); END IF; END LOOP; -- Fetch rows with DBMS_SQL package: WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP FOR i IN 1 .. colcnt LOOP IF (desctab(i).col_type = 1) THEN DBMS_SQL.COLUMN_VALUE(curid, i, namevar); ELSIF (desctab(i).col_type = 2) THEN DBMS_SQL.COLUMN_VALUE(curid, i, numvar); ELSIF (desctab(i).col_type = 12) THEN DBMS_SQL.COLUMN_VALUE(curid, i, datevar); -- statements END IF; END LOOP; END LOOP; DBMS_SQL.CLOSE_CURSOR(curid); END; /
SQL injection is a technique for maliciously exploiting applications that use client-supplied data in SQL statements, thereby gaining unauthorized access to a database in order to view or manipulate restricted data. This section describes SQL injection vulnerabilities in PL/SQL and explains how to guard against them.
To try the examples in this topic, connect to the HR
schema and execute the statements in Example 7-8.
Example 7-8 Setup for SQL Injection Examples
CREATE TABLE secret_records ( user_name VARCHAR2(9), service_type VARCHAR2(12), value VARCHAR2(30)); INSERT INTO secret_records VALUES ('Andy', 'Waiter', 'Serve dinner at Cafe Pete'); INSERT INTO secret_records VALUES ('Chuck', 'Merger', 'Buy company XYZ');
Topics:
SQL injection techniques differ, but they all exploit a single vulnerability: string input is not correctly validated and is concatenated into a dynamic SQL statement. This topic classifies SQL injection attacks as follows:
Statement modification means 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 SELECT
statement or by inserting a UNION
ALL
clause. The classic example of this technique is bypassing password authentication by making a WHERE
clause always TRUE
.
The SQL*Plus script in Example 7-9 creates a procedure that is vulnerable to statement modification and then invokes that procedure with and without statement modification. With statement modification, the procedure returns a supposedly secret record.
Example 7-9 Procedure Vulnerable to Statement Modification
SQL> REM Create vulnerable procedure SQL> SQL> CREATE OR REPLACE PROCEDURE get_record (user_name IN VARCHAR2, service_type IN VARCHAR2, record OUT VARCHAR2) IS query VARCHAR2(4000); BEGIN -- Following SELECT statement is vulnerable to modification -- because it uses concatenation to build WHERE clause. query := 'SELECT value FROM secret_records WHERE user_name=''' || user_name || ''' AND service_type=''' || service_type || ''''; DBMS_OUTPUT.PUT_LINE('Query: ' || query); EXECUTE IMMEDIATE query INTO record; DBMS_OUTPUT.PUT_LINE('Record: ' || record); END; / Procedure created. SQL> REM Demonstrate procedure without SQL injection SQL> SQL> SET SERVEROUTPUT ON; SQL> SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_record('Andy', 'Waiter', record_value); 5 END; 6 / Query: SELECT value FROM secret_records WHERE user_name='Andy' AND service_type='Waiter' Record: Serve dinner at Cafe Pete PL/SQL procedure successfully completed. SQL> SQL> REM Example of statement modification SQL> SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_record( 5 'Anybody '' OR service_type=''Merger''--', 6 'Anything', 7 record_value); 8 END; 9 / Query: SELECT value FROM secret_records WHERE user_name='Anybody ' OR service_type='Merger'--' AND service_type='Anything' Record: Buy company XYZ PL/SQL procedure successfully completed. SQL>
Statement injection means that a user appends one or more new SQL statements to a dynamically generated SQL statement. Anonymous PL/SQL blocks are vulnerable to this technique.
The SQL*Plus script in Example 7-10 creates a procedure that is vulnerable to statement injection and then invokes that procedure with and without statement injection. With statement injection, the procedure deletes the supposedly secret record exposed in Example 7-9.
Example 7-10 Procedure Vulnerable to Statement Injection
SQL> REM Create vulnerable procedure SQL> SQL> CREATE OR REPLACE PROCEDURE p 2 (user_name IN VARCHAR2, 3 service_type IN VARCHAR2) 4 IS 5 block VARCHAR2(4000); 6 BEGIN -- Following block is vulnerable to statement injection -- because it is built by concatenation. 7 block := 8 'BEGIN 9 DBMS_OUTPUT.PUT_LINE(''user_name: ' || user_name || ''');' 10 || 'DBMS_OUTPUT.PUT_LINE(''service_type: ' || service_type || '''); 11 END;'; 12 13 DBMS_OUTPUT.PUT_LINE('Block: ' || block); 14 15 EXECUTE IMMEDIATE block; 16 END; 17 / Procedure created. SQL> SQL> REM Demonstrate procedure without SQL injection SQL> SQL> SET SERVEROUTPUT ON; SQL> SQL> BEGIN 2 p('Andy', 'Waiter'); 3 END; 4 / Block: BEGIN DBMS_OUTPUT.PUT_LINE('user_name: Andy'); DBMS_OUTPUT.PUT_LINE('service_type: Waiter'); END; user_name: Andy service_type: Waiter PL/SQL procedure successfully completed. SQL> REM Example of statement modification SQL> SQL> SELECT * FROM secret_records; USER_NAME SERVICE_TYPE VALUE --------- ------------ ------------------------------ Andy Waiter Serve dinner at Cafe Pete Chuck Merger Buy company XYZ 2 rows selected. SQL> SQL> BEGIN 2 p('Anybody', 'Anything''); 3 DELETE FROM secret_records WHERE service_type=INITCAP(''Merger'); 4 END; 5 / Block: BEGIN DBMS_OUTPUT.PUT_LINE('user_name: Anybody'); DBMS_OUTPUT.PUT_LINE('service_type: Anything'); DELETE FROM secret_records WHERE service_type=INITCAP('Merger'); END; user_name: Anybody service_type: Anything PL/SQL procedure successfully completed. SQL> SELECT * FROM secret_records; USER_NAME SERVICE_TYPE VALUE --------- ------------ ------------------------------ Andy Waiter Serve dinner at Cafe Pete 1 row selected. SQL>
If you use dynamic SQL in your PL/SQL applications, you must check the input text to ensure that it is exactly what you expected. You can use the following techniques:
The most effective way to make your PL/SQL code invulnerable to SQL injection attacks is to use bind arguments. Oracle Database uses the values of bind arguments exclusively and does not interpret their contents in any way. (Bind arguments also improve performance.)
The procedure in Example 7-11 is invulnerable to SQL injection because it builds the dynamic SQL statement with bind arguments (not by concatenation as in the vulnerable procedure in Example 7-9). The same binding technique fixes the vulnerable procedure shown in Example 7-10.
Example 7-11 Procedure Invulnerable to SQL Injection
SQL> REM Create invulnerable procedure SQL> SQL> CREATE OR REPLACE PROCEDURE get_record_2 2 (user_name IN VARCHAR2, 3 service_type IN VARCHAR2, 4 record OUT VARCHAR2) 5 IS 6 query VARCHAR2(4000); 7 BEGIN 8 query := 'SELECT value FROM secret_records 9 WHERE user_name=:a 10 AND service_type=:b'; 11 12 DBMS_OUTPUT.PUT_LINE('Query: ' || query); 13 14 EXECUTE IMMEDIATE query INTO record USING user_name, service_type; 15 16 DBMS_OUTPUT.PUT_LINE('Record: ' || record); 17 END; 18 / Procedure created. SQL> REM Demonstrate procedure without SQL injection SQL> SQL> SET SERVEROUTPUT ON; SQL> SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_record_2('Andy', 'Waiter', record_value); 5 END; 6 / Query: SELECT value FROM secret_records WHERE user_name=:a AND service_type=:b Record: Serve dinner at Cafe Pete PL/SQL procedure successfully completed. SQL> SQL> REM Attempt statement modification SQL> SQL> DECLARE 2 record_value VARCHAR2(4000); 3 BEGIN 4 get_record_2('Anybody '' OR service_type=''Merger''--', 5 'Anything', 6 record_value); 7 END; 8 / Query: SELECT value FROM secret_records WHERE user_name=:a AND service_type=:b DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at "HR.GET_RECORD_2", line 14 ORA-06512: at line 4 SQL>
Always have your program validate user input to ensure that it is what is intended. For example, if the user is passing a department number for a DELETE
statement, check the validity of this department number by selecting from the departments
table. Similarly, if a user enters the name of a table to be deleted, check that this table exists by selecting from the static data dictionary view ALL_TABLES
.
Caution:
When checking the validity of a user name and its password, always return the same error regardless of which item is invalid. Otherwise, a malicious user who receives the error message "invalid password" but not "invalid user name" (or the reverse) will realize that he or she has guessed one of these correctly.In validation-checking code, the subprograms in the package DBMS_ASSERT
are often useful. For example, you can use the DBMS_ASSERT
.ENQUOTE_LITERAL
function to enclose a string literal in quotation marks, as Example 7-12 does. This prevents a malicious user from injecting text between an opening quotation mark and its corresponding closing quotation mark.
Caution:
Although theDBMS_ASSERT
subprograms are useful in validation code, they do not replace it. For example, an input string can be a qualified SQL name (verified by DBMS_ASSERT
.QUALIFIED_SQL_NAME
) and still be a fraudulent password.See Also:
Oracle Database PL/SQL Packages and Types Reference for information aboutDBMS_ASSERT
subprograms.In Example 7-12, the procedure raise_emp_salary
checks the validity of the column name that was passed to it before it updates the employees
table, and then the anonymous PL/SQL block invokes the procedure from both a dynamic PL/SQL block and a dynamic SQL statement.
Example 7-12 Dynamic SQL
CREATE OR REPLACE PROCEDURE raise_emp_salary ( column_value NUMBER, emp_column VARCHAR2, amount NUMBER ) IS v_column VARCHAR2(30); sql_stmt VARCHAR2(200); BEGIN -- Check validity of column name that was given as input: SELECT COLUMN_NAME INTO v_column FROM USER_TAB_COLS WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_column; sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' || DBMS_ASSERT.ENQUOTE_NAME(v_column,FALSE) || ' = :2'; EXECUTE IMMEDIATE sql_stmt USING amount, column_value; -- If column name is valid: IF SQL%ROWCOUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('Salaries were updated for: ' || emp_column || ' = ' || column_value); END IF; -- If column name is not valid: EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column); END raise_emp_salary; / DECLARE plsql_block VARCHAR2(500); BEGIN -- Invoke raise_emp_salary from a dynamic PL/SQL block: plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'; EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10; -- Invoke raise_emp_salary from a dynamic SQL statement: EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;' USING 112, 'EMPLOYEE_ID', 10; END; /