In Part 1 I mentioned how pl/sql is empowered by new supplied packages and the importance of using them.
Before I proceed with examples I want to mention a great online resource I always use; psoug.org Please check Oracle Built-in Packages and Oracle Built-in Functions lists in library of Mr.Morgan Daniel. I hope to start also another series on Enhanced Built-in Functions soon :)
1- Monitoring batch processes “kindly” :)
So here is one of my favorite packages all time. I use it as much as DBMS_OUTPUT. I first met this package when I needed something for batch process monitoring when I was a DBA. The primary advantage of application info is it involves very minimal redo cost when compared to a heap table you can design for this monitoring purpose. And the results can be immediately seen on v$session columns with simple select statements without any commit need. “kindly” word in the subject comes from these reasons :) Here are some examples;
BEGIN dbms_application_info.set_module(module_name => 'add_employee', action_name => 'insert INTO emp'); INSERT INTO emp (ename, empno) VALUES ('TONG', 1234); dbms_application_info.set_module(NULL, NULL); END; /
The following sample query illustrates the use of the MODULE and ACTION column of the V$SQLAREA.
SELECT sql_text FROM v$sqlarea WHERE module = 'add_employee'; SQL_TEXT ----------------------------------------------------- INSERT INTO EMP (ENAME, EMPNO) VALUES ('TONG', 1234)
You can also read the information via the functions provided;
DBMS_APPLICATION_INFO.READ_CLIENT_INFO ( client_info OUT VARCHAR2 ); DBMS_APPLICATION_INFO.READ_MODULE ( module_name OUT VARCHAR2, action_name OUT VARCHAR2 );
Here is another example how to monitor a long running process with this package, as this example completes each iteration Oracle updates V$SESSION_LONGOPS on the procedure’s progress;
CREATE TABLE test (testcol NUMBER(10)); -- Session 1 SELECT DISTINCT sid FROM gv$mystat; -- use this sid number in the session 2 query below DECLARE rindex BINARY_INTEGER; slno BINARY_INTEGER; sofar NUMBER(6,2); target BINARY_INTEGER; totwork NUMBER := 300; BEGIN rindex := dbms_application_info.set_session_longops_nohint; SELECT object_id INTO target FROM user_objects WHERE object_name = 'TEST'; FOR i IN 1 .. totwork LOOP sofar := i/3; dbms_application_info.set_session_longops(rindex, slno, 'something you want to be seen when queried', target, 0, sofar, 100, 'Pct Complete'); INSERT INTO test VALUES (i); -- to delay the process in order to watch from another session dbms_lock.sleep(0.25); END LOOP; END; / -- Session 2 substitute the sid returned above from session 1 SELECT sofar, totalwork FROM gv$session_longops WHERE sid = 10;
2- Lempel-Ziv compression of RAW and BLOB data
UTL_COMPRESS package can be used on binary data like RAW, BLOB and BFILE for compression and decompression. Like gzip UTL_COMPRESS uses Lempel-Ziv compression algoritm. The package provides a set of data compression utilities. Here is a simple example;
SET SERVEROUTPUT ON DECLARE l_original_blob BLOB; l_compressed_blob BLOB; l_uncompressed_blob BLOB; BEGIN -- initialize both blobs TO something. l_original_blob := to_blob(utl_raw.cast_to_raw('1234567890123456789012345678901234567890')); l_compressed_blob := to_blob('1'); l_uncompressed_blob := to_blob('1'); -- compress THE data. utl_compress.lz_compress(src => l_original_blob, dst => l_compressed_blob); -- uncompress THE data. utl_compress.lz_uncompress(src => l_compressed_blob, dst => l_uncompressed_blob); -- display lengths. dbms_output.put_line('original length :' || length(l_original_blob)); dbms_output.put_line('compressed length :' || length(l_compressed_blob)); dbms_output.put_line('uncompressed length :' || length(l_uncompressed_blob)); -- free temporary blobs. dbms_lob.freetemporary(l_original_blob); dbms_lob.freetemporary(l_compressed_blob); dbms_lob.freetemporary(l_uncompressed_blob); END; / original length :40 compressed length :33 uncompressed length :40 PL/SQL procedure successfully completed
3- Which Oracle version I am developing on
The DBMS_DB_VERSION package specifies the Oracle version numbers and other information useful for simple conditional compilation selections based on Oracle versions. The boolean constants in the package follow a naming convention. Each constant gives a name for a boolean expression. For example:
– VER_LE_9_1 represents version <= 9 and release <= 1
– VER_LE_10_2 represents version <= 10 and release <= 2
– VER_LE_10 represents version <= 10
Each version of Oracle from Oracle Database 10g Release 2 will contain a DBMS_DB_VERSION package with Boolean constants showing absolute and relative version information.
SET SERVEROUTPUT ON BEGIN dbms_output.put_line('VERSION ' || dbms_db_version.version); dbms_output.put_line('RELEASE ' || dbms_db_version.release); IF dbms_db_version.ver_le_10_2 THEN dbms_output.put_line('10gR2 TRUE'); ELSE dbms_output.put_line('10gR2 FALSE'); END IF; END; / VERSION 10 RELEASE 2 10gR2 TRUE PL/SQL procedure successfully completed
Also with 10g there is a new feature called PL/SQL Conditional Compilation This feature is useful for;
– compatibility between releases(10.2 and upwards only),
– trace and debugging,
– testing and quality assurance
By default conditional compilation flag will be NULL, let’s try on this example;
SET SERVEROUTPUT ON CREATE OR REPLACE PROCEDURE p1 AS BEGIN dbms_output.put_line('Before'); $IF $$trace_enabled $THEN dbms_output.put_line('Conditional Code'); $END dbms_output.put_line('After'); END; / EXEC p1; Procedure created Before After PL/SQL procedure successfully completed
Conditional compilation can be enabled as follows:
ALTER PROCEDURE p1 COMPILE PLSQL_CCFLAGS = 'trace_enabled: true' REUSE SETTINGS; EXEC p1; Procedure altered Before Conditional Code After PL/SQL procedure successfully completed
In this example another 10g new feature is used mentioned with Managing Commit Redo Behavior title in the documentation.
BEGIN $IF DBMS_DB_VERSION.VER_le_10_1 $THEN $ERROR 'Unsupported database release or feature!' $END $ELSE DBMS_OUTPUT.PUT_LINE ('Release ' || DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' is supported.'); -- Note that this COMMIT syntax is newly supported in 10.2 COMMIT WRITE IMMEDIATE NOWAIT; $END END; / Release 10.2 is supported. PL/SQL procedure successfully completed
Continue reading with Part 3
Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.2.0
Refences Used : Metalink Note 61722.1 How to use the DBMS_APPLICATION_INFO Package
If only more developers used the DBMS_APPLICATION_INFO package post on oracleandy blog
Action, Module, Program ID and V$SQL… post on Mr.Kytes blog
3 Comments