Dynamic Magic Using DBMS_SQL
Look at a tutorial that explains how to use DBMS_SQL.
Join the DZone community and get the full member experience.Join For Free
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:
- Define a cursor; just an ID
- Build your query; the query that you will fitch/execute
- Parse the cursor using your query and check for any errors in the query.
- Bind the variables if any.
- Get the table description; the columns count and types info.
- Define the columns and link them to the right variables.
- Execute the cursor and the query.
- Fetch the actual data from the columns.
- Admire your code!
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 ahmed."COL_INFO_OBJ" IS OBJECT ( column_type NUMBER, column_name VARCHAR2 (32), column_name_len NUMBER, column_schema_name VARCHAR2 (32), column_precision NUMBER, column_scale NUMBER ); /
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.
FUNCTION dynamic_vault_ins (table_name IN VARCHAR2, cond IN VARCHAR2 DEFAULT '1 = 1', enc_key IN VARCHAR2 DEFAULT NULL, del_flag VARCHAR2 DEFAULT 'NO') RETURN NUMBER IS encrypt_typ NUMBER := DBMS_CRYPTO.encrypt_aes256 + DBMS_CRYPTO.chain_cbc + DBMS_CRYPTO.pad_pkcs5; enc_key_var VARCHAR2(256); raw_col_v RAW(2000); encrypted_col_v RAW(2000); encr_row raw_tab := raw_tab(); sql_query VARCHAR2(2000); col_count NUMBER; col_desc DBMS_SQL.desc_tab := DBMS_SQL.desc_tab(); cur_id NUMBER; row_counter NUMBER := 0; tab_exist NUMBER; num_var NUMBER; char_var VARCHAR2(2000); date_var DATE; c_var CHAR(4); row_id_var NUMBER := 1; opr_id_var NUMBER := 1; temp_var NUMBER; col_details col_info_obj_t := col_info_obj_t(); bad_cond EXCEPTION; BEGIN IF REGEXP_COUNT (UPPER(cond), ';|DELETE|DROP|CREATE|INSERT|UPDATE|GRANT|TRUNCATE') > 0 THEN RAISE bad_cond; END IF; IF enc_key IS NULL OR LENGTH (enc_key) != 32 THEN enc_key_var := 'ahmed-rony-yousef-29102006-12-11'; ELSE enc_key_var := enc_key; END IF; SELECT COUNT(tname) INTO tab_exist FROM tab WHERE tname = 'DATA_VAULT'; IF tab_exist = 0 THEN EXECUTE IMMEDIATE 'CREATE TABLE data_vault (row_id number, opr_id NUMBER, ins_date timestamp, table_name VARCHAR2(32), enc_data raw_tab) NESTED TABLE enc_data STORE AS enc_data'; ELSE EXECUTE IMMEDIATE 'SELECT nvl(MAX(row_id),0) + 1, nvl(MAX(opr_id),0) + 1 FROM data_vault' INTO row_id_var, opr_id_var; END IF; SELECT COUNT(tname) INTO tab_exist FROM tab WHERE tname = 'VAULT_COL_INFO2'; IF tab_exist = 0 THEN EXECUTE IMMEDIATE 'CREATE TABLE vault_col_info2 ( opr_id NUMBER, inst_time TIMESTAMP, no_of_rows NUMBER, org_query varchar (2000), col_data col_info_obj_t) nested table col_data store as col_data2'; END IF; /*Building the query*/ sql_query := 'select * from '||DBMS_ASSERT.sql_object_name(table_name)||' where '||cond; /*open cursor*/ cur_id := DBMS_SQL.open_cursor; /*parse the cursor*/ DBMS_SQL.parse(cur_id, sql_query, DBMS_SQL.native); /*Build a descrbtion of the columns and store the result in the special record-collection type 'dbms_sql.desc_tab' and get the count of column and store in a variable (in this case it's col_count*/ DBMS_SQL.describe_columns(cur_id, col_count, col_desc); /*Extending the nested table and after that storing the colums details in the custom nested table of objetcs*/ col_details.EXTEND(col_count); FOR i IN 1..col_count LOOP col_details(i) := col_info_obj( col_desc(i).col_type, col_desc(i).col_name, col_desc(i).col_name_len, col_desc(i).col_schema_name, col_desc(i).col_precision, col_desc(i).col_scale); END LOOP; /*Define the columns types YOU MUST DEFINE THE COLUNS IF YOU'RE GOING TO USE FETCH OR YOU WILL GET AN ERROR*/ FOR i IN 1..col_count LOOP case col_desc(i).col_type when 2 THEN DBMS_SQL.define_column(cur_id, i, num_var); when 12 THEN DBMS_SQL.define_column(cur_id, i, date_var); when 1 THEN DBMS_SQL.define_column(cur_id, i, char_var, 200); when 96 THEN DBMS_SQL.define_column(cur_id, i, c_var, 12); END case; END LOOP; /*Execute the cursor*/ temp_var := DBMS_SQL.execute(cur_id); encr_row.EXTEND(col_count); /*Fetch the data from the rows and encrypt it*/ WHILE DBMS_SQL.fetch_rows(cur_id) > 0 LOOP FOR i IN 1..col_count LOOP case col_desc(i).col_type when 2 THEN DBMS_SQL.COLUMN_VALUE(cur_id, i, num_var); /*Convert the data to RAW*/ raw_col_v := UTL_I18N.string_to_raw(TO_CHAR(NVL(num_var,0)),'AL32UTF8' ); -- Encrypt the data encrypted_col_v := DBMS_CRYPTO.encrypt(src => raw_col_v, typ => encrypt_typ, key=> UTL_I18N.string_to_raw(enc_key_var)); encr_row(i) := encrypted_col_v; -- when 12 THEN DBMS_SQL.COLUMN_VALUE(cur_id, i, date_var); raw_col_v := UTL_I18N.string_to_raw(TO_CHAR(NVL(date_var, TO_DATE('1999/07/15', 'YYYY/MM/DD'))),'AL32UTF8' ); encrypted_col_v := DBMS_CRYPTO.encrypt(src => raw_col_v, typ => encrypt_typ, key=> UTL_I18N.string_to_raw(enc_key_var)); encr_row(i) := encrypted_col_v; when 1 THEN DBMS_SQL.COLUMN_VALUE(cur_id, i, char_var); raw_col_v := UTL_I18N.string_to_raw(NVL(char_var,'-'),'AL32UTF8' ); encrypted_col_v := DBMS_CRYPTO.encrypt(src => raw_col_v, typ => encrypt_typ, key=> UTL_I18N.string_to_raw(enc_key_var)); encr_row(i) := encrypted_col_v; when 96 THEN DBMS_SQL.COLUMN_VALUE(cur_id, i, c_var); raw_col_v := UTL_I18N.string_to_raw(TO_CHAR(c_var),'AL32UTF8' ); encrypted_col_v := DBMS_CRYPTO.encrypt(src => raw_col_v, typ => encrypt_typ, key=> UTL_I18N.string_to_raw(enc_key_var)); encr_row(i) := encrypted_col_v; ELSE DBMS_SQL.COLUMN_VALUE(cur_id, i, char_var); raw_col_v := UTL_I18N.string_to_raw(char_var,'AL32UTF8' ); encrypted_col_v := DBMS_CRYPTO.encrypt(src => raw_col_v, typ => encrypt_typ, key=> UTL_I18N.string_to_raw(enc_key_var)); encr_row(i) := encrypted_col_v; END case; END LOOP; /* Here you have to use dynamic sql because if the table still not exist the compiler will raise an error 'table or view does not exist'*/ EXECUTE IMMEDIATE 'INSERT INTO data_vault VALUES (:1, :2, :3, :4, :5)' USING row_id_var, opr_id_var, SYSTIMESTAMP, table_name, encr_row; row_id_var := row_id_var + 1; row_counter := row_counter + 1; END LOOP; /* Here you have to use dynamic sql because if the table still not exist the compiler will raise an error 'table or view does not exist'*/ EXECUTE IMMEDIATE 'insert into vault_col_info2 values(:1, :2, :3, :4, :5)' USING opr_id_var, SYSTIMESTAMP, row_counter, sql_query, col_details; IF del_flag = 'DELETE' OR del_flag = 'YES' THEN EXECUTE IMMEDIATE 'delete '||table_name||' where '||cond; DBMS_OUTPUT.put_line('Total oreginal rows deleted is: '||SQL%ROWCOUNT); END IF; RETURN row_counter; COMMIT; DBMS_SQL.close_cursor(cur_id); EXCEPTION WHEN bad_cond THEN raise_application_error (-20001,'Bad or dangerous condetions'); return row_counter; WHEN OTHERS THEN raise_application_error (-20002,'SOmthing wrong happened! ==>'||SQLERRM); return row_counter; END dynamic_vault_ins;
Let us know your thoughts in the comments down below.
Opinions expressed by DZone contributors are their own.