Oracle – Simple/Reusable Statement Runner Using PL/SQL Table Type (Collection)

In an Oracle anonymous block based script, how many times do you run into situations where what needs to be done remains a constant but what that logic works with, keeps changing? Parameters are a great way to do it too but in the illustration below, we are going to use PL/SQL table type (collection).

In the script below, I need to do a bunch of REVOKE’s. The list of REVOKE’s will keep changing from time to time but the script itself is a constant.  In this case, the meat of the script runs the revoke and ignores errors if the user/role did not exist and keeps going.

The script is very re-usable because I could replace everything between BEGIN PASTE and END PASTE based on the changing list of REVOKE’s.

DECLARE

  --Declare a TABLE type (collection type) and a variable of that type to hold our collection
  TYPE VARCHAR2_TABLE_TYPE IS TABLE OF VARCHAR2(125);
  v_Revokes_Table VARCHAR2_TABLE_TYPE;

BEGIN

v_Revokes_Table := VARCHAR2_TABLE_TYPE(
        --
        --************  BEGIN PASTE OF REVOKES AFTER THIS LINE **********--
        --
        'REVOKE LEGAL_DEPARTMENT_ROLE FROM USER0012',
        'REVOKE ADMINISTRATION_DEPT_USERS FROM USER0046',
        'REVOKE TRAVEL_OVERSIGHT_ROLE FROM USER0046',
        'REVOKE EXECUTIVE_MGMT_ROLE FROM USER0046',
        'REVOKE WEB_APPLICATION_ROLE FROM USER0046',
        'REVOKE LEGAL_DEPARTMENT_ROLE FROM USER2344',
        'REVOKE FRONT_END_ROLE FROM USER1543',
        'REVOKE DATA_PREPARER_ROLE FROM USER1543',
        'REVOKE DATA_UPLOADER_ROLE FROM USER3421',
        'REVOKE DATA_ARCHIVER_ROLE FROM USER8903',
        'REVOKE FAX_APPLICATION_ROLE FROM USER4567',
        'REVOKE FRONT_END_ROLE FROM USER0854',
        'REVOKE DATA_SCIENCE_ROLE FROM USER9076',
        'REVOKE DATA_ANALYSIS_ROLE FROM USER5679',
        'REVOKE ANALYTICS_ROLE FROM USER0291',
        'REVOKE ADMINISTRATION_DEPT_USERS FROM USER2121',
        'REVOKE ANALYTICS_ROLE FROM USER2322',
        'REVOKE BUDGETING_ROLE FROM USER1234',
        'REVOKE SPENDING_ROLE FROM USER2311',
        'REVOKE TRAVEL_OVERSIGHT_ROLE FROM USER0098',
        'REVOKE READONLYROLE FROM USER1121',
        'REVOKE ZEBRA_XREF_ROLE FROM USER1211'
        --
        --************  END PASTE OF REVOKES **********--
        --
    );

   --Loop through the list and do the revoke
    FOR i IN 1..v_Revokes_Table.COUNT LOOP

        BEGIN

            --Do the revoke and ignore errors..
            dbms_output.put_line(v_Revokes_Table(i));
            EXECUTE IMMEDIATE v_Revokes_Table(i); 

        EXCEPTION
            --Should really be ignoring only specific errors but for this example it works!
            WHEN OTHERS THEN
                dbms_output.put_line('Failed: ' || SUBSTR(SQLERRM, 1, 240));
        END;

    END LOOP;

END;

Since the script is straight-forward enough, I am going to forego the explanation.

To refine this further, you could also use the SQL*Plus DEFINE to define a SQL*Plus variable to hold the array content that then gets used within the anonymous block. Then, it would be truly reusable without question. The example above would have to change a bit but the concept is the same nevertheless.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s