DROP PROCEDURE SDSADM.SET_VUID_SEQ;

CREATE OR REPLACE PROCEDURE SDSADM.SET_VUID_SEQ
(
    seq_tab_prefix STRING,
    pkey_name      STRING  DEFAULT 'ID')
/***************************************************************************
 *
 *    NAME
 *      set_vuid_seq
 *
 *    DESCRIPTION
 *      set_vuid_seq sets a schema's VUID sequence next value to start with the number
 *      above the highest value in the Primary Key column of a matching set of tables.
 *
 *    PARAMETERS
 *      seq_tab_prefix  Prefix of the VUID sequence and the matching set of tables
 *      pkey_name       Name of the column to check in each table
 *
 */

 AS
    CURSOR matching_tables(name_prefix STRING) IS SELECT table_name
                                FROM user_tables
                               WHERE table_name LIKE name_prefix
                                 AND table_name not in ('STD_ZIPASSIGNMENT', 'STD_STATCNTYXREF');

    sql_stmt     STRING(255);
    nxt_tab_val  INTEGER(38);
    cur_tab_val  INTEGER(38);
    seq_name     STRING(30);
    tab_name     STRING(30);
    cur_seq_val  INTEGER(38);
    cur_seq_min  INTEGER(38);
    cur_seq_max  INTEGER(38);
    cur_seq_stp  INTEGER(38);

BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    seq_name := UPPER(seq_tab_prefix) || '_VUID';
    tab_name := UPPER(seq_tab_prefix) || '_%';

    -- Get the sequence's current settings
    sql_stmt := 'SELECT min_value, max_value, increment_by, last_number FROM all_sequences';
    sql_stmt := sql_stmt || ' WHERE sequence_name = ''' || seq_name || '''';
    EXECUTE IMMEDIATE sql_stmt INTO cur_seq_min, cur_seq_max, cur_seq_stp, cur_seq_val;

    nxt_tab_val := cur_seq_val;

    FOR my_tables IN matching_tables(tab_name) LOOP
        IF (cur_seq_stp > 0) THEN
            sql_stmt := 'SELECT MAX(' || pkey_name || ') FROM ' || my_tables.table_name;
            EXECUTE IMMEDIATE sql_stmt INTO cur_tab_val;
            IF (nxt_tab_val <= cur_tab_val) THEN
                nxt_tab_val := cur_tab_val + 1;
            END IF;
        ELSE
            sql_stmt := 'SELECT MIN(' || pkey_name || ') FROM ' || my_tables.table_name;
            EXECUTE IMMEDIATE sql_stmt INTO cur_tab_val;
            IF (nxt_tab_val >= cur_tab_val) THEN
                nxt_tab_val := cur_tab_val - 1;
            END IF;
        END IF;
    END LOOP;

    -- Adjust the sequence if necessary
    IF (nxt_tab_val - cur_seq_val <> 0) THEN

        -- Build the SQL statement to alter the sequence to step to the desired value
        sql_stmt := 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY ' || (nxt_tab_val - cur_seq_val);
        EXECUTE IMMEDIATE sql_stmt;

        -- Get the next value from the sequence to force it to step
        sql_stmt := 'SELECT ' || seq_name || '.nextval FROM dual';
        EXECUTE IMMEDIATE sql_stmt INTO cur_seq_val;

        -- Build the SQL Statement to return the sequence to its original step value
        sql_stmt := 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY ' || cur_seq_stp;

        EXECUTE IMMEDIATE sql_stmt;
    END IF;

    DBMS_OUTPUT.PUT_LINE('The sequence ' || seq_name || ' is now set to a CURRVAL of: ' || cur_seq_val);

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
 
/


CREATE OR REPLACE PUBLIC SYNONYM SET_VUID_SEQ FOR SDSADM.SET_VUID_SEQ;
DROP PROCEDURE SDSADM.SDSADM_PROC_CACHE_OBJECTS;

CREATE OR REPLACE PROCEDURE SDSADM.SDSADM_PROC_CACHE_OBJECTS AS

  CURSOR sdsadm_tab  IS select A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
                        from sys.user_constraints a, sys.user_cons_columns b
                        where a.constraint_name = B.CONSTRAINT_NAME
                        AND A.CONSTRAINT_TYPE = 'P'
                        ORDER BY TABLE_NAME ;

  CURSOR sdsadm_ndx  IS select A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME, C.INDEX_NAME
                               from sys.user_constraints a, sys.user_cons_columns b, sys.user_indexes c
                               where a.constraint_name = B.CONSTRAINT_NAME
                               AND a.table_name = c.table_name
                               AND A.CONSTRAINT_TYPE = 'P'
                               ORDER BY TABLE_NAME;

  sql_stmt        String(512);
  v_max_id        number;

  BEGIN
     FOR this_sql IN sdsadm_tab  LOOP
       sql_stmt :=  'SELECT /*+ FULL(' ||this_sql.table_name||' ) */ MAX('
                                       || this_sql.column_name
                                       || ') from '
                                       || this_sql.table_name;
       EXECUTE IMMEDIATE sql_stmt into v_max_id;
     END LOOP;

     FOR this_sql IN sdsadm_ndx  LOOP
       sql_stmt :=  'SELECT /*+ INDEX(' ||this_sql.table_name|| ' ' ||this_sql.index_name||' ) */ MAX('||this_sql.column_name ||')  from '||this_sql.table_name ;
--       DBMS_OUTPUT.PUT_LINE(sql_stmt);
       EXECUTE IMMEDIATE sql_stmt into v_max_id;
     END LOOP;

  END;
/
DROP PROCEDURE SDSADM.BATCH_TAB_PRIVS;

CREATE OR REPLACE PROCEDURE SDSADM.BATCH_TAB_PRIVS
(
    grantee         STRING,
    tab_name_pat    STRING  DEFAULT '%',
    select_priv     BOOLEAN DEFAULT FALSE,
    references_priv BOOLEAN DEFAULT TRUE,
    update_priv     BOOLEAN DEFAULT FALSE,
    insert_priv     BOOLEAN DEFAULT FALSE,
    delete_priv     BOOLEAN DEFAULT FALSE
)
/***************************************************************************
 *
 *    NAME
 *      batch_tab_privs
 *
 *    DESCRIPTION
 *      batch_tab_privs grants/revokes selected table privileges for a
 *      specified user for tables within your schema whose name matches a
 *      specified pattern.
 *
 *    PARAMETERS
 *      grantee         Name of the user/role from which you wish to
 *                      grant/revoke privileges
 *      tab_name_pat    Table name pattern. Optional; defaults to all tables
 *      select_priv     Select rows from the tables. Optional; defaults to REVOKE
 *      references_priv Create foreign keys that reference the tables. Optional;
 *                      defaults to GRANT
 *      update_priv     Update rows in the tables. Optional; defaults to REVOKE
 *      insert_priv     Insert rows into the tables. Optional, defaults to REVOKE
 *      delete_priv     Delete rows from the tables. Optional; defaults to REVOKE
 *
 */

 AS
    priv_not_granted EXCEPTION;
    role_does_not_exist EXCEPTION;
    PRAGMA EXCEPTION_INIT(priv_not_granted, -1927);
    PRAGMA EXCEPTION_INIT(role_does_not_exist, -1917);

    CURSOR matching_tables IS SELECT table_name FROM user_tables WHERE table_name LIKE UPPER(tab_name_pat) ORDER By table_name;

    schema_owner  STRING(30);
    role_count    NUMBER(9);
    is_role       BOOLEAN := TRUE;

BEGIN
    DBMS_OUTPUT.ENABLE(1000000);

    -- Execute GRANT/REVOKE for

    FOR this_table IN matching_tables LOOP
       BEGIN
           BEGIN
               -- Validate the grantee name
               SELECT username               -- Look for the grantee in the all_users view
                 INTO schema_owner
                 FROM all_users
                WHERE username = UPPER(grantee);

           EXCEPTION
               WHEN no_data_found THEN
                   BEGIN
                       is_role := FALSE;
                       IF select_priv THEN
                          EXECUTE IMMEDIATE 'GRANT SELECT ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
                       ELSE
                          EXECUTE IMMEDIATE 'REVOKE SELECT ON ' || this_table.table_name || ' FROM ' || UPPER(grantee);
                       END IF;
                    EXCEPTION
                        WHEN role_does_not_exist THEN
                            NULL;
                    END;
           END;
           IF select_priv THEN
              EXECUTE IMMEDIATE 'GRANT SELECT ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE SELECT ON ' || this_table.table_name || ' FROM ' || UPPER(grantee);
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       BEGIN
           IF references_priv AND is_role THEN
              EXECUTE IMMEDIATE 'GRANT REFERENCES ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE REFERENCES ON ' || this_table.table_name || ' FROM ' || UPPER(grantee) ||
                                ' CASCADE CONSTRAINTS';
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       BEGIN
           IF update_priv THEN
              EXECUTE IMMEDIATE 'GRANT UPDATE ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE UPDATE ON ' || this_table.table_name || ' FROM ' || UPPER(grantee);
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       BEGIN
           IF insert_priv THEN
              EXECUTE IMMEDIATE 'GRANT INSERT ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE INSERT ON ' || this_table.table_name || ' FROM ' || UPPER(grantee);
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       BEGIN
           IF delete_priv THEN
              EXECUTE IMMEDIATE 'GRANT DELETE ON ' || this_table.table_name || ' TO ' || UPPER(grantee);
           ELSE
              EXECUTE IMMEDIATE 'REVOKE DELETE ON ' || this_table.table_name || ' FROM ' || UPPER(grantee);
           END IF;
       EXCEPTION
           WHEN priv_not_granted THEN
               NULL;
       END;
       DBMS_OUTPUT.PUT_LINE('Privileges updated on ' ||this_table.table_name || ' for ' || UPPER(grantee) ||'.');
    END LOOP;

EXCEPTION
    WHEN others THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
 
/


CREATE OR REPLACE PUBLIC SYNONYM BATCH_TAB_PRIVS FOR SDSADM.BATCH_TAB_PRIVS;
