In Part 1 I mentioned how pl/sql is empowered by new supplied packages and the importance of using them.
In Part 2 I mentioned;
1- Monitoring batch processes “kindly” – DBMS_APPLICATION_INFO package
2- Lempel-Ziv compression of RAW and BLOB data – UTL_COMPRESS package
3- Which Oracle version I am developing on – DBMS_DB_VERSION package and PL/SQL Conditional Compilation
So we continue with some more new packages;
4- Handling Data Errors with an Error Logging Table
DML error logging extends existing DML functionality by enabling you to specify the name of an error logging table into which Oracle should record errors encountered during DML operations. This enables you to complete the DML operation in spite of any errors, and to take corrective action on the erroneous rows at a later time.
Lets look at this simple example first, here I want to take the data of all_tables into T table. But there is a check constraint, blocks<10. Because of the automicity rule below insert statement fails and T destination table has no rows;
DROP TABLE t PURGE ; CREATE TABLE t AS SELECT * FROM all_tables WHERE 1=2; ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (owner, table_name); ALTER TABLE t ADD CONSTRAINT cc_t CHECK (blocks < 10); INSERT /*+ APPEND */ INTO t SELECT * FROM all_tables; ORA-02290: check constraint (CLON.CC_T) violated SELECT COUNT(*) FROM t; COUNT(*) ---------- 0
Prior to 10g we could handle this with PL/SQL exceptions, but this would have some performance cost which we will discuss in the second part. But with 10g we can create an error log table on table T and the exceptional rows automatically inserted into this table;
exec dbms_errlog.create_error_log('T');
desc err$_t
Name Type Nullable Default Comments
------------------------- -------------- -------- ------- --------
ORA_ERR_NUMBER$ NUMBER Y
ORA_ERR_MESG$ VARCHAR2(2000) Y
ORA_ERR_ROWID$ UROWID(4000) Y
ORA_ERR_OPTYP$ VARCHAR2(2) Y
ORA_ERR_TAG$ VARCHAR2(2000) Y
OWNER VARCHAR2(4000) Y
TABLE_NAME VARCHAR2(4000) Y
TABLESPACE_NAME VARCHAR2(4000) Y
CLUSTER_NAME VARCHAR2(4000) Y
...
INSERT /*+ APPEND */ INTO t
SELECT * FROM all_tables
LOG ERRORS REJECT LIMIT UNLIMITED;
COMMIT;
SELECT COUNT(*) FROM t;
COUNT(*)
----------
2386
SELECT COUNT(*) FROM err$_t;
COUNT(*)
----------
3238
Mandatory columns of the error log table are described as follows;
Column Name Data Type Description ORA_ERR_NUMBER$ NUMBER Oracle error number ORA_ERR_MESG$ VARCHAR2(2000) Oracle error message text ORA_ERR_ROWID$ ROWID Rowid of the row in error update and delete only) ORA_ERR_OPTYP$ VARCHAR2(2) Type of operation I = insert, U = update, D = delete ORA_ERR_TAG$ VARCHAR2(2000) User supplied tag
So we are always trying to do it with a single SQL if possible, so lets compare this scenerio with PL/SQL bulk operations;
drop table sales_src purge ;
drop table sales_target purge ;
drop table err$_sales_target purge ;
create table sales_src nologging parallel 2 as select * from all_source ;
create table sales_target nologging parallel 2 as select * from all_source where 1 = 2 ;
ALTER TABLE sales_target ADD CONSTRAINT cc_sales_target CHECK (line < 5000);
set serveroutput on SIZE UNLIMITED
set timing on
exec runstats_pkg.rs_start;
-- step 1 - pl/sql bulk operastions and exception handling
DECLARE
TYPE ARRAY IS TABLE OF sales_target%ROWTYPE INDEX BY BINARY_INTEGER;
sales_src_arr ARRAY;
errors NUMBER;
l_cnt NUMBER := 0;
l_bulk_limit NUMBER := 1000;
bulk_error EXCEPTION;
PRAGMA EXCEPTION_INIT(bulk_error, -24381);
CURSOR c IS
SELECT * FROM sales_src;
BEGIN
OPEN c;
LOOP
FETCH c BULK COLLECT
INTO sales_src_arr LIMIT l_bulk_limit ;
BEGIN
FORALL i IN 1 .. sales_src_arr.COUNT SAVE EXCEPTIONS
INSERT /*+ APPEND */ INTO sales_target VALUES sales_src_arr (i);
EXCEPTION
WHEN bulk_error THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
l_cnt := l_cnt + errors;
FOR i IN 1 .. errors LOOP
dbms_output.put_line('error iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || ' oracle error IS ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
END LOOP;
END;
EXIT WHEN c%NOTFOUND;
END LOOP;
CLOSE c;
dbms_output.put_line(l_cnt || ' total errors');
END;
/
..
error iteration 877 oracle error IS 2290
error iteration 878 oracle error IS 2290
error iteration 879 oracle error IS 2290
1441 total errors
PL/SQL procedure successfully completed.
Elapsed: 00:00:18.39
ROLLBACK;
Rollback complete.
Elapsed: 00:00:13.40
exec runstats_pkg.rs_middle;
-- step 2 - Single Insert and Error Log Table Solution with 10g
exec dbms_errlog.create_error_log(upper('sales_target'));
INSERT /*+ APPEND */
INTO sales_target
SELECT * FROM sales_src log
LOG ERRORS
REJECT LIMIT UNLIMITED
/
153136 rows created.
Elapsed: 00:00:13.81
ROLLBACK;
Rollback complete.
Elapsed: 00:00:00.00
exec runstats_pkg.rs_stop(1000);
Run1 ran in 3183 hsecs
Run2 ran in 1437 hsecs
run 1 ran in 221,5% of the time
select count(*) from err$_sales_target ;
COUNT(*)
----------
1441
SELECT * FROM err$_sales_target WHERE rownum < 2;
ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ OWNER NAME TYPE LINE TEXT
2290 ORA-02290: check constraint (HR.CC_SALES_TARGET) violated I SYS DBMS_BACKUP_RESTORE PACKAGE 5190 -- fname - Datafile copy name
5- Automatic SQL Tuning
Automatic SQL Tuning is a new capability of the query optimizer that automates the entire SQL tuning process. Using the newly enhanced query optimizer to tune SQL statements, the automatic process replaces manual SQL tuning, which is a complex, repetitive, and time-consuming function. The Automatic SQL Tuning features are exposed to the user with the SQL Tuning Advisor.
While the recommended interface for the SQL Tuning Advisor is the Oracle Enterprise Manager Database Control, the advisor can be administered with procedures in the DBMS_SQLTUNE package.
Running SQL Tuning Advisor using DBMS_SQLTUNE package is a multi-step process:
1. Create a SQL Tuning Set (if tuning multiple SQL statements)
2. Create a SQL tuning task
3. Execute a SQL tuning task
4. Display the results of a SQL tuning task
5. Implement recommendations as appropriate
set serveroutput on
– Creating a SQL Tuning Task
DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := ‘SELECT /*+ ORDERED */ * ‘ ||
‘FROM employees e, locations l, departments d ‘ ||
‘WHERE e.department_id = d.department_id AND ‘ ||
‘l.location_id = d.location_id AND ‘ ||
‘e.employee_id < :bnd';
-- uncomment to re-run
-- DBMS_SQLTUNE.drop_tuning_task(task_name => ‘my_sql_tuning_task’);
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => ‘HR’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘my_sql_tuning_task’,
description => ‘Task to tune a query on a specified employee’);
END;
/
– Executing a SQL Tuning Task
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘my_sql_tuning_task’ );
END;
/
– Checking the Progress of the SQL Tuning Advisor
SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE username = ‘HR’ AND task_id =
(SELECT TASK_ID FROM USER_ADVISOR_TASKS WHERE task_name = ‘my_sql_tuning_task’);
– Checking the Status of a SQL Tuning Task
SELECT TASK_ID, status FROM USER_ADVISOR_TASKS WHERE task_name = ‘my_sql_tuning_task’;
– Displaying the Results of a SQL Tuning Task
SET LONG 10000
SET LONGCHUNKSIZE 10000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘my_sql_tuning_task’)
FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
--------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task
Tuning Task Owner : HR
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 01/05/2007 14:33:38
Completed at : 01/05/2007 14:33:43
Number of SQL Profile Findings : 1
Number of SQL Restructure Findings: 1
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
--------------------------------------------------------------------------------
--------------------
-------------------------------------------------------------------------------
Schema Name: HR
SQL ID : dg7nfaj0bdcvk
SQL Text : SELECT /*+ ORDERED */ * FROM employees e, locations l,
departments d WHERE e.department_id = d.department_id AND
l.location_id = d.location_id AND e.employee_id < :bnd
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 33,55%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task', replace => TRUE);
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
2- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 3 of the
execution plan.
Recommendation
--------------
- Consider removing the "ORDERED" hint.
Rationale
---------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
----------------------------------------------------------------------------------------------------
The "ORDERED" hint might force the optimizer to generate a cartesian
product. A cartesian product should be avoided whenever possible because
it is an expensive operation and might produce a large amount of data.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
...
6- Hashing and Encryption Capabilities
DBMS_CRYPTO provides an interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs running network communications. It provides support for several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm. AES has been approved by the National Institute of Standards and Technology (NIST) to replace the Data Encryption Standard (DES).
While encryption is not the ideal solution for addressing a number of security threats, it is clear that selectively encrypting sensitive data before storage in the database does improve security. Examples of such data could include:
* Credit card numbers
* National identity numbers
conn / as sysdba
grant execute on dbms_crypto to hr;
conn hr/hr
set serveroutput on
DECLARE
l_credit_card_no VARCHAR2(19) := '1234-5678-9012-3456';
l_ccn_raw RAW(128) := utl_raw.cast_to_raw(l_credit_card_no);
l_key RAW(128) := utl_raw.cast_to_raw('abcdefgh');
l_encrypted_raw RAW(2048);
l_decrypted_raw RAW(2048);
BEGIN
dbms_output.put_line('Original : ' || l_credit_card_no);
l_encrypted_raw := dbms_crypto.encrypt(l_ccn_raw,
dbms_crypto.des_cbc_pkcs5,
l_key);
dbms_output.put_line('Encrypted : ' ||
RAWTOHEX(utl_raw.cast_to_raw(l_encrypted_raw)));
l_decrypted_raw := dbms_crypto.decrypt(src => l_encrypted_raw,
typ => dbms_crypto.des_cbc_pkcs5,
key => l_key);
dbms_output.put_line('Decrypted : ' ||
utl_raw.cast_to_varchar2(l_decrypted_raw));
END;
/
Original : 1234-5678-9012-3456
Encrypted :
38303843354144343732323245303145433338393341423337343543464345393641364335414434
4245454345424136
Decrypted : 1234-5678-9012-3456
PL/SQL procedure successfully completed.
DECLARE
enc_val RAW(2000);
l_key RAW(2000);
l_key_len NUMBER := 128 / 8; -- convert bits to bytes
l_mod NUMBER := dbms_crypto.ENCRYPT_AES128 + dbms_crypto.CHAIN_CBC +
dbms_crypto.PAD_PKCS5;
BEGIN
l_key := dbms_crypto.randombytes(l_key_len);
enc_val := dbms_crypto.encrypt(utl_i18n.string_to_raw('1234-5678-9012-3456',
'AL32UTF8'),
l_mod,
l_key);
dbms_output.put_line(enc_val);
END;
/
D2743700936E23C1CA7CC802022BA7C7954ED0B2DAE274958B56ED6FF4A0DAE5
PL/SQL procedure successfully completed.
Another simple example to demostrate hashing and storing in database;
CREATE TABLE security ( data RAW(2000) );
/* Hash calculating function.
* Takes a string of data as its argument.
* Returns a hash in raw format.
*/
CREATE OR REPLACE FUNCTION return_hash(data IN VARCHAR) RETURN RAW IS
BEGIN
/* Call HASH() function, which takes data in RAW format.
* Must use STRING_TO_RAW() to convert data.
* HASH_SH1 or HASH_MD5 are constants representing types of hashes to calculate.
*/
RETURN DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW (data, 'AL32UTF8'), DBMS_CRYPTO.HASH_SH1);
END;
/
INSERT INTO security (data) VALUES (return_hash('TONGUC')) ;
SELECT * FROM security ;
DATA
--------------------------------------------------------------------------------
D8A3068DCFBC27341808B206F6CFFB674F7200EE
7- PL/SQL Compile-Time Warnings
To make your programs more robust and avoid problems at run time, you can turn on checking for certain warning conditions. These conditions are not serious enough to produce an error and keep you from compiling a subprogram. They might point out something in the subprogram that produces an undefined result or might create a performance problem.
To work with PL/SQL warning messages, you use the PLSQL_WARNINGS initialization parameter, the DBMS_WARNING package, and the USER/DBA/ALL_PLSQL_OBJECT_SETTINGS views.
set linesize 120 col name format a40 col value format a40 -- current warning state -- PLSQL_WARNINGS enables or disables the reporting of warning messages by the PL/SQL compiler, and specifies which warning messages to show as errors. -- Default value 'DISABLE:ALL' SELECT name, value FROM gv$parameter WHERE name LIKE 'plsql%warn%'; NAME VALUE ---------------------------------------- ---------------------------------------- plsql_warnings DISABLE:ALL -- current optimizer level for the instance -- OPTIMIZER_DYNAMIC_SAMPLING controls the level of dynamic sampling performed by the optimizer. -- Default value If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2 SELECT name, value FROM gv$parameter WHERE name = 'optimizer_dynamic_sampling'; NAME VALUE ---------------------------------------- ---------------------------------------- optimizer_dynamic_sampling 2 col plsql_code_type format a30 col plsql_debug format a30 col plsql_warnings format a50 col nls_length_semantics format a30 -- USER_PLSQL_OBJECT_SETTINGS displays compiler settings for all stored objects in the database. SELECT DISTINCT TYPE, plsql_optimize_level, plsql_code_type, plsql_debug, plsql_warnings, nls_length_semantics FROM user_plsql_object_settings; TYPE PLSQL_OPTIMIZE_LEVEL PLSQL_CODE_TYPE PLSQL_DEBUG PLSQL_WARNINGS NLS_LENGTH_SEMANTICS ------------ -------------------- ------------------------------ ------------------------------ -------------------------------------------------- ------------------------------ PACKAGE BODY 2 INTERPRETED FALSE DISABLE:ALL BYTE PROCEDURE 2 INTERPRETED FALSE ENABLE:INFORMATIONAL,DISABLE:PERFORMANCE,DISABLE:S BYTE ...
First example with DBMS_WARNING package;
-- Check the current warning setting
SELECT DBMS_WARNING.get_warning_setting_string() FROM DUAL;
DBMS_WARNING.GET_WARNING_SETTING_STRING()
-----------------------------------------
DISABLE:ALL
-- When warnings disabled, the following procedure compiles with no warnings
CREATE OR REPLACE PROCEDURE unreachable_code AS
l_dummy VARCHAR2(10) := '1';
BEGIN
IF 1=1 THEN
SELECT '2'
INTO l_dummy
FROM dual;
ELSE
RAISE_APPLICATION_ERROR(-20000, ‘l_dummy != 1!’);
END IF;
END unreachable_code;
/
Procedure created.
-- enable all warning messages for this session
CALL DBMS_WARNING.set_warning_setting_string('ENABLE:ALL' ,'SESSION');
Call completed.
-- Recompile the procedure and a warning about unreachable code displays
ALTER PROCEDURE unreachable_code COMPILE;
SP2-0805: Procedure altered with compi
SHOW ERRORS;
Errors for PROCEDURE UNREACHABLE_CODE:
LINE/COL ERROR
-------- -----------------------------
9/9 PLW-06002: Unreachable code
Another example with “alter session” this time;
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
CREATE OR REPLACE PROCEDURE nocopy_warn(sa IN OUT dbms_sql.varchar2a) IS
BEGIN
dbms_output.put_line('This is a test');
END nocopy_warn;
/
Procedure created.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE nocopy_warn(sa IN OUT dbms_sql.varchar2a) IS
BEGIN
dbms_output.put_line('This is a test');
END nocopy_warn;
/
SP2-0804: Procedure created with compilation warnings
SQL> sho err
Errors for PROCEDURE NOCOPY_WARN:
LINE/COL ERROR
-------- ------------------------------------------------------------
1/23 PLW-07203: parameter 'SA' may benefit from use of the NOCOPY
compiler hint
CREATE OR REPLACE PROCEDURE nocopy_warn(sa IN OUT NOCOPY dbms_sql.varchar2a) IS
BEGIN
dbms_output.put_line('This is a test');
END nocopy_warn;
/
Procedure created.
– Instance or session level.
ALTER SYSTEM SET PLSQL_WARNINGS=’ENABLE:ALL’;
ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL’;
ALTER SESSION SET PLSQL_WARNINGS=’DISABLE:PERFORMANCE’;
– Recompile with extra checking.
ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS=’ENABLE:PERFORMANCE’;
– Set mutiple values.
ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:SEVERE’,'DISABLE:PERFORMANCE’,'DISABLE:INFORMATIONAL’;
Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0
Refences Used :
http://www.psoug.org/reference/dbms_errlog.html
Oracle® Database Performance Tuning Guide
10g Release 2 (10.2) Chapter 12 Automatic SQL Tuning
http://www.psoug.org/reference/dbms_sqltune.html
Oracle® Database Security Guide
10g Release 2 (10.2) Chapter 17 Developing Applications Using Data Encryption
http://www.psoug.org/reference/dbms_crypto.html
How To Encrypt Data in Oracle Using PHP by Larry Ullma
Oracle® Database PL/SQL User’s Guide and Reference 10g Release 2 (10.2) Chapter 10 Handling PL/SQL Errors
http://www.psoug.org/reference/dbms_warning.html
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 
2 Comments