9
Dynamic SQL

Dynamic SQL is a programming technique that 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. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

In past releases of Oracle, the only way to implement dynamic SQL in a PL/SQL application was by using the DBMS_SQL package. Oracle8i introduces native dynamic SQL, an alternative to the DBMS_SQL package. Using native dynamic SQL, you can place dynamic SQL statements directly into PL/SQL blocks.

This chapter covers the following topics:

• What Is Dynamic SQL?

• When to Use Dynamic SQL

• A Dynamic SQL Scenario Using Native Dynamic SQL

• Native Dynamic SQL vs. the DBMS_SQL Package

• Application Development Languages Other Than PL/SQL

What Is Dynamic SQL?

Dynamic SQL enables you to write programs that reference SQL statements whose full text is not known until runtime. Before discussing dynamic SQL in detail, a clear definition of static SQL may provide a good starting point for understanding dynamic SQL. Static SQL statements do not change from execution to execution. The full text of static SQL statements are known at compilation, which provides the following benefits:

• Successful compilation verifies that the SQL statements reference valid database objects.

• Successful compilation verifies that the necessary privileges are in place to access the database objects.

• Performance of static SQL is generally better than dynamic SQL.

Because of these advantages, you should use dynamic SQL only if you cannot use static SQL to accomplish your goals, or if using static SQL is cumbersome compared to dynamic SQL. However, static SQL has limitations that can be overcome with dynamic SQL. You may not always know the full text of the SQL statements that must be executed in a PL/SQL procedure. Your program may accept user input that defines the SQL statements to execute, or your program may need to complete some processing work to determine the correct course of action. In such cases, you should use dynamic SQL.

For example, consider a reporting application that performs standard queries on tables in a data warehouse environment where the exact table name is unknown until runtime. To accommodate the large amount of data in the data warehouse efficiently, you create a new table every quarter to store the invoice information for the quarter. These tables all have exactly the same definition and are named according to the starting month and year of the quarter, for example INV_01_1997, INV_04_1997, INV_07_1997, INV_10_1997, INV_01_1998, etc. In such a case, you can use dynamic SQL in your reporting application to specify the table name at runtime.

With static SQL, all of the data definition information, such as table definitions, referenced by the SQL statements in your program must be known at compilation. If the data definition changes, you must change and recompile the program. Dynamic SQL programs can handle changes in data definition information, because the SQL statements can change "on the fly" at runtime. Therefore, dynamic SQL is much more flexible than static SQL. Dynamic SQL enables you to write application code that is reusable because the code defines a process that is independent of the specific SQL statements used.

In addition, dynamic SQL lets you execute SQL statements that are not supported in static SQL programs, such as data definition language (DDL) statements. Support for these statements allows you to accomplish more with your PL/SQL programs.

When to Use Dynamic SQL

You should use dynamic SQL in cases where static SQL does not support the operation you want to perform, or in cases where you do not know the exact SQL statements that must be executed by a PL/SQL procedure. These SQL statements may depend on user input, or they may depend on processing work done by the program. The following sections describe typical situations where you should use dynamic SQL and typical problems that can be solved by using dynamic SQL.

To Execute Dynamic DML Statements

You can use dynamic SQL to execute DML statements in which the exact SQL statement is not known until runtime. For examples, see the DML examples in the "Examples of DBMS_SQL Package Code and Native Dynamic SQL Code" and "Sample DML Operation".

To Execute Statements Not Supported by Static SQL in PL/SQL

In PL/SQL, you cannot execute the following types of statements using static SQL:

• Data definition language (DDL) statements, such as CREATE, DROP, GRANT, and REVOKE

• Session control language (SCL) statements, such as ALTER SESSION and SET ROLE

  See Also: Oracle8i SQL Reference for information about DDL and SCL statements.

Use dynamic SQL if you need to execute any of these types of statements within a PL/SQL block.

In addition, static SQL in PL/SQL does not allow the use of the TABLE clause in the SELECT statements. There is no such limitation in dynamic SQL. For example, the following PL/SQL block contains a SELECT statement that uses the TABLE clause and native dynamic SQL:

CREATE TYPE t_emp AS OBJECT (id NUMBER, name VARCHAR2(20))
/
CREATE TYPE t_emplist AS TABLE OF t_emp
/

