text: -- +----------------------------------------------------------------------------+
  -- |                          Jeffrey M. Hunter                                 |
  -- |                      jhunter@idevelopment.info                             |
  -- |                         www.idevelopment.info                              |
  -- |----------------------------------------------------------------------------|
  -- |      Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.       |
  -- |----------------------------------------------------------------------------|
  -- | DATABASE : Oracle                                                          |
  -- | FILE     : plsql_webdba_utl_pkg.sql                                        |
  -- | CLASS    : PL/SQL                                                          |
  -- | PURPOSE  : Example PL/SQL package that shows how to use dynamic SQL before |
  -- |            Oracle8i.                                                       |
  -- | NOTE     : As with any code, ensure to test this script in a development   |
  -- |            environment before attempting to run it in production.          |
  -- +----------------------------------------------------------------------------+
  
  CREATE OR REPLACE PACKAGE webdba_util AS
  
    PROCEDURE drop_user(username_in IN VARCHAR2);
    PROCEDURE create_user(username_in             IN VARCHAR2, 
                          default_tablespace_in   IN VARCHAR2,
                          temporary_tablespace_in IN VARCHAR2,
                          password_in             IN VARCHAR2);
    PROCEDURE change_user_password(username_in IN VARCHAR2,
                                  password_in IN VARCHAR2);
    PROCEDURE grant_user(username_in IN VARCHAR2,
                         privilege_in IN VARCHAR2);
    PROCEDURE revoke_user(username_in IN VARCHAR2,
                         privilege_in IN VARCHAR2);
    
  END webdba_util;
  /
  show errors
  
  CREATE OR REPLACE PACKAGE BODY webdba_util AS
  
    PROCEDURE drop_user(username_in IN VARCHAR2) IS
      v_Cursor           NUMBER;
      v_DropUserString   VARCHAR2(500);
      v_Results          INTEGER;
    BEGIN
  
      v_Cursor  := DBMS_SQL.open_cursor;
      v_DropUserString := 'DROP USER ' || username_in || ' CASCADE';
  
      DBMS_SQL.PARSE(v_Cursor, v_DropUserString, DBMS_SQL.V7);
      v_Results := DBMS_SQL.EXECUTE(v_Cursor);
  
      DBMS_SQL.close_cursor(v_Cursor);
  
    END drop_user;
  
    PROCEDURE create_user(username_in             IN VARCHAR2,
                          default_tablespace_in   IN VARCHAR2,
                          temporary_tablespace_in IN VARCHAR2,
                          password_in             IN VARCHAR2) IS
      v_Cursor           NUMBER;
      v_DropUserString   VARCHAR2(1500);
      v_Results          INTEGER;
    BEGIN
  
      v_Cursor  := DBMS_SQL.open_cursor;
      v_DropUserString := 'CREATE USER ' || username_in || ' DEFAULT TABLESPACE ' || default_tablespace_in ||
  ' TEMPORARY TABLESPACE ' || temporary_tablespace_in || ' IDENTIFIED BY ' || password_in;
  
      DBMS_SQL.PARSE(v_Cursor, v_DropUserString, DBMS_SQL.V7);
      v_Results := DBMS_SQL.EXECUTE(v_Cursor);
  
      DBMS_SQL.close_cursor(v_Cursor);
  
    END create_user;
  
  
    PROCEDURE change_user_password(username_in IN VARCHAR2,
                                   password_in IN VARCHAR2) IS
      v_Cursor           NUMBER;
      v_AlterUserString   VARCHAR2(500);
      v_Results          INTEGER;
    BEGIN
  
      v_Cursor  := DBMS_SQL.open_cursor;
      v_AlterUserString := 'ALTER USER ' || username_in || ' IDENTIFIED BY ' || password_in;
  
      DBMS_SQL.PARSE(v_Cursor, v_AlterUserString, DBMS_SQL.V7);
      v_Results := DBMS_SQL.EXECUTE(v_Cursor);
  
      DBMS_SQL.close_cursor(v_Cursor);
  
    END change_user_password;
  
    PROCEDURE grant_user(username_in IN VARCHAR2,
                         privilege_in IN VARCHAR2) IS
      v_Cursor           NUMBER;
      v_AlterUserString   VARCHAR2(500);
      v_Results          INTEGER;
    BEGIN
  
      v_Cursor  := DBMS_SQL.open_cursor;
      v_AlterUserString := 'GRANT ' || privilege_in || ' TO ' || username_in;
  
      DBMS_SQL.PARSE(v_Cursor, v_AlterUserString, DBMS_SQL.V7);
      v_Results := DBMS_SQL.EXECUTE(v_Cursor);
  
      DBMS_SQL.close_cursor(v_Cursor);
  
    END grant_user;
  
    PROCEDURE revoke_user(username_in IN VARCHAR2,
                         privilege_in IN VARCHAR2) IS
      v_Cursor           NUMBER;
      v_AlterUserString   VARCHAR2(500);
      v_Results          INTEGER;
    BEGIN
  
      v_Cursor  := DBMS_SQL.open_cursor;
      v_AlterUserString := 'REVOKE ' || privilege_in || ' FROM ' || username_in;
  
      DBMS_SQL.PARSE(v_Cursor, v_AlterUserString, DBMS_SQL.V7);
      v_Results := DBMS_SQL.EXECUTE(v_Cursor);
  
      DBMS_SQL.close_cursor(v_Cursor);
  
    END revoke_user;
  
  
  END webdba_util;
  /
  
  show errors
  