Native Dynamic SQL

Native Dynamic SQL! When I first heard the term the very first confusion raised in my mind was what Dynamic SQL is. Or at least what is static SQL so that I can differentiate between the two. Here is what I understood. When you know the tables and columns that you are referencing in your SQL then the SQL you are using is static. There are many advantages of using static SQL. First all the errors and exceptions that can occur at runtime are known or can be found. And more importantly all the techniques of SQL tuning are applicable to static SQL only. So why dynamic SQL?
There is another school of thought in application development that says; “you should write robust, reusable and dynamic code modules”. This is object oriented programming. Many times in application development it happens that you don’t know the columns and tables that you will be interacting with. For that purpose and many others you must rely on dynamic SQL. Thus dynamic SQL is SQL that decides at runtime what tables to retrieve data from and what columns to retrieve.
Having said what is dynamic SQL is not enough? You must know how to generate it to be beneficial. Oracle provides two ways for the generation of dynamic SQL. One is DBSM_SQL built in package and the other is Native Dynamic SQL. Which one is better of the two is something that is dependent on your personal judgment. However NDS outperforms DBMS_SQL in performance. However you can’t ignore the DBMS_SQL because it is the legacy way and billions of lines of code have been written using this package; so in order to understand that code you must understand this package. Also NDS does not support the generation of dynamic SQL where name, number or types of arguments is not known. So you must use DBSM_SQL package for those situations.
On the other hand NDS is better then DBMS_SQL in many ways. First and I think the most prominent advantage is that its syntax is pretty similar to normal SQL syntax. Performance is also a lot better then the DBSM_SQL package. And also the NDS is the future that we will be using.