CREATE TABLE dept_new (id NUMBER, emps t_emplist)
    NESTED TABLE emps STORE AS emp_table;

INSERT INTO dept_new VALUES (
    10, 
    t_emplist(
        t_emp(1, 'SCOTT'),
        t_emp(2, 'BRUCE')));

DECLARE
    deptid NUMBER;
    ename  VARCHAR2(20);
BEGIN
    EXECUTE IMMEDIATE 'SELECT d.id, e.name
        FROM dept_new d, TABLE(d.emps) e  -- not allowed in static SQL
                                          -- in PL/SQL
        WHERE e.id = 1'
        INTO deptid, ename;
END;
/

To Execute Dynamic Queries

You can use dynamic SQL to create applications that execute dynamic queries, which are queries whose full text is not known until runtime. Many types of applications need to use dynamic queries, including:

• Applications that allow users to input or choose query search or sorting criteria at runtime

• Applications that allow users to input or choose optimizer hints at run time

• Applications that query a database where the data definitions of tables are constantly changing

• Applications that query a database where new tables are created often

For examples, see "Query Example", and see the query examples in "A Dynamic SQL Scenario Using Native Dynamic SQL".

To Reference Database Objects that Do Not Exist at Compilation

Many types of applications must interact with data that is generated periodically. For example, it may be possible to determine the definition of the database tables at compilation, but not the names of the tables, because new tables are being generated periodically. Your application needs to access the data, but there is no way to know the exact names of the tables until runtime.

Dynamic SQL can solve this problem, because dynamic SQL allows you to wait until runtime to specify the table names you need to access. For example, in the sample data warehouse application discussed in "What Is Dynamic SQL?", new tables are generated every quarter, and these tables always have the same definition. In this case, you might allow a user to specify the name of the table at runtime with a dynamic SQL query similar to the following:

CREATE OR REPLACE PROCEDURE query_invoice(
       month VARCHAR2, 
       year VARCHAR2) IS
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    query_str VARCHAR2(200);
    inv_num NUMBER;
    inv_cust VARCHAR2(20);
    inv_amt NUMBER;
BEGIN
    query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year 
      || ' WHERE invnum = :id';
    OPEN c FOR query_str USING inv_num;
    LOOP
        FETCH c INTO inv_num, inv_cust, inv_amt;
        EXIT WHEN c%NOTFOUND;
        -- process row here
    END LOOP;
    CLOSE c;
END;
/

To Optimize Execution Dynamically

If you use static SQL, you must decide at compilation how you want to construct your SQL statements, whether to have hints in your statements, and, if you include hints, exactly which hints to have. However, you can use dynamic SQL to build a SQL statement in a way that optimizes the execution and/or concatenates the hints into a SQL statement dynamically. This allows you to change the hints based on your current database statistics, without requiring recompilation.

For example, the following procedure uses a variable called a_hint to allow users to pass a hint option to the SELECT statement:

CREATE OR REPLACE PROCEDURE query_emp
      (a_hint VARCHAR2) AS
   TYPE cur_typ IS REF CURSOR;
   c cur_typ;
BEGIN
   OPEN c FOR 'SELECT ' || a_hint ||
      ' empno, ename, sal, job FROM emp WHERE empno = 7566';
      -- process
END;
/

In this example, the user can pass any of the following values for a_hint:

• a_hint = '/*+ ALL_ROWS */'

• a_hint = '/*+ FIRST_ROWS */'

• a_hint = '/*+ CHOOSE */'

• Any other valid hint option

  See Also: Oracle8i Tuning for more information about using hints.

To Invoke Dynamic PL/SQL Blocks

You can use the EXECUTE IMMEDIATE statement to invoke anonymous PL/SQL blocks. The ability to invoke dynamic PL/SQL blocks can be useful for application extension and customization where the module to be executed is determined dynamically at runtime.

For example, suppose you want to write an application that takes an event number and dispatches 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 would be to implement the dispatcher as a switch statement, as shown below, where the code handles each event by making a static call to its appropriate handler.

CREATE OR REPLACE PROCEDURE event_handler_1(param number) AS BEGIN 
   -- process event
   RETURN; 
END;
/

CREATE OR REPLACE PROCEDURE event_handler_2(param number) AS BEGIN 
   -- process event
   RETURN; 
END;
/

