/*
|| Oracle 10g PL/SQL and SQL Enhancements - Listing 1
||
|| Contains examples of new and improved Oracle 10g PL/SQL features, including:
||
|| - Encryption and decryption with DBMS_CRYPTO
|| - Improved database monitoring with DBMS_MONITOR and DBMS_SERVER_ALERT
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's 
|| new and improved PL/SQL supplied packages DataPump features and should be
|| carefully proofread before executing it against any existing Oracle database
|| to insure that no potential damage can occur.
||
*/

----- 
-- Listing 1.1: Using DBMS_CRYPTO to encrypt and decrypt data
-----

CREATE OR REPLACE PACKAGE hr.pkg_sensitive_data
/*
|| Describes public interface for sample functions to encrypt and decrypt 
|| character and numeric data using DBMS_CRYPTO methods.
*/
IS
    FUNCTION encryptor (
        input_string        IN  VARCHAR2
    ) RETURN RAW;
    
    FUNCTION decryptor (
        INPUT_STRING        IN  VARCHAR2
    ) RETURN VARCHAR2;

END pkg_sensitive_data;
/

CREATE OR REPLACE PACKAGE BODY pkg_sensitive_data
/*
|| Implements public methods for sample functions to encrypt and decrypt 
|| character and numeric data using DBMS_CRYPTO methods.
*/
IS
    SQLERRMSG   VARCHAR2(255);
    SQLERRCDE   NUMBER;
    
    -----
    -- Defined Encryption Methods:
    -- DES_CBC_NONE: Data Encryption Standard Block Cipher, Cipher Block '
    --               chaining, no padding
    -- SH1_ECB_ZERO: Secure Hash Algorithm, Electronic Codebook Cipher 
    --               chaining, pad with zeroes
    -----    
    DES_CBC_NONE        CONSTANT PLS_INTEGER :=
        DBMS_CRYPTO.ENCRYPT_DES + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_NONE;
    SH1_ECB_ZERO        CONSTANT PLS_INTEGER :=
        DBMS_CRYPTO.HASH_SH1 + DBMS_CRYPTO.CHAIN_ECB + DBMS_CRYPTO.PAD_ZERO;

    FUNCTION encryptor (
        input_string        IN  VARCHAR2
    ) RETURN RAW
    IS
        -- Local variables
        seed                VARCHAR2(64) := 
            'a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0';
        converted_seed      RAW(64);
        converted_string    RAW(64);
        encrypted_string    RAW(64);

    BEGIN
        -- Convert incoming string and supplied seed to RAW datatype using the 
        -- new UTLI18N package to convert the string to the AL32UTF8 character
        -- set
        converted_string := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
        converted_seed   := UTL_I18N.STRING_TO_RAW(seed, 'AL32UTF8');

        -- Encrypt the RAW value using the ENCRYPT function and the appropriate
        -- encryption type
        encrypted_string := 
             DBMS_CRYPTO.ENCRYPT(
                 src => converted_string
                ,typ => SH1_ECB_ZERO 
                ,key => converted_seed
                ,iv =>  NULL);
        
        RETURN encrypted_string;

    EXCEPTION
        WHEN OTHERS THEN
            SQLERRMSG := SQLERRM;
            SQLERRCDE := SQLCODE;
            RETURN NULL;

    END encryptor;
    
    FUNCTION decryptor (
        input_string        IN  VARCHAR2
    ) RETURN VARCHAR2
    IS
        -- Local variables
        converted_string    VARCHAR2(64);
        seed                VARCHAR2(64) := 
            'a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0a5b7c9d0';
        converted_seed      RAW(64);
        decrypted_string    VARCHAR2(64);

    BEGIN
        -- Convert incoming string and supplied seed to RAW datatype using the 
        -- new UTLI18N package to convert the string to the AL32UTF8 character
        -- set
        converted_string := UTL_I18N.STRING_TO_RAW(input_string, 'AL32UTF8');
        converted_seed   := UTL_I18N.STRING_TO_RAW(seed, 'AL32UTF8');
        
        -- Encrypt the RAW value using the ENCRYPT function and the appropriate
        -- encryption type
        decrypted_string := 
            DBMS_CRYPTO.DECRYPT(
                 src => input_string
                ,typ => SH1_ECB_ZERO
                ,key => converted_seed
                ,iv =>  NULL);
        
        -- Convert incoming string to RAW datatype, using the UTLI18N package 
        -- to convert the string to the AL32UTF8 character set
        converted_string := UTL_I18N.RAW_TO_CHAR(decrypted_string, 'AL32UTF8');

        RETURN converted_string;

    EXCEPTION
        WHEN OTHERS THEN
            SQLERRMSG := SQLERRM;
            SQLERRCDE := SQLCODE;
            RETURN NULL;

    END decryptor;
END pkg_sensitive_data;
/

----- 
-- Listing 1.2: Demonstrate usage of DBMS_CRYPTO 
-----

-- Create a table for storing sensitive information
CREATE TABLE hr.emp_secured_data (
     employee_id        NUMBER      PRIMARY KEY
    ,ssn                VARCHAR2(64) NOT NULL
    ,credit_card_nbr    VARCHAR2(64) NOT NULL
);

