In May 1994, Oracle introduced the Dynamic SQL concept in its Database version 7.1. With this it enabled developers to write the dynamic SQL through “DBMS_SQL” package.

But, because of its “Not So Easy” syntaxes, the “Sluggish Performance” and “Unsupported User-Defined Types”, the DBMS_SQL library was not very successful. All these shortcomings raised the need for something else, which could help the developers in writing a well optimized code .

After five years,Oracle released an improved version of Dynamic SQL with the launch of Oracle Database version 8.1.5 in February 1999. Because of its stable nature this improved version of Dynamic SQL became the native part of the PL/SQL language and got its name “Native Dynamic SQL” through which it is known now a days.

In order to understand the native dynamic SQL we first need to understand what is static SQL in Oracle Database?

What is Static SQL?

Any SQL statement which does not change during the runtime is called a static SQL statement.

The advantage of Static SQL statements is that we already know whether all the depended objects over which we are writing the statement are present or not.

Another advantage of Static SQL statements is that we hardcode them into our application. Thus we can tune them for optimal performance.

What is Native Dynamic SQL?

Any SQL statement which is constructed at the runtime is called dynamic SQL. Therefore they cannot be hardcoded into the application. This in turn increases their flexibility.

On one hand ,static SQL lets you execute only DML statements inside your PL/SQL block. On the other hand, the dynamic SQL enables you to execute DDL statements and that too inside your PL/SQL block. Thus by using Dynamic SQL you can create a table or drop an index or truncate your table right inside your PL/SQL block. This is an advantage of Native Dynamic SQL over static SQL.

What is Native Dynamic PL/SQL?

Similar to dynamic SQL, the process of constructing PL/SQL code at runtime is called dynamic PL/SQL.

Now, let’s see the benefits of Native Dynamic SQL over DBMS_SQL package in Oracle Database.

Benefits of Native Dynamic SQL

1. Easy to Use Codes: In comparison to DBMS_SQL package, the native dynamic SQL is much easy to use. This is mainly because it is incorporated in the SQL itself. It is equivalent to using Static SQL within a PL/SQL code. Also native dynamic SQL code is more compact and readable than DBMS_SQL package. The latter requires large amount of codes due to strict sequential procedures. It makes it more complex.
2. Improvement In The Performance Of The Code: There is a built-in support for native dynamic SQL in the PL/SQL interpreter. Thus the programs that use it are more efficient than those using DBMS­_SQL package. Basically native dynamic SQL combines the steps involving preparation, binding & execution into one operation. This reduces the procedure call & data copying overhead in turn improving the performance.
3. Support for User-Defined Types: Unlike DBMS_SQL package, the native dynamic SQL supports all kinds of user-defined types. For instance user-defined objects, REFs & collections.
4. Support For Fetching Into Records: The rows that are the result of a query can be directly fetched into PL/SQL records using native dynamic SQL. This isn’t possible with DBMS_SQL package.