CREATE OR REPLACE PROCEDURE event_handler_3(param number) AS BEGIN 
   -- process event
   RETURN; 
END;
/

CREATE OR REPLACE PROCEDURE event_dispatcher
   (event number, param number) IS
BEGIN
  IF (event = 1) THEN
    EVENT_HANDLER_1(param);
  ELSIF (event = 2) THEN
    EVENT_HANDLER_2(param);
  ELSIF (event = 3) THEN
    EVENT_HANDLER_3(param);
  END IF;
END;
/

This code is not very extensible because the dispatcher code must be updated whenever a handler for a new event is added. However, using native dynamic SQL, you can write an extensible event dispatcher similar to the following:

CREATE OR REPLACE PROCEDURE event_dispatcher
   (event NUMBER, param NUMBER) IS
BEGIN
  EXECUTE IMMEDIATE
     'BEGIN
        EVENT_HANDLER_' || to_char(event) || '(:1);
     END;'
  USING param;
END;
/

To Perform Dynamic Operations Using Invoker-Rights

By using the invoker-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-rights and dynamic SQL, enable you to build reusable application subcomponents that can operate on and access the invoker's data and modules.

A Dynamic SQL Scenario Using Native Dynamic SQL

The scenario described in this section illustrates the power and flexibility of native dynamic SQL. This scenario includes examples that show you how to perform the following operations using native dynamic SQL:

• Execute DDL and DML operations

• Execute single row and multiple row queries

Data Model

The database in this scenario is a company's human resources database (named hr) with the following data model:

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, while 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.

Sample DML Operation

The following native dynamic SQL procedure gives a raise to all employees with a particular job title:

CREATE OR REPLACE PROCEDURE salary_raise (raise_percent NUMBER, job VARCHAR2) IS
    TYPE loc_array_type IS TABLE OF VARCHAR2(40)
        INDEX BY binary_integer;
    dml_str VARCHAR2		(200);
    loc_array	 loc_array_type;
BEGIN
    -- bulk fetch the list of office locations
    SELECT location BULK COLLECT INTO loc_array
        FROM offices;
    -- for each location, give a raise to employees with the given 'job' 
    FOR i IN loc_array.first..loc_array.last LOOP
        dml_str := 'UPDATE emp_' || loc_array(i) 
        || ' SET sal = sal * (1+(:raise_percent/100))'
        || ' WHERE job = :job_title';
    EXECUTE IMMEDIATE dml_str USING raise_percent, job;
    END LOOP;
END;
/

Sample DDL Operation

The EXECUTE IMMEDIATE statement can perform DDL operations. For example, the following procedure adds an office location:

CREATE OR REPLACE PROCEDURE add_location (loc VARCHAR2) IS
BEGIN
    -- insert new location in master table
    INSERT INTO offices VALUES (loc);
    -- create an employee information table
    EXECUTE IMMEDIATE  
    'CREATE TABLE ' || 'emp_' || loc ||
    '( 
        empno   NUMBER(4) NOT NULL,
        ename   VARCHAR2(10),
        job     VARCHAR2(9),
        sal     NUMBER(7,2),
        deptno  NUMBER(2) 
    )';
END;
/

The following procedure deletes an office location:

CREATE OR REPLACE PROCEDURE drop_location (loc VARCHAR2) IS
BEGIN
    -- delete the employee table for location 'loc'
    EXECUTE IMMEDIATE 'DROP TABLE ' || 'emp_' || loc;
    -- remove location from master table
    DELETE FROM offices WHERE location = loc;
END;
/

Sample Dynamic Single-Row Query

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:

CREATE OR REPLACE FUNCTION get_num_of_employees (loc VARCHAR2, job VARCHAR2) 
    RETURN NUMBER IS
    query_str VARCHAR2(1000);
    num_of_employees NUMBER;
BEGIN
    query_str := 'SELECT COUNT(*) FROM '
        || ' emp_' || loc
        || ' WHERE job = :job_title';
    EXECUTE IMMEDIATE query_str
        INTO num_of_employees
        USING job;
    RETURN num_of_employees;
END;
/

Sample Dynamic Multiple-Row Query

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:

CREATE OR REPLACE PROCEDURE list_employees(loc VARCHAR2, job VARCHAR2) IS
    TYPE cur_typ IS REF CURSOR;
    c           cur_typ;
    query_str   VARCHAR2(1000);
    emp_name    VARCHAR2(20);
    emp_num     NUMBER;
