/*
|| Oracle 10g PL/SQL and SQL Enhancements - Listing 2
||
|| Contains examples of new Oracle 10g PL/SQL and SQL features, including:
||
|| - Recompiling invalidated objects with UTL_RECOMP
|| - Using UTL_I18N for character set conversion and data conversion
|| - Compressing binary files with UTL_COMPRESS
|| - Transferring binary files between servers with DBMS_FILE_TRANSFER
|| - Using enhanced features of UTL_FILE
|| - Sending e-mail with UTL_MAIL
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g's 
|| new PL/SQL utility procedures and should be carefully proofread before 
|| executing it against any existing Oracle database to insure that no
|| potential damage can occur.
||
*/
 
-----
-- Listing 2.1: Speeding recompilations with UTL_RECOMP
-----

-- Remember to connect as SYSDBA!
CONNECT SYS / AS SYSDBA;

-- Recompile all objects serially
EXEC UTL_RECOMP.RECOMP_SERIAL();

-- Recompile all objects owned by HR serially
EXEC UTL_RECOMP.RECOMP_SERIAL('HR');

-- Recompile all objects using a specified number of CPU threads
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);

-- Recompile all objects in the SH schema, but let Oracle use all
-- threads specified in JOB_QUEUE_PROCESSES
EXEC UTL_RECOMP.RECOMP_PARALLEL(NULL, 'SH');

 
-----
-- Listing 2.2: Exploring UTL_I18N's character conversion functions
-----
SET SERVEROUTPUT ON LIMIT 100000
DECLARE
    converted_vstring    VARCHAR2(255);
    converted_nstring    NVARCHAR2(255);
    converted_raw        RAW(32);

BEGIN
    -----
    -- Convert RAW data from valid Oracle character set to a VARCHAR2
    -- string in the database's character set
    -----

    converted_vstring := UTL_I18N.RAW_TO_CHAR(HEXTORAW('616262646566C2AA'), 'UTF8');
    DBMS_OUTPUT.PUT_LINE('Converted string is ' || converted_vstring);
    
    -----
    -- Convert RAW data from valid Oracle character set to an NVARCHAR2
    -- string in the national character set
    -----

    converted_nstring := UTL_I18N.RAW_TO_NCHAR(HEXTORAW('616262646566C2AA'), 'WE8MSWIN1252');
    DBMS_OUTPUT.PUT_LINE('Converted national character set string is ' || converted_nstring);

    -----
    -- Convert VARCHAR2 data to RAW datatype
    -----
    converted_raw := UTL_I18N.STRING_TO_RAW('abcdefg', 'WEMSWIN1252');
    DBMS_OUTPUT.PUT_LINE('String converted to raw value is ' || RAWTOHEX(converted_raw));

END;
/

 
-----
-- Listing 2.3: Using UTL_I18N to obtain characterset information 
--              from the database and convert between Oracle LANGUAGE.TERRITORY
--              and ISO information metadata
-----
SET SERVEROUTPUT ON LIMIT 100000
DECLARE
    dflt_charset    VARCHAR2(32);
    iana_charset    VARCHAR2(32);
    ora_charset     VARCHAR2(32);
    language        VARCHAR2(32);
    locale          VARCHAR2(32);
    territory       VARCHAR2(32);

BEGIN

    -----
    -- Utilities for finding "safe" character sets for e-mailing and 
    -- translation
    -----

    -- What's the safe characterset for:
    -- ... Spanish generic context in a non-Windows environment?
    dflt_charset := 
        UTL_I18N.GET_DEFAULT_CHARSET(
             language => 'Spanish'
            ,context =>  UTL_I18N.GENERIC_CONTEXT
            ,iswindows => FALSE
            );
    DBMS_OUTPUT.PUT_LINE('Spanish Generic For LINUX: ' || dflt_charset);
    
    -- ... Italian e-mail context in a Windows environment?
    dflt_charset := 
        UTL_I18N.GET_DEFAULT_CHARSET(
             language => 'Italian'
            ,context =>  UTL_I18N.MAIL_CONTEXT
            ,iswindows => TRUE
            );
    DBMS_OUTPUT.PUT_LINE('Italian E-Mail For Windows: ' || dflt_charset);

    -- French e-mail context in a non-Windows environment?
    dflt_charset := 
        UTL_I18N.GET_DEFAULT_CHARSET(
             language => 'Spanish'
            ,context =>  UTL_I18N.GENERIC_CONTEXT
            ,iswindows => FALSE
            );
    DBMS_OUTPUT.PUT_LINE('French E-Mail For UNIX: ' || dflt_charset);

    -----
    -- Utilities for converting Internet Assigned Numbers Authority (IANA) 
    -- locales and charactersets to corresponding Oracle language, territory,
    -- and characterset constants
    -----
    
    -- What IANA character set corresponds to Oracle's US7ASCII character set?
    iana_charset := 
        UTL_I18N.MAP_CHARSET(
             charset => 'US7ASCII' 
            ,context => UTL_I18N.GENERIC_CONTEXT
            ,flag => UTL_I18N.ORACLE_TO_IANA
    );
    DBMS_OUTPUT.PUT_LINE('IANA characterset for US7ASCII: ' || iana_charset);

    -- What Oracle character set corresponds to IANA's ISO-8859-1 character set?
    ora_charset := 
        UTL_I18N.MAP_CHARSET(
             charset => 'iso-8859-1' 
            ,context => UTL_I18N.GENERIC_CONTEXT
            ,flag => UTL_I18N.IANA_TO_ORACLE
    );
    DBMS_OUTPUT.PUT_LINE('Oracle characterset for ISO-8859-1: ' || ora_charset);

    -- What Oracle language and territory designations corresponds to ISO's 
    -- en_US locale?
    language := 
        UTL_I18N.MAP_LANGUAGE_FROM_ISO(
             isolocale => 'en_US' 
    );
    territory := 
        UTL_I18N.MAP_LANGUAGE_FROM_ISO(
             isolocale => 'en_US' 
    );
    DBMS_OUTPUT.PUT_LINE('Oracle Language.Territory: ' || language || '.' || territory);

    -- What ISO locale corresponds to Oracle's AMERICAN.AMERICAN language and territory?
    locale := 
        UTL_I18N.MAP_LOCALE_TO_ISO(
             ora_language => 'American' 
            ,ora_territory => 'America' 
    );
    DBMS_OUTPUT.PUT_LINE('ISO Locale: ' || locale);
    
