/*
|| 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;
/