BEGIN
    query_str := 'SELECT ename, empno FROM emp_' || loc 
        || ' WHERE job = :job_title';
    -- find employees who perform the specified job
    OPEN c FOR query_str USING job;
    LOOP
        FETCH c INTO emp_name, emp_num;
        EXIT WHEN c%NOTFOUND;
        -- process row here
    END LOOP;
    CLOSE c;
END;
/

Native Dynamic SQL vs. the DBMS_SQL Package

Oracle provides two methods for using dynamic SQL within PL/SQL: native dynamic SQL and the DBMS_SQL package. Native dynamic SQL enables you to place dynamic SQL statements directly into PL/SQL code. These dynamic statements include DML statements (including queries), PL/SQL anonymous blocks, DDL statements, transaction control statements, and session control statements.

To process most native dynamic SQL statements, you use the EXECUTE IMMEDIATE statement. However, to process a multi-row query (SELECT statement), you use OPEN-FOR, FETCH, and CLOSE statements.

The DBMS_SQL package is a PL/SQL library that offers a programmatic API to execute SQL statements dynamically. The DBMS_SQL package has programmatic interfaces to open a cursor, parse a cursor, supply binds, etc. Programs that use the DBMS_SQL package make calls to this package to perform dynamic SQL operations.

The following sections provide detailed information about the advantages of both methods.

Advantages of Native Dynamic SQL

Native dynamic SQL provides the following advantages over the DBMS_SQL package:

Ease of Use

Native dynamic SQL is much simpler to use than the DBMS_SQL package. Because native dynamic SQL is integrated with SQL, you can use it in the same way that you currently use static SQL within PL/SQL code. In addition, native dynamic SQL code is typically more compact and readable than equivalent code that uses the DBMS_SQL package.

The DBMS_SQL package is not as easy to use as native dynamic SQL. There are many procedures and functions that must be used in a strict sequence. Typically, performing simple operations requires a large amount of code when you use the DBMS_SQL package. You can avoid this complexity by using native dynamic SQL instead.

Table 9-1 illustrates the difference in the amount of code required to perform the same operation using the DBMS_SQL package and native dynamic SQL.

Table 9-1 Code Comparison of DBMS_SQL Package and Native Dynamic SQL

CREATE PROCEDURE insert_into_table (
      table_name  VARCHAR2, 
      deptnumber  NUMBER, 
      deptname    VARCHAR2, 
      location    VARCHAR2) IS
   cur_hdl         INTEGER;
   stmt_str        VARCHAR2(200);
   rows_processed  BINARY_INTEGER;

BEGIN
   stmt_str := 'INSERT INTO ' || 
      table_name || ' VALUES 
      (:deptno, :dname, :loc)';

   -- open cursor
   cur_hdl := dbms_sql.open_cursor;

   -- parse cursor
   dbms_sql.parse(cur_hdl, stmt_str,
      dbms_sql.native);

   -- supply binds
   dbms_sql.bind_variable
      (cur_hdl, ':deptno', deptnumber);
   dbms_sql.bind_variable
      (cur_hdl, ':dname', deptname);
   dbms_sql.bind_variable
      (cur_hdl, ':loc', location);

    -- execute cursor
    rows_processed := 
    dbms_sql.execute(cur_hdl);

    -- close cursor
    dbms_sql.close_cursor(cur_hdl);

END;
/

CREATE PROCEDURE insert_into_table (
      table_name  VARCHAR2, 
      deptnumber  NUMBER, 
      deptname    VARCHAR2, 
      location    VARCHAR2) IS
   stmt_str    VARCHAR2(200);

BEGIN
   stmt_str := 'INSERT INTO ' || 
      table_name || ' values 
      (:deptno, :dname, :loc)';

   EXECUTE IMMEDIATE stmt_str 
      USING 
      deptnumber, deptname, location;

END;
/

Performance Improvements

The performance of native dynamic SQL in PL/SQL is comparable to the performance of static SQL because the PL/SQL interpreter has built-in support for native dynamic SQL. Therefore, the performance of programs that use native dynamic SQL is much better than that of programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent statements that use the DBMS_SQL package. Of course, your performance gains may vary depending on your application.

