Download Oracle Database PL/SQL User's Guide and Reference

Guidelines for Using Dynamic SQL with PL/SQL
bonus_amt NUMBER := 50;
sql_stmt VARCHAR(200);
BEGIN
sql_stmt := 'UPDATE employees SET salary = salary + :1
RETURNING last_name INTO :2';
EXECUTE IMMEDIATE sql_stmt
USING bonus_amt RETURNING BULK COLLECT INTO enames;
END;
/
To bind the input variables in a SQL statement, you can use the FORALL statement and
USING clause, as shown in Example 7–6. The SQL statement cannot be a query.
Example 7–6 Dynamic SQL Inside FORALL Statement
DECLARE
TYPE NumList IS TABLE OF NUMBER;
TYPE NameList IS TABLE OF VARCHAR2(15);
empids NumList;
enames NameList;
BEGIN
empids := NumList(101,102,103,104,105);
FORALL i IN 1..5
EXECUTE IMMEDIATE
'UPDATE employees SET salary = salary * 1.04 WHERE employee_id = :1
RETURNING last_name INTO :2'
USING empids(i) RETURNING BULK COLLECT INTO enames;
END;
/
Guidelines for Using Dynamic SQL with PL/SQL
This section shows you how to take full advantage of dynamic SQL and how to avoid
some common pitfalls.
When using dynamic SQL with PL/SQL, be aware of the risks
of SQL injection, which is a possible security issue. For more
information on SQL injection and possible problems, see Oracle
Database Application Developer's Guide - Fundamentals. You can also
search for "SQL injection" on the Oracle Technology Network at
http://www.oracle.com/technology/
Note:
Building a Dynamic Query with Dynamic SQL
You use three statements to process a dynamic multi-row query: OPEN-FOR, FETCH,
and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH
rows from the result set one at a time. When all the rows are processed, you CLOSE the
cursor variable. For more information about cursor variables, see "Using Cursor
Variables (REF CURSORs)" on page 6-20.
When to Use or Omit the Semicolon with Dynamic SQL
When building up a single SQL statement in a string, do not include any semicolon at
the end.
Performing SQL Operations with Native Dynamic SQL
7-7