END;
/

 
-----
-- Listing 2.4: Reading and writing files with UTL_FILE and a DIRECTORY object.
-----

-- Create a new directory object. Note that the directory folder need not 
-- exist for this command to succeed, but any subsequent attempt to utilize 
-- the DIRECTORY object will fail until the folder is created on the server.
-- This should be run from SYSTEM for best results

DROP DIRECTORY log_files_dir;
CREATE DIRECTORY log_files_dir as '/u02/logfiles';
GRANT READ, WRITE ON DIRECTORY log_files_dir TO hr, sh, system;

-- Write to an external file in newly-defined directory
SET SERVEROUTPUT ON SIZE 100000
DECLARE
    fid UTL_FILE.FILE_TYPE;
    bfr VARCHAR2(32767);
    CURSOR curTables IS
        SELECT owner, table_name
          FROM dba_tables
         WHERE owner = 'HR';
BEGIN 
    -- Open a new log file using the new DIRECTORY format
    fid := 
        UTL_FILE.FOPEN(
             location => 'LOG_FILES_DIR'
            ,filename => 'HR_Tables.lst'
            ,open_mode => 'w'
            ,max_linesize => 1024);

    -- Write logging entries to the open file
    bfr := '>>> HR Schema Table List <<<';
    UTL_FILE.PUT_LINE(fid, bfr, TRUE);

    FOR recTable IN curTables
        LOOP
            UTL_FILE.PUTF(fid, '> Table: %s.%s\n', recTable.owner, recTable.table_name);
        END LOOP;

    bfr := '>>> End of List <<<';
    UTL_FILE.PUT_LINE(fid, bfr, TRUE);

    -- Close all opened files
    UTL_FILE.FCLOSE_ALL;    

    EXCEPTION
        WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('Invalid path!');
        WHEN UTL_FILE.INVALID_MODE THEN DBMS_OUTPUT.PUT_LINE('Invalid mode!');
        WHEN UTL_FILE.INVALID_OPERATION THEN DBMS_OUTPUT.PUT_LINE('Invalid operation!');
        WHEN UTL_FILE.INVALID_MAXLINESIZE THEN DBMS_OUTPUT.PUT_LINE('Incorrect Line Size!');
        WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Other unspecified failure');

END;
/

-- Read from the newly-created external file
SET SERVEROUTPUT ON SIZE 100000

DECLARE
    fid UTL_FILE.FILE_TYPE;
    bfr VARCHAR2(32767);
    eof BOOLEAN := FALSE;

BEGIN 
    -- Open the log file for reading
    fid := 
        UTL_FILE.FOPEN(
             location => 'LOG_FILES_DIR'
            ,filename => 'HR_Tables.lst'
            ,open_mode => 'r'
            ,max_linesize => 1024);

    -- Read rows and write them to SYSOUT
    WHILE NOT eof
        LOOP
            UTL_FILE.GET_LINE(fid, bfr, 255);
            IF SUBSTR(bfr,1,3) <> '>>>' THEN 
                DBMS_OUTPUT.PUT_LINE(bfr);
            END IF;
        END LOOP;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN UTL_FILE.FCLOSE_ALL;  
        WHEN UTL_FILE.INVALID_PATH THEN DBMS_OUTPUT.PUT_LINE('Invalid path!');
        WHEN UTL_FILE.INVALID_MODE THEN DBMS_OUTPUT.PUT_LINE('Invalid mode!');
        WHEN UTL_FILE.INVALID_OPERATION THEN DBMS_OUTPUT.PUT_LINE('Invalid operation!');
        WHEN UTL_FILE.INVALID_MAXLINESIZE THEN DBMS_OUTPUT.PUT_LINE('Incorrect Line Size!');
        WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Other unspecified failure');
    