The DBMS_SQL package is based on a procedural API and, as a result, incurs high procedure call and data copy overhead. For example, every time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for later use during execution. Similarly, every time you execute a fetch, first 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 resulting from data copying. In contrast, 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.

Performance Tip

When using either native dynamic SQL or the DBMS_SQL package, you can improve performance by using bind variables, because using bind variables allows Oracle to share a single cursor for multiple SQL statements.

For example, the following native dynamic SQL code does not use bind variables:

CREATE OR REPLACE PROCEDURE del_dept (
   my_deptno  dept.deptno%TYPE) IS
BEGIN
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = ' || to_char (my_deptno);
END;
/

For each distinct my_deptno variable, a new cursor is created, which can cause resource contention and poor performance. Instead, bind my_deptno as a bind variable, as in the following example:

CREATE OR REPLACE PROCEDURE del_dept (
   my_deptno  dept.deptno%TYPE) IS
BEGIN
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :1' USING my_deptno;
END;
/

Here, the same cursor is reused for different values of the bind my_deptno, thereby improving performance and scalabilty.

Support for User-Defined Types

Native dynamic SQL supports all of the types supported by static SQL in PL/SQL. Therefore, native dynamic SQL provides support for user-defined types, such as user-defined objects, collections, and REFs. The DBMS_SQL package does not support these user-defined types.

Support for Fetching Into Records

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 the following example, the rows from a query are fetched into the emp_rec record:

DECLARE
    TYPE EmpCurTyp IS REF CURSOR;
    c EmpCurTyp;
    emp_rec emp%ROWTYPE;
    stmt_str VARCHAR2(200);
    e_job emp.job%TYPE;

BEGIN
   stmt_str := 'SELECT * FROM emp WHERE job = :1';
    -- in a multi-row query
    OPEN c FOR stmt_str USING 'MANAGER';
    LOOP
        FETCH c INTO emp_rec;
        EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
    -- in a single-row query
    EXECUTE IMMEDIATE stmt_str INTO emp_rec USING 'PRESIDENT';

END;
/

Advantages of the DBMS_SQL Package

The DBMS_SQL package provides the following advantages over native dynamic SQL:

Support for Client-Side Programs

Currently, 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 bind a variable, define a variable, or execute a statement.

Support for DESCRIBE

The DESCRIBE_COLUMNS procedure in the DBMS_SQL package can be used to describe the columns for a cursor opened and parsed through DBMS_SQL. The functionality is similar to the DESCRIBE command in SQL*Plus. Native dynamic SQL does not have a DESCRIBE facility.

Support for Bulk Dynamic SQL

Bulk SQL is the ability to process multiple rows of data in a single DML statement. Bulk SQL improves performance by reducing the amount of context switching between SQL and the host language. Currently, the DBMS_SQL package supports bulk dynamic SQL.

Although there is no direct support for bulk operations in native dynamic SQL, you can simulate a native dynamic bulk SQL statement by placing the bulk SQL statement in a 'BEGIN ... END' block and executing the block dynamically. This workaround enables you to realize the benefits of bulk SQL within a native dynamic SQL program. For example, the following native dynamic SQL code copies the ename column of one table to another:

CREATE TYPE name_array_type IS
   VARRAY(100) of VARCHAR2(50)
/

CREATE OR REPLACE PROCEDURE copy_ename_column
   (table1 VARCHAR2, table2 VARCHAR2) IS
   ename_col  NAME_ARRAY_TYPE;
BEGIN
  -- bulk fetch the 'ename' column into a VARRAY of VARCHAR2s.
  EXECUTE IMMEDIATE
     'BEGIN
        SELECT ename BULK COLLECT INTO :tab
           FROM ' || table1 || ';
      END;'
   USING OUT ename_col;

   -- bulk insert the 'ename' column into another table. 
   EXECUTE IMMEDIATE
      'BEGIN
         FORALL i IN :first .. :last
            INSERT INTO ' || table2 || ' VALUES (:tab(i));
      END;' 
   USING ename_col.first, ename_col.last, ename_col;
END;
/

Multiple Row Updates and Deletes with a RETURNING Clause

The DBMS_SQL package supports statements with a RETURNING clause that update or delete multiple rows. Native dynamic SQL only supports a RETURNING clause if a single row is returned.

