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:

1. Define a cursor; just an ID
2. Build your query; the query that you will fitch/execute
3. Parse the cursor using your query and check for any errors in the query.
4. Bind the variables if any.
5. Get the table description; the columns count and types info.
6. Define the columns and link them to the right variables.
7. Execute the cursor and the query.
8. Fetch the actual data from the columns.
9. 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.

The Setup

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.