Dynamic Magic Using DBMS_SQL
Dynamic SQL/PLSQL is considered one of the most advanced topics in Oracle PL/SQL. Even though it’s not a new topic at all, it’s still one of the most challenging concepts to digest and master.
Dynamic SQL is simply any part of your SQL code that is not written explicitly before compiling. So, for instance, if you do not know the “WHERE” predicate in a SQL query, then you have to use dynamic SQL. Oracle gives us two distinct ways to build dynamic SQL/plsql code, and the first one is the NativeDynamic SQL (NDS). Using the elegant and easy-to-use
EXECUTE IMMEDIATE and
OPEN.. FOR statements, this method is more than enough in over 90 percent of scenarios. But what about the remaining 10 percent? Here is where it gets a little more complex.
When faced with dynamic SQL method four, in which you need your code to execute dynamic SQL statements that contain a varying number of bind variables, NDS will not be enough because with NDS, you need to know the number of columns and the data types if you’re going to retrieve them (columns types). Oracle provides an alternative (actually much older) way to handle the most extreme situations where the code needs to be super-dynamic, and that’s the DBMS_SQL package.
DBMS_SQL is capable of handling all the unknowns that you may encounter. Using DBMS_SQL, you use a special cursor type (not the regular cursor we know and use always) when you use a DBMS_SQL cursor and only get a unique cursor ID. Then, we get the full table description including the types and properties of the columns. Using all of this information, we can fetch the data into the right types of variables. The process is typically done as follows:
Now, let’s talk about our imaginary scenario. For some reason, we need to create a table that can be used as a “data vault” to store any type of data from any other table and keep it in an encrypted form. We must do that in a way that only the person who removed the data to the “vault” can decrypt it and retrieve it.
For that to happen, we will need many parts, such as some custom data types and objects, but more importantly, we must build a function that is capable of collecting the data from any table and encrypt then store in the vault.
Notice here that the function will have been written in a way that makes it possible to handle any table without knowing anything about it, so what is our only tool to do so? Easy, it’s the DBMS_SQL package!
Using the Data Vault imaginary function, I will try to demonstrate and explain the most important, practical components of a dynamic SQL-based function that depends on DBMS_SQL.
Just one final note, this is a tutorial that is designed to demonstrate how to use the DBMS_SQL, so it’s not 100 percent production-ready, and many things can be enhanced or can even be done in some other way. There are also some security issues, but again, it’s about DBMS_SQL.
1. I built a nested table of raw types to store the encrypted data that will be stored in the vault.
CREAT OR REPLACE TYPE raw_tab AS NESTED TABLE OF RAW(2000)
2. To store the columns info, I created an object that mimics the DBMS_SQL_DESC type, and I also created a nested table of this object to hold the information about all of the table’s columns.
CREATE OR REPLACE TYPE COL_INFO_OBJ_T AS TABLE OF col_info_obj;
3. I chose to create two tables; one is the vault itself where the encrypted data is going to be stored, and the other one is like a master record that keeps information about the insert operation, and it will be very important when we build the restore/decrypt function or procedure. In order to demonstrate the use of NDS alongside DBMS_SQL, the function will create the needed tables if any one of them does not exist.