Support for SQL Statements Larger than 32KB

The DBMS_SQL package supports SQL statements larger than 32KB; native dynamic SQL does not.

Reuse of SQL Statements

The PARSE procedure in the DBMS_SQL package parses a SQL statement once. After the initial parsing, the statement can be used multiple times with different sets of bind arguments.

In contrast, native dynamic SQL prepares a SQL statement for execution each time the statement is used. Statement preparation typically involves parsing, optimization, and plan generation. Preparing a statement each time it is used incurs a small performance penalty. However, Oracle's shared cursor mechanism minimizes the cost, and the performance penalty is typically trivial when compared to the performance benefits of native dynamic SQL.

Examples of DBMS_SQL Package Code and Native Dynamic SQL Code

The following examples illustrate the differences in the code necessary to complete operations with the DBMS_SQL package and native dynamic SQL. Specifically, the following types of examples are presented:

• A query

• A DML operation

• A DML returning operation

In general, the native dynamic SQL code is more readable and compact, which can improve developer productivity.

Query Example

The following example includes a dynamic query statement with one bind variable (:jobname) and two select columns (ename and sal):

stmt_str := 'SELECT ename, sal FROM emp WHERE job = :jobname';

This example queries for employees with the job description SALESMAN in the job column of the emp table. Table 9-2 shows sample code that accomplishes this query using the DBMS_SQL package and native dynamic SQL.

Table 9-2 Querying Using the DBMS_SQL Package and Native Dynamic SQL

DECLARE  
  stmt_str varchar2(200);
  cur_hdl int;
  rows_processed int;
  name varchar2(10);
  salary int;
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor 
stmt_str := 'SELECT ename, sal FROM emp WHERE 
job = :jobname';
dbms_sql.parse(cur_hdl, stmt_str, dbms_
sql.native); 

-- supply binds (bind by name) 
dbms_sql.bind_variable(
  cur_hdl, 'jobname', 'SALESMAN'); 

-- describe defines 
dbms_sql.define_column(cur_hdl, 1, name, 200); 
dbms_sql.define_column(cur_hdl, 2, salary); 

rows_processed := dbms_sql.execute(cur_hdl); -- 
execute 

LOOP 
  -- fetch a row 
  IF dbms_sql.fetch_rows(cur_hdl) > 0 then 

    -- fetch columns from the row 
    dbms_sql.column_value(cur_hdl, 1, name); 
    dbms_sql.column_value(cur_hdl, 2, salary); 

    -- <process data> 

      ELSE
        EXIT; 
      END IF; 
END LOOP; 
dbms_sql.close_cursor(cur_hdl); -- close cursor
END;
/

DECLARE
  TYPE EmpCurTyp IS REF CURSOR;
  cur EmpCurTyp;
  stmt_str VARCHAR2(200);
  name VARCHAR2(20);
  salary NUMBER;
BEGIN
  stmt_str := 'SELECT ename, sal FROM emp 
    WHERE job = :1';
  OPEN cur FOR stmt_str USING 'SALESMAN'; 

LOOP
  FETCH cur INTO name, salary; 
  EXIT WHEN cur%NOTFOUND; 
  -- <process data>  
END LOOP; 
CLOSE cur;
END;
/

DML Example

The following example includes a dynamic INSERT statement for a table with three columns:

stmt_str := 'INSERT INTO dept_new VALUES (:deptno, :dname, :loc)';

This example inserts a new row for which the column values are in the PL/SQL variables deptnumber, deptname, and location. Table 9-3 shows sample code that accomplishes this DML operation using the DBMS_SQL package and native dynamic SQL.

Table 9-3 DML Operation Using the DBMS_SQL Package and Native Dynamic SQL

DECLARE
  stmt_str VARCHAR2(200);
  cur_hdl NUMBER;
  deptnumber NUMBER := 99;
  deptname VARCHAR2(20);
  location VARCHAR2(10);
  rows_processed NUMBER;
BEGIN
  stmt_str := 'INSERT INTO dept_new VALUES 
    (:deptno, :dname, :loc)';
  cur_hdl := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(
    cur_hdl, stmt_str, DBMS_SQL.NATIVE);
    -- supply binds
  DBMS_SQL.BIND_VARIABLE
    (cur_hdl, ':deptno', deptnumber);
  DBMS_SQL.BIND_VARIABLE
    (cur_hdl, ':dname', deptname);
  DBMS_SQL.BIND_VARIABLE
    (cur_hdl, ':loc', location);
    rows_processed := dbms_sql.execute(cur_hdl);  
    -- execute
  DBMS_SQL.CLOSE_CURSOR(cur_hdl); -- close