-- Load some test data and encrypt it with ENCRYPTOR function
INSERT INTO hr.emp_secured_data (employee_id,ssn,credit_card_nbr) 
VALUES (
     301
    ,HR.pkg_sensitive_data.encryptor('345678901')
    ,HR.pkg_sensitive_data.encryptor('372812345630100')
    );
INSERT INTO hr.emp_secured_data (employee_id,ssn,credit_card_nbr) 
VALUES (
     302
    ,HR.pkg_sensitive_data.encryptor('456789012')
    ,HR.pkg_sensitive_data.encryptor('372812345630200')
    );
INSERT INTO hr.emp_secured_data (employee_id,ssn,credit_card_nbr) 
VALUES (
     303
    ,HR.pkg_sensitive_data.encryptor('789012345')
    ,HR.pkg_sensitive_data.encryptor('372812345630300')
    );

COMMIT;

-- Read sensitive values from table using DECRYPTOR function
SQL> COL employee_id     FORMAT 9999999  HEADING 'EmpID'
SQL> COL dcr_ssn         FORMAT A9       HEADING 'SocSecNbr'
SQL> COL dcr_ccn         FORMAT A16      HEADING 'Credit Card'
SQL> SELECT
  2       employee_id
  3      ,HR.pkg_sensitive_data.decryptor(ssn) dcr_ssn
  4      ,HR.pkg_sensitive_data.decryptor(credit_card_nbr) dcr_ccn
  5    FROM HR.emp_secured_data;

   EmpID SocSecNbr Credit Card                                                  
-------- --------- ----------------                                             
     301 345678901 372812345630100                                              
     302 456789012 372812345630200                                              
     303 789012345 372812345630300                                              

----- 
-- Listing 1.3: Create a new table and package to illustrate 
--              DBMS_MONITOR features
-----

-- Create table
CREATE TABLE sh.orders
(
    order_id   NUMBER(10) NOT NULL,
    product_id NUMBER(10) NOT NULL,
    qty        NUMBER(15,2) NOT NULL,
    status     CHAR(1) DEFAULT 'P' NOT NULL
)
TABLESPACE example
  PCTFREE 10
  INITRANS 1
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );

ALTER TABLE sh.orders
  ADD CONSTRAINT orders_pk PRIMARY KEY (order_id)
  USING INDEX 
  TABLESPACE EXAMPLE
  PCTFREE 10
  INITRANS 2
  MAXTRANS 255
  STORAGE
  (
    INITIAL 64K
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
  );
  
ALTER TABLE sh.orders
  ADD CONSTRAINT status_ck
  CHECK (status IN ('C','P'));

-----
-- Create the new Order Entry package specification and body
-----
CREATE OR REPLACE PACKAGE sh.pkg_order_entry
/*
|| Specifies public interface for Order Entry functions
*/ 
IS 
    TYPE curQuery IS REF CURSOR 
    RETURN sh.orders%ROWTYPE;

    PROCEDURE AddNewOrder (
         OrderID    IN  sh.orders.order_id%TYPE
        ,ProductID  IN  sh.orders.product_id%TYPE
        ,Quantity   IN  sh.orders.qty%TYPE
    );

    PROCEDURE UpdateOrderStatus;

    PROCEDURE DisplayOrders(
         ProductID   IN     sh.orders.product_id%TYPE
        ,curResults  OUT    sh.pkg_order_entry.curquery
    );
END;
/

CREATE OR REPLACE PACKAGE BODY sh.pkg_order_entry
/*
|| Implements public interface for Order Entry functions
*/ 
IS 
    PROCEDURE AddNewOrder (
         OrderID    IN  sh.orders.order_id%TYPE
        ,ProductID  IN  sh.orders.product_id%TYPE
        ,Quantity   IN  sh.orders.qty%TYPE
    )
    IS
    BEGIN

        DBMS_APPLICATION_INFO.SET_MODULE(
             module_name => 'AddNewOrder'
            ,action_name => 'Initialization'
        );

        INSERT INTO orders (order_id, product_id, qty, status)
        VALUES (OrderID, ProductID, Quantity, 'P'); 
        
        COMMIT;   


    END AddNewOrder;
    
    PROCEDURE UpdateOrderStatus
    IS
    BEGIN
        DBMS_APPLICATION_INFO.SET_MODULE(
             module_name => 'UpdateOrderStatus'
            ,action_name => 'GetStatus'
        );

        UPDATE sh.orders
           SET status = 'C'
         WHERE status = 'P';

        COMMIT;   

           
    END UpdateOrderStatus;
    
    PROCEDURE DisplayOrders (
         ProductID   IN     sh.orders.product_id%TYPE
        ,curResults  OUT    sh.pkg_order_entry.curquery
    )
    IS
    BEGIN
        DBMS_APPLICATION_INFO.SET_MODULE(
             module_name => 'DisplayOrders'
            ,action_name => 'GetOrders'
        );

        OPEN curResults FOR 
        SELECT *
          FROM sh.orders
         WHERE product_id = ProductID;

    END DisplayOrders;
    
