Expanded Supplied Packages with 10g – Part 2 – Extract(Read) > Transform(Test&Apply)

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