END;
/

 
-----
-- Listing 2.5: Transferring binary files within servers and between 
--              servers using DBMS_FILE_TRANSFER. Binary file sizes 
--              must be a multiple of 512 bytes, and cannot be larger
--              than two terabytes (2TB).
-----

BEGIN

    -----
    -- Copy a file within a local server to one server to another.
    -- The user executing this procedure must have read access to
    -- the source directory, and write access to the destination 
    -- directory.
    -----
    DBMS_FILE_TRANSFER.COPY_FILE (
         source_directory_object => 'EXPORT_DIR'
        ,source_file_name => 'FULLSCHEMA.DMP'
        ,destination_directory_object => 'EXPORT_DIR'
        ,destination_file_name => 'FULLSCHEMA.BKP'
    );

    -----
    -- Copy a file from the local server to a remote server.
    -- The user executing this procedure must have read access to
    -- the source directory on the local server, and write access to
    -- the destination directory on the remote server.
    -----
    DBMS_FILE_TRANSFER.PUT_FILE (
         source_directory_object => 'EXPORT_DIR'
        ,source_file_name => 'FULLSCHEMA.DMP'
        ,destination_directory_object => 'EXPORT_DIR'
        ,destination_file_name => 'FULLSCHEMA.BKP'
        ,destination_database => 'ZDC_BKPSRVR'
    );

    -----
    -- Copy a file from a remote server to the local server.
    -- The user executing this procedure must have read access to
    -- the source directory on the remote server, and write access to
    -- the destination directory on the local server.
    -----
    DBMS_FILE_TRANSFER.GET_FILE (
         source_directory_object => 'EXPORT_DIR'
        ,source_file_name => 'FULLSCHEMA.BKP'
        ,source_database => 'ZDC_DBSRVR'
        ,destination_directory_object => 'EXPORT_DIR'
        ,destination_file_name => 'FULLSCHEMA.DMP'
    );
    
END;
/

 
-----
-- Listing 2.6: Sending e-mail with UTL_MAIL. 
--              Remember that this package must be installed separately via:
--                + $ORACLE_HOME/rdbms/admin/utlmail.sql
--                + ORACLE_HOME/rdbms/admin/prvtmail.plb
--              and that either the SMTP_OUT_SERVER initialization must be set,
--              or UTL_MAIL will invoke the default server from the DB_DOMAIN
--              parameter.
-----

DECLARE
    CRLF        CHAR(2) := CHR(10) || CHR(13);
    raw_att     RAW(32) := HEXTORAW('616262646566C2AA');
    vcr_att     VARCHAR2(255) := 'This is a sample of a VARCHAR2 attachment!';
    
BEGIN 

    -- Send a simple e-mail message from within the database
    UTL_MAIL.SEND(
         sender => 'oradba@zerodefectcomputing.com'
        ,recipients => 'bgates@microsoft.com'
        ,cc => 'lellison@oracle.com'
        ,bcc =>  NULL
        ,subject => 'When is LongHorn due out? Really??'
        ,message => 'Dear Mr. Gates: ' || CRLF || CRLF
        || 'I would really love to play with LongHorn soon!' || CRLF || CRLF
        || 'Kind regards,' || CRLF || CRLF
        || 'Jim Czuprynski'
        ,mime_type => 'text/plain; charset=us-ascii'
        ,priority => 1
    );
    
    -- Send an e-mail message with a RAW attachment
    UTL_MAIL.SEND_ATTACH_RAW(
         sender => 'oradba@zerodefectcomputing.com'
        ,recipients => 'lellison@oracle.com'
        ,cc => 'bgates@microsoft.com'
        ,bcc =>  NULL
        ,subject => 'FWD: When is LongHorn due out? Really??'
        ,message => 'Dear Mr. Ellison: ' || CRLF || CRLF
        || 'I thought you'd like me to rub this in a bit.' || CRLF || CRLF
        || 'Kind regards,' || CRLF || CRLF
        || 'Jim Czuprynski'
        ,mime_type => 'text/plain; charset=us-ascii'
        ,priority => 2
        ,attachment => raw_att
        ,att_inline => TRUE
        ,att_mime_type => 'application/octet'
        ,att_filename => 'raw_file.txt'
    );
    
    -- Send an e-mail message with a VARCHAR2 attachment
    UTL_MAIL.SEND_ATTACH_VARCHAR2(
         sender => 'oradba@zerodefectcomputing.com'
        ,recipients => 'bgates@microsoft.com'
        ,cc => 'lellison@oracle.com'
        ,bcc =>  NULL
        ,subject => 'RE: When is LongHorn due out? Really??'
        ,message => 'Dear Mr. Gates: ' || CRLF || CRLF
        || 'Just kidding about rubbing it in.' || CRLF || CRLF
        || 'Kind regards,' || CRLF || CRLF 
        || 'Jim Czuprynski'
        ,mime_type => 'text/plain; charset=us-ascii'
        ,priority => 3
        ,attachment => vcr_att
        ,att_inline => TRUE
        ,att_mime_type => 'application_octet'
        ,att_filename => 'vcr_file.doc'
    );

END;
/