END;
/

----- 
-- Listing 1.4: Monitoring a client session's activity and statistics 
--              with DBMS_MONITOR
-----

-- Enable SQL tracing for a given database session ID (SID)
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(161);

-- Disable SQL tracing for a given database session ID (SID)
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(161);

-- Start gathering statistics for specific Module(s) and Action(s)
BEGIN
    DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
         service_name => 'zdcdb.zerodefect.com'
        ,module_name => 'AddNewOrder'
        ,action_name => 'Initialization'
    );
    DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
         service_name => 'zdcdb.zerodefect.com'
        ,module_name => 'UpdateOrderStatus'
        ,action_name => 'SetStatus'
    );
    DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
         service_name => 'zdcdb.zerodefect.com'
        ,module_name => 'DisplayOrders'
        ,action_name => 'GetOrders'
    );
END;
/

-- Stop gathering statistics
BEGIN
    DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(
         service_name => 'zdcdb.zerodefect.com'
        ,module_name => 'AddNewOrder'
        ,action_name => 'Initialization'
    );
    DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(
         service_name => 'zdcdb.zerodefect.com'
        ,module_name => 'UpdateOrderStatus'
        ,action_name => 'SetStatus'
    );
    DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(
         service_name => 'zdcdb.zerodefect.com'
        ,module_name => 'DisplayOrders'
        ,action_name => 'GetOrders'
    );
END;
/

----- 
-- Listing 1.5: Reviewing statistics gathered for PKG_ORDER_ENTRY by querying
--              V$SERV_MOD_ACT_STATS dynamic view
-----

SQL> COL module          FORMAT A18      HEADING 'Module'
SQL> COL action          FORMAT A14      HEADING 'Action'
SQL> COL stat_name       FORMAT A32      HEADING 'Statistic' WRAP
SQL> COL value           FORMAT 9999999  HEADING 'Value'
SQL> SELECT
  2       MODULE
  3      ,action
  4      ,stat_name
  5      ,VALUE
  6    FROM v$serv_mod_act_stats
  7   WHERE value <> 0
  8   ORDER BY
  9       MODULE
 10      ,action
 11      ,stat_name;

Module             Action         Statistic                           Value
------------------ -------------- -------------------------------- --------
AddNewOrder        Initialization DB CPU                               1795
AddNewOrder        Initialization DB time                              1795
AddNewOrder        Initialization execute count                          10
AddNewOrder        Initialization opened cursors cumulative               9
AddNewOrder        Initialization parse count (total)                     9
AddNewOrder        Initialization parse time elapsed                    345
AddNewOrder        Initialization sql execute elapsed time             1161
AddNewOrder        Initialization user calls                             12

8 rows selected.
 
----- 
-- Listing 1.6: Setting up an alert about a server's status with
--              DBMS_SERVER_ALERT
-----

-----
-- Set "tablespace full" warning and critical thresholds of 10% and 20% 
-- respectively for USERS tablespace. (Note that these are absurdly low warning 
-- and critical threshold values, but are set low for illustration purposes.)
-----
BEGIN
    DBMS_SERVER_ALERT.SET_THRESHOLD(
         metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL
        ,warning_operator => DBMS_SERVER_ALERT.OPERATOR_GE
        ,warning_value => '10'
        ,critical_operator => DBMS_SERVER_ALERT.OPERATOR_GE
        ,critical_value => '20'
        ,observation_period => 1
        ,consecutive_occurrences => 3
        ,instance_name => 'ZDCDB'
        ,object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE
        ,object_name => 'USERS'
     );
END;
/

----- 
-- Listing 1.7: Show threshold settings, including recent alerts
-----

-- Are there any outstanding alerts?
SELECT *
  FROM dba_outstanding_alerts;

-- Display the arguments for alert thresholds
SELECT *
  FROM sys.dba_alert_arguments;

-- Show threshold alert history for last 12 hours
SELECT 
     object_type
    ,object_name
    ,reason
    ,suggested_action
    ,time_suggested
    ,resolution
    ,advisor_name
    ,metric_value
    ,message_type
    ,message_group
    ,message_level
  FROM dba_alert_history
 WHERE creation_time >= SYSDATE - 12/24
   AND resolution <> 'cleared'
 ORDER BY time_suggested DESC;

-- Show selected thresholds, including default system thresholds
COL metrics_name        FORMAT A24      HEADING 'Metrics Name'
COL warning             FORMAT A08      HEADING 'Warning'
COL critical            FORMAT A08      HEADING 'Danger!'
COL obs_per             FORMAT 99999    HEADING 'Obsv|Per'
COL cons_occur          FORMAT 99999    HEADING 'Cons|Occur'
COL object_type         FORMAT A10      HEADING 'Object|Type'
COL object_name         FORMAT A10      HEADING 'Object|Name'

SELECT 
     metrics_name
    ,warning_operator || ' ' || warning_value warning
    ,critical_operator || ' ' || critical_value critical
    ,observation_period obs_per
    ,consecutive_occurrences cons_occur
    ,object_type
    ,object_name
  FROM sys.dba_thresholds
 WHERE object_type = 'TABLESPACE';