END;
/

DECLARE
  stmt_str VARCHAR2(200);
  deptnumber NUMBER := 99;
  deptname VARCHAR2(20);
  location VARCHAR2(10);
BEGIN
  stmt_str := 'INSERT INTO dept_new VALUES  
  (:deptno, :dname, :loc)';
  EXECUTE IMMEDIATE stmt_str 
    USING deptnumber, deptname, location;
END;
/

DML Returning Example

The following example includes a dynamic UPDATE statement that updates the location of a department when given the department number (deptnumber) and a new location (location), and then returns the name of the department:

stmt_str := 'UPDATE dept_new 
            SET loc = :newloc
            WHERE deptno = :deptno
            RETURNING dname INTO :dname';

This example inserts a new row for which the column values are in the PL/SQL variables deptnumber, deptname, and location. Table 9-4 shows sample code that accomplishes this DML returning operation using the DBMS_SQL package and native dynamic SQL.

Table 9-4 DML Returning Operation Using the DBMS_SQL Package and Native Dynamic SQL

DECLARE 
  deptname_array dbms_sql.Varchar2_Table; 
  cur_hdl INT;
  stmt_str VARCHAR2(200);
  location VARCHAR2(20);
  deptnumber NUMBER := 10;
  rows_processed NUMBER;
BEGIN 
 stmt_str := 'UPDATE dept_new 
    SET loc = :newloc
    WHERE deptno = :deptno
    RETURNING dname INTO :dname';
    
  cur_hdl := dbms_sql.open_cursor; 
  dbms_sql.parse
    (cur_hdl, stmt_str, dbms_sql.native); 
  -- supply binds 
  dbms_sql.bind_variable
    (cur_hdl, ':newloc', location); 
  dbms_sql.bind_variable
    (cur_hdl, ':deptno', deptnumber); 
  dbms_sql.bind_array
    (cur_hdl, ':dname', deptname_array); 
  -- execute cursor 
  rows_processed := dbms_sql.execute(cur_hdl); 
  -- get RETURNING column into OUT bind array 
  dbms_sql.variable_value
    (cur_hdl, ':dname', deptname_array); 
  dbms_sql.close_cursor(cur_hdl); 
END;
/

DECLARE 
  deptname_array dbms_sql.Varchar2_Table; 
  stmt_str  VARCHAR2(200);
  location  VARCHAR2(20);
  deptnumber NUMBER := 10;
  deptname   VARCHAR2(20);
BEGIN
  stmt_str := 'UPDATE dept_new 
    SET loc = :newloc
    WHERE deptno = :deptno
    RETURNING dname INTO :dname';
  EXECUTE IMMEDIATE stmt_str 
    USING location, deptnumber, OUT deptname;
END;
/

Application Development Languages Other Than PL/SQL

So far, the discussion in this chapter has been about PL/SQL support for dynamic SQL. However, you can use other application development languages to implement programs that use dynamic SQL. These application development languages include C/C++, COBOL, and Java.

If you use C/C++, you can develop applications that use 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. Similarly, 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.

In the past, the only way to use dynamic SQL in PL/SQL applications was by using the DBMS_SQL package. There are a number of limitations to using this package, including performance concerns. Consequently, application developers may have used one of the alternatives to PL/SQL discussed above to implement dynamic SQL. However, with the introduction of native dynamic SQL in PL/SQL, many of the drawbacks to using PL/SQL for dynamic SQL are now eliminated.

If you have an application that uses OCI, Pro*C/C++, or Pro*COBOL for dynamic SQL execution, the network roundtrips required to perform dynamic SQL operations may hurt performance. Because these applications typically reside on clients, more network calls are required to complete dynamic SQL operations. If you have this type of application, consider moving the dynamic SQL functionality to stored procedures and stored functions in PL/SQL that use native dynamic SQL. Doing so might improve the performance of your application because the stored procedures can reside on the server, thereby eliminating the network overhead. You can then call the PL/SQL stored procedures and stored functions from the application.