Native Dynamic SQL
Before going into learning Native dynamic SQL, a short background is important. Oracle gave a mid-summer gift in May 1994 to all the developers by introducing the Dynamic SQL concept in its Database version 7.1 (a.k.a. Oracle 7.1). With this it enabled developers to write the dynamic SQL through “DBMS_SQL” package.
But gradually the DBMS_SQL library started losing its charm. Because of its “Not So Easy” syntaxes, the “Sluggish Performance” and “Unsupported User-Defined Types”. All these shortcomings raised the need for something else. Which could help the developers in writing a well optimized code without putting excessive stress on their minds.
Oracle took its time, almost five years. Finally, it released an improved version of Dynamic SQL with the launch of Oracle Database 8i or to be precise 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. That is a brief introduction of the genesis of the native dynamic SQL in Oracle Database.
In order to understand the native dynamic SQL we first need to understand what is static SQL in Oracle Database?
What is Static SQL?
As the name suggests 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.
Now that we have learnt what Static SQL is, let’s see what is dynamic SQL?
What is Native Dynamic SQL?
Again as the name suggests, any SQL statement which is constructed at the runtime is called dynamic SQL. These statements are built on the fly. 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 take a look at the benefits of Native Dynamic SQL over DBMS_SQL package in Oracle Database.
Benefits of Native Dynamic SQL
- 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.
- 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.
- 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.
- 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.
What are the functionalities of Dynamic SQL in Oracle Database?
- Firstly, Dynamic DDL & DML with the use of any bind variable.
- Secondly, Dynamic DQL and
- Thirdly, Dynamic DML using a known list of bind variables
These are the three functionalities which Dynamic SQL adds to your PL/SQL programming.
So, how do we use dynamic SQL in Oracle Database?
The two most common methods of using dynamic SQL and PL/SQL in Oracle Database are:
- Execute Immediate statement and
- Open-For, Fetch & Close block.
Execute Immediate statement is used when the query is returning single row data. In case the query is returning multi row data then you can take help of Open-For, Fetch and close block. We will learn about Execute Immediate and Open-for, fetch and close block in detail in the upcoming tutorials.
Are there any other ways of using Dynamic SQL in Oracle Database?
Apart from above mentioned most commonly used methods, the other ways of using dynamic SQL or PL/SQL are
- With Bulk Fetch
- Secondly with Bulk Execute Immediate
- Along with Bulk FORALL and
- Lastly with Bulk Collect Into statement
We have learnt all the above commands and statements in the previous series on bulk data processing.
If you like learning through video tutorial then go ahead check it out.
That is a detailed introduction to the Native Dynamic SQL in Oracle Database. Hope you enjoyed reading. Do make sure to Share the Link of this blog with your friends on your social media. Also do make sure to subscribe to the YouTube channel to follow this series and learn something new and interesting.
Thanks and have a great day!