DBMS_SQL vs. Native Dynamic SQL

DBMS_SQL vs. Native Dynamic SQL

Although you can use DBMS_SQL to perform dynamic SQL and dynamic PL/SQL, native dynamic SQL is better suited to performing DML, DDL, PL/SQL, and transaction and session control statements for the following reasons:

• 
  

  Native dynamic SQL enables you to place dynamic SQL statements and dynamic PL/SQL blocks directly into PL/SQL code, without the use of an API. This is similar to static SQL, and it's much easier in native dynamic SQL than in DBMS_SQL in terms of coding.

  
  


• 
  

  The "native" in native dynamic SQL refers to the built-in support for dynamic SQL inside the PL/SQL interpreter. Native dynamic SQL is also integrated with SQL, so its performance is on par with static SQL's. There is no API involved, thus the high costs involved in procedure call and data copying when using DBMS_SQL are minimized.
  

  
  


• 
  

  Native dynamic SQL supports the full range of object extensibility features, such as user-defined types and objects including collections and REFs, within PL/SQL. These features aren't supported by the DBMS_SQL package.

  
  


• 
  

  Native dynamic SQL, like static SQL, supports fetching into records of type %ROWTYPE or as defined by the TYPE … IS RECORD … statement.

  
  

So what use is DBMS_SQL then?

• 
  

  DBMS_SQL is well suited to performing dynamic SQL and PL/SQL from client-side applications such as Oracle Developer.

  
  


• 
  

  The maximum length of the dynamic SQL statement is limited to 32KB. Statements larger than this can be handled only by the DBMS_SQL package.

  
  


• 
  

  DBMS_SQL is good when the same SQL statement is executed multiple times in the same session as it reuses the parsed SQL statement. Native dynamic SQL prepares the SQL statement each time it is processed.

  
  

Here's an example of the code involved in creating a table whose name is dynamically constructed using native dynamic SQL and DBMS_SQL:

/* Native Dynamic SQL */
DECLARE
  dyn_tab_name VARCHAR2(30) := 'temp';
  dyn_string VARCHAR2(150);
BEGIN
  dyn_string := 'create table '||dyn_tab_name||' ( col1 NUMBER NOT NULL) ';
  EXECUTE IMMEDIATE dyn_string;
END;
/

You can verify the output of this PL/SQL block as follows:

 SQL> desc temp
 Name                                    Null?    Type
 --------------------------------------- -------  -------------
 COL1                                    NOT NULL NUMBER

Using native dynamic SQL involves just two steps:

1. 
   

   Construct the dynamic SQL statement in the form of string.

   
   


2. 
   

   Execute the SQL statement.

   
   

drop table temp;
-- DBMS_SQL
DECLARE
  dyn_tab_name VARCHAR2(30) := 'temp';
  dyn_string VARCHAR2(150);
  c INTEGER;
  ret_code INTEGER;
BEGIN
  dyn_string := 'create table '||dyn_tab_name||
                                         ' ( col1 NUMBER NOT NULL) ';
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c, dyn_string, DBMS_SQL.V7);
  ret_code := DBMS_SQL.EXECUTE(c);
   DBMS_SQL.CLOSE_CURSOR(c);
END;
/

Using DBMS_SQL involves five steps:

1. 
   

   Construct the dynamic SQL statement in the form of string.

   
   


2. 
   

   Declare a cursor handle and open a cursor for the handle.

   
   


3. 
   

   Parse the dynamically constructed SQL statement and associate the SQL statement with the cursor handle.

   
   


4. 
   

   Execute the SQL statement.

   
   


5. 
   

   Close the cursor handle.

   
   

As you can see, it's much easier to code with native dynamic SQL than with DBMS_SQL. Also, depending on the type of SQL statement, DBMS_SQL involves more steps than native dynamic SQL. For example, to use bind variables, an extra step of explicitly binding the variables to the values is required in DBMS_SQL.

Also, in the case of SELECT statements with DBMS_SQL, two extra steps of defining the columns in the SELECT and retrieving values from the dynamic SQL into local PL/SQL variables are required in addition to the execute and FETCH steps.