In the database, a nested table is a column type that stores an unspecified number of rows in no particular order.
When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is . The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.
The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements.
An uninitialized nested table variable is a null collection. You must initialize it, either by making it empty or by assigning a non- value to it. For details, see "Collection Constructors" and "Assigning Values to Collection Variables".
Example 5-5 Nested Table of Local Type
This example defines a local nested table type, declares a variable of that type (initializing it with a constructor), and defines a procedure that prints the nested table. (The procedure uses the collection methods and , described in "Collection Methods".) The example invokes the procedure three times: After initializing the variable, after changing the value of one element, and after using a constructor to the change the values of all elements. After the second constructor invocation, the nested table has only two elements. Referencing element 3 would raise error ORA-06533.DECLARE TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type -- nested table variable initialized with constructor: names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh'); PROCEDURE print_names (heading VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(heading); FOR i IN names.FIRST .. names.LAST LOOP -- For first to last element DBMS_OUTPUT.PUT_LINE(names(i)); END LOOP; DBMS_OUTPUT.PUT_LINE('---'); END; BEGIN print_names('Initial Values:'); names(3) := 'P Perez'; -- Change value of one element print_names('Current Values:'); names := Roster('A Jansen', 'B Gupta'); -- Change entire table print_names('Current Values:'); END; /
Result:Initial Values: D Caruso J Hamil D Piro R Singh --- Current Values: D Caruso J Hamil P Perez R Singh --- Current Values: A Jansen B Gupta
Example 5-6 Nested Table of Standalone Type
This example defines a standalone nested table type, , and a standalone procedure to print a variable of that type, . An anonymous block declares a variable of type , initializing it to empty with a constructor, and invokes twice: After initializing the variable and after using a constructor to the change the values of all elements.CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER; / CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) AUTHID DEFINER IS i NUMBER; BEGIN i := nt.FIRST; IF i IS NULL THEN DBMS_OUTPUT.PUT_LINE('nt is empty'); ELSE WHILE i IS NOT NULL LOOP DBMS_OUTPUT.PUT('nt.(' || i || ') = '); DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt(i)), 'NULL')); i := nt.NEXT(i); END LOOP; END IF; DBMS_OUTPUT.PUT_LINE('---'); END print_nt; / DECLARE nt nt_type := nt_type(); -- nested table variable initialized to empty BEGIN print_nt(nt); nt := nt_type(90, 9, 29, 58); print_nt(nt); END; /
Result:nt is empty --- nt.(1) = 90 nt.(2) = 9 nt.(3) = 29 nt.(4) = 58 ---
Important Differences Between Nested Tables and Arrays
Conceptually, a nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in these important ways:
An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
An array is always dense. A nested array is dense initially, but it can become sparse, because you can delete elements from it.
Figure 5-2 shows the important differences between a nested table and an array.
Appropriate Uses for Nested Tables
A nested table is appropriate when:
The number of elements is not set.
Index values are not consecutive.
You must delete or update some elements, but not all elements simultaneously.
Nested table data is stored in a separate store table, a system-generated database table. When you access a nested table, the database joins the nested table with its store table. This makes nested tables suitable for queries and updates that affect only some elements of the collection.
You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.
This chapter describes how to use native dynamic SQL (dynamic SQL for short) with PL/SQL to make your programs more flexible, by building and processing SQL statements at run time.
With dynamic SQL, you can directly execute most types of SQL statement, including data definition and data control statements. You can build statements in which you do not know table names, clauses, and other information in advance.
Why Use Dynamic SQL with PL/SQL?
Dynamic SQL enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation.
To process most dynamic SQL statements, you use the statement. To process a multi-row query ( statement), you use the , , and statements.
You need dynamic SQL in the following situations:
You want to execute a SQL data definition statement (such as ), a data control statement (such as ), or a session control statement (such as ). Unlike , , and statements, these statements cannot be included directly in a PL/SQL program.
You want more flexibility. For example, you might want to pass the name of a schema object as a parameter to a procedure. You might want to build different search conditions for the clause of a statement.
You want to issue a query where you do not know the number, names, or datatypes of the columns in advance. In this case, you use the package rather than the statement.
If you have older code that uses the package, the techniques described in this chapter using and generally provide better performance, more readable code, and extra features such as support for objects and collections.
For a comparison of dynamic SQL with , see Oracle Database Application Developer's Guide - Fundamentals. For information on the package, see Oracle Database PL/SQL Packages and Types Reference.
Note:Native dynamic SQL using the and statements is faster and requires less coding than the package. However, the package should be used in these situations:
Using the EXECUTE IMMEDIATE Statement in PL/SQL
The statement prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block. The main argument to is the string containing the SQL statement to execute. You can build up the string using concatenation, or use a predefined string.
Except for multi-row queries, the dynamic string can contain any SQL statement or any PL/SQL block. The string can also contain placeholders, arbitrary names preceded by a colon, for bind arguments. In this case, you specify which PL/SQL variables correspond to the placeholders with the , , and clauses.
When constructing a single SQL statement in a dynamic string, do not include a semicolon (;) at the end inside the quotation mark. When constructing a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block; there will be a semicolon immediately before the end of the string literal, and another following the closing single quotation mark.
You can only use placeholders in places where you can substitute variables in the SQL statement, such as conditional tests in clauses. You cannot use placeholders for the names of schema objects. For the right way, see "Passing Schema Object Names As Parameters".
Used only for single-row queries, the clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the clause.
Used only for DML statements that have a clause (without a clause), the clause specifies the variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable in the clause.
You can place all bind arguments in the clause. The default parameter mode is . For DML statements that have a clause, you can place arguments in the clause without specifying the parameter mode. If you use both the clause and the clause, the clause can contain only arguments.
At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the clause and/or clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (, , and ). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".
Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections, s, instances of an object type, and refs.
As a rule, dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be Booleans or associative arrays. The only exception is that a PL/SQL record can appear in the clause.
You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. However, you incur some overhead because re-prepares the dynamic string before every execution.
For more information on , see "EXECUTE IMMEDIATE Statement".
Example 7-1 illustrates several uses of dynamic SQL.
Example 7-1 Examples of Dynamic SQLCREATE OR REPLACE PROCEDURE raise_emp_salary (column_value NUMBER, emp_column VARCHAR2, amount NUMBER) IS v_column VARCHAR2(30); sql_stmt VARCHAR2(200); BEGIN -- determine if a valid column name has been 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 ' || v_column || ' = :2'; EXECUTE IMMEDIATE sql_stmt USING amount, column_value; IF SQL%ROWCOUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('Salaries have been updated for: ' || emp_column || ' = ' || column_value); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Invalid Column: ' || emp_column); END raise_emp_salary; / DECLARE plsql_block VARCHAR2(500); BEGIN -- note the semi-colons (;) inside the quotes '...' plsql_block := 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;'; EXECUTE IMMEDIATE plsql_block USING 110, 'DEPARTMENT_ID', 10; EXECUTE IMMEDIATE 'BEGIN raise_emp_salary(:cvalue, :cname, :amt); END;' USING 112, 'EMPLOYEE_ID', 10; END; / DECLARE sql_stmt VARCHAR2(200); v_column VARCHAR2(30) := 'DEPARTMENT_ID'; dept_id NUMBER(4) := 46; dept_name VARCHAR2(30) := 'Special Projects'; mgr_id NUMBER(6) := 200; loc_id NUMBER(4) := 1700; BEGIN -- note that there is no semi-colon (;) inside the quotes '...' EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; sql_stmt := 'INSERT INTO departments VALUES (:1, :2, :3, :4)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, mgr_id, loc_id; EXECUTE IMMEDIATE 'DELETE FROM departments WHERE ' || v_column || ' = :num' USING dept_id; EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE'; EXECUTE IMMEDIATE 'DROP TABLE bonus'; END; /
In Example 7-2, a standalone procedure accepts the name of a database table and an optional -clause condition. If you omit the condition, the procedure deletes all rows from the table. Otherwise, the procedure deletes only those rows that meet the condition.
Example 7-2 Dynamic SQL Procedure that Accepts Table Name and WHERE ClauseCREATE TABLE employees_temp AS SELECT * FROM employees; CREATE OR REPLACE PROCEDURE delete_rows ( table_name IN VARCHAR2, condition IN VARCHAR2 DEFAULT NULL) AS where_clause VARCHAR2(100) := ' WHERE ' || condition; v_table VARCHAR2(30); BEGIN -- first make sure that the table actually exists; if not, raise an exception SELECT OBJECT_NAME INTO v_table FROM USER_OBJECTS WHERE OBJECT_NAME = UPPER(table_name) AND OBJECT_TYPE = 'TABLE'; IF condition IS NULL THEN where_clause := NULL; END IF; EXECUTE IMMEDIATE 'DELETE FROM ' || v_table || where_clause; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Invalid table: ' || table_name); END; / BEGIN delete_rows('employees_temp', 'employee_id = 111'); END; /
Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings
With the clause, the mode defaults to , so you do not need to specify a parameter mode for input bind arguments.
With the clause, the mode is , so you cannot specify a parameter mode for output bind arguments.
You must specify the parameter mode in more complicated cases, such as this one where you call a procedure from a dynamic PL/SQL block:CREATE PROCEDURE create_dept ( deptid IN OUT NUMBER, dname IN VARCHAR2, mgrid IN NUMBER, locid IN NUMBER) AS BEGIN SELECT departments_seq.NEXTVAL INTO deptid FROM dual; INSERT INTO departments VALUES (deptid, dname, mgrid, locid); END; /
To call the procedure from a dynamic PL/SQL block, you must specify the mode for the bind argument associated with formal parameter , as shown in Example 7-3.
Example 7-3 Using IN OUT Bind Arguments to Specify SubstitutionsDECLARE plsql_block VARCHAR2(500); new_deptid NUMBER(4); new_dname VARCHAR2(30) := 'Advertising'; new_mgrid NUMBER(6) := 200; new_locid NUMBER(4) := 1700; BEGIN plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;'; EXECUTE IMMEDIATE plsql_block USING IN OUT new_deptid, new_dname, new_mgrid, new_locid; END; /
Using Bulk Dynamic SQL in PL/SQL
Bulk SQL passes entire collections back and forth, not just individual elements. This technique improves performance by minimizing the number of context switches between the PL/SQL and SQL engines. You can use a single statement instead of a loop that issues a SQL statement in every iteration.
Using the following commands, clauses, and cursor attribute, your applications can construct bulk SQL statements, then execute them dynamically at run time:
The static versions of these statements, clauses, and cursor attribute are discussed in "Reducing Loop Overhead for DML Statements and Queries with Bulk SQL". Refer to that section for background information.
Using Dynamic SQL with Bulk SQL
Bulk binding lets Oracle bind a variable in a SQL statement to a collection of values. The collection type can be any PL/SQL collection type: index-by table, nested table, or varray. The collection elements must have a SQL datatype such as , , or . Three statements support dynamic bulk binds: , , and .
You can use the clause with the statement to store values from each column of a query's result set in a separate collection.
You can use the clause with the statement to store the results of an , , or statement in a set of collections.
You can use the clause with the statement to store values from each column of a cursor in a separate collection.
You can put an statement with the inside a statement. You can store the results of all the , , or statements in a set of collections.
You can pass subscripted collection elements to the statement through the clause. You cannot concatenate the subscripted elements directly into the string argument to ; for example, you cannot build a collection of table names and write a statement where each iteration applies to a different table.
Examples of Dynamic Bulk Binds
This sections contains examples of dynamic bulk binds.You can bind define variables in a dynamic query using the clause. As shown in Example 7-4, you can use that clause in a bulk or bulk statement.
Example 7-4 Dynamic SQL with BULK COLLECT INTO ClauseDECLARE TYPE EmpCurTyp IS REF CURSOR; TYPE NumList IS TABLE OF NUMBER; TYPE NameList IS TABLE OF VARCHAR2(25); emp_cv EmpCurTyp; empids NumList; enames NameList; sals NumList; BEGIN OPEN emp_cv FOR 'SELECT employee_id, last_name FROM employees'; FETCH emp_cv BULK COLLECT INTO empids, enames; CLOSE emp_cv; EXECUTE IMMEDIATE 'SELECT salary FROM employees' BULK COLLECT INTO sals; END; /
Only , , and statements can have output bind variables. You bulk-bind them with the clause of , as shown in Example 7-5.
Example 7-5 Dynamic SQL with RETURNING BULK COLLECT INTO ClauseDECLARE TYPE NameList IS TABLE OF VARCHAR2(15); enames NameList; 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 statement and clause, as shown in Example 7-6. The SQL statement cannot be a query.
Example 7-6 Dynamic SQL Inside FORALL StatementDECLARE 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.
Building a Dynamic Query with Dynamic SQL
You use three statements to process a dynamic multi-row query: , , and . First, you a cursor variable a multi-row query. Then, you rows from the result set one at a time. When all the rows are processed, you the cursor variable. For more information about cursor variables, see "Using Cursor Variables (REF CURSORs)".
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.
When building up a PL/SQL anonymous block, include the semicolon at the end of each PL/SQL statement and at the end of the anonymous block. For example:BEGIN EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''semicolons''); END;'; END; /
Improving Performance of Dynamic SQL with Bind Variables
When you code , , , and statements directly in PL/SQL, PL/SQL turns the variables into bind variables automatically, to make the statements work efficiently with SQL. When you build up such statements in dynamic SQL, you need to specify the bind variables yourself to get the same performance.
In the following example, Oracle opens a different cursor for each distinct value of . This can lead to resource contention and poor performance as each statement is parsed and cached.CREATE PROCEDURE fire_employee (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id = ' || TO_CHAR(emp_id); END; /
You can improve performance by using a bind variable, which allows Oracle to reuse the same cursor for different values of :CREATE PROCEDURE fire_employee (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id = :id' USING emp_id; END; /
Passing Schema Object Names As Parameters
Suppose you need a procedure that accepts the name of any database table, then drops that table from your schema. You must build a string with a statement that includes the object names, then use to execute the statement:CREATE TABLE employees_temp AS SELECT last_name FROM employees; CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS BEGIN EXECUTE IMMEDIATE 'DROP TABLE ' || table_name; END; /
Use concatenation to build the string, rather than trying to pass the table name as a bind variable through the clause.
In addition, if you need to call a procedure whose name is unknown until runtime, you can pass a parameter identifying the procedure. For example, the following procedure can call another procedure () by specifying the procedure name when executed.CREATE PROCEDURE run_proc (proc_name IN VARCHAR2, table_name IN VARCHAR2) ASBEGIN EXECUTE IMMEDIATE 'CALL "' || proc_name || '" ( :proc_name )' using table_name; END; /
If you want to drop a table with the procedure, you can run the procedure as follows. Note that the procedure name is capitalized.CREATE TABLE employees_temp AS SELECT last_name FROM employees; BEGIN run_proc('DROP_TABLE', 'employees_temp'); END; /
Using Duplicate Placeholders with Dynamic SQL
Placeholders in a dynamic SQL statement are associated with bind arguments in the clause by position, not by name. If you specify a sequence of placeholders like , you must include four items in the clause. For example, given the dynamic string
the fact that the name X is repeated is not significant. You can code the corresponding clause with four different bind variables:
If the dynamic statement represents a PL/SQL block, the rules for duplicate placeholders are different. Each unique placeholder maps to a single item in the clause. If the same placeholder appears two or more times, all references to that name correspond to one bind argument in the clause. In Example 7-7, all references to the placeholder are associated with the first bind argument a, and the second unique placeholder is associated with the second bind argument b.
Example 7-7 Using Duplicate Placeholders With Dynamic SQLCREATE 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; END; /
Using Cursor Attributes with Dynamic SQL
The SQL cursor attributes , , , and work when you issue an , , , or single-row statement in dynamic SQL:BEGIN EXECUTE IMMEDIATE 'DELETE FROM employees WHERE employee_id > 1000'; DBMS_OUTPUT.PUT_LINE('Number of employees deleted: ' || TO_CHAR(SQL%ROWCOUNT)); END; /
Likewise, when appended to a cursor variable name, the cursor attributes return information about the execution of a multi-row query:
Example 7-8 Accessing %ROWCOUNT For an Explicit CursorDECLARE TYPE cursor_ref IS REF CURSOR; c1 cursor_ref; TYPE emp_tab IS TABLE OF employees%ROWTYPE; rec_tab emp_tab; rows_fetched NUMBER; BEGIN OPEN c1 FOR 'SELECT * FROM employees'; FETCH c1 BULK COLLECT INTO rec_tab; rows_fetched := c1%ROWCOUNT; DBMS_OUTPUT.PUT_LINE('Number of employees fetched: ' || TO_CHAR(rows_fetched)); END; /
For more information about cursor attributes, see "Managing Cursors in PL/SQL".
Passing Nulls to Dynamic SQL
The literal is not allowed in the clause. To work around this restriction, replace the keyword with an uninitialized variable:CREATE TABLE employees_temp AS SELECT * FROM EMPLOYEES; DECLARE a_null CHAR(1); -- set to NULL automatically at run time BEGIN EXECUTE IMMEDIATE 'UPDATE employees_temp SET commission_pct = :x' USING a_null; END; /
Using Database Links with Dynamic SQL
PL/SQL subprograms can execute dynamic SQL statements that use database links to refer to objects on remote databases:CREATE PROCEDURE delete_dept (db_link VARCHAR2, dept_id INTEGER) IS BEGIN EXECUTE IMMEDIATE 'DELETE FROM departments@' || db_link || ' WHERE department_id = :num' USING dept_id; END; / -- delete department id 41 in the departments table on the remote DB hr_db CALL delete_dept('hr_db', 41);
The targets of remote procedure calls (RPCs) can contain dynamic SQL statements. For example, suppose the following standalone function, which returns the number of rows in a table, resides on the database in London:CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER AS rows NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows; RETURN rows; END; / -- From an anonymous block, you might call the function remotely, as follows: DECLARE emp_count INTEGER; BEGIN emp_count := row_count@hr_db('employees'); DBMS_OUTPUT.PUT_LINE(emp_count); END; /
Using Invoker Rights with Dynamic SQL
Dynamic SQL lets you write schema-management procedures that can be centralized in one schema, and can be called from other schemas and operate on the objects in those schemas. For example, this procedure can drop any kind of database object:CREATE OR REPLACE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AUTHID CURRENT_USER AS BEGIN EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name; END; /
Let's say that this procedure is part of the schema. Without the clause, the procedure would always drop objects in the schema, regardless of who calls it. Even if you pass a fully qualified object name, this procedure would not have the privileges to make changes in other schemas.
The clause lifts both of these restrictions. It lets the procedure run with the privileges of the user that invokes it, and makes unqualified references refer to objects in that user's schema.
For details, see "Using Invoker's Rights Versus Definer's Rights (AUTHID Clause)".
Using Pragma RESTRICT_REFERENCES with Dynamic SQL
A function called from SQL statements must obey certain rules meant to control side effects. (See "Controlling Side Effects of PL/SQL Subprograms".) To check for violations of the rules, you can use the pragma . The pragma asserts that a function does not read or write database tables or package variables. (For more information, See Oracle Database Application Developer's Guide - Fundamentals.)
If the function body contains a dynamic , , or statement, the function always violates the rules () and (). PL/SQL cannot detect those side-effects automatically, because dynamic SQL statements are checked at run time, not at compile time. In an statement, only the clause can be checked at compile time for violations of .
Avoiding Deadlocks with Dynamic SQL
In a few situations, executing a SQL data definition statement results in a deadlock. For example, the following procedure causes a deadlock because it attempts to drop itself. To avoid deadlocks, never try to or a subprogram or package while you are still using it.CREATE OR REPLACE PROCEDURE calc_bonus (emp_id NUMBER) AS BEGIN EXECUTE IMMEDIATE 'DROP PROCEDURE calc_bonus'; -- deadlock! END; /
Backward Compatibility of the USING Clause
When a dynamic , , or statement has a clause, output bind arguments can go in the clause or the clause. In new applications, use the clause. In old applications, you can continue to use the clause.
Using Dynamic SQL With PL/SQL Records and Collections
You can use dynamic SQL with records and collections. As shown in Example 7-9, you can fetch rows from the result set of a dynamic multi-row query into a record:
Example 7-9 Dynamic SQL Fetching into a RecordDECLARE TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; emp_rec employees%ROWTYPE; sql_stmt VARCHAR2(200); v_job VARCHAR2(10) := 'ST_CLERK'; BEGIN sql_stmt := 'SELECT * FROM employees WHERE job_id = :j'; OPEN emp_cv FOR sql_stmt USING v_job; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.last_name || ' Job Id: ' || emp_rec.job_id); END LOOP; CLOSE emp_cv; END; /
For an example of using dynamic SQL with object types, see "Using Dynamic SQL With Objects".