Akdora’s Blog

Programming, Oracle, Life, Fun

DBMS_APPLICATION_INFO package February 11, 2009

Filed under: PL/SQL — Akdora @ 7:15 am
Tags: , , , , , ,

In a project I had to retrieve some data from about 30 different tables and put them together to a new table. I had a main query and retrieve others data by eacy row of this query. It’s a kind of a master query.
This process will be worked per day.
Whhile I was testing my code, I had some problems. Because, We will read a thousand row and I do not know which row is processing. If an error occures, It may lock my PL/SQL development tool.
(The row that retrieving current data may locks in some queries because of performance problems or other problems). It was irritating.

I cannot use dbms_output.put_line because it shows us the result after completing the job. But the test procedure also do not response🙂
In this moment I met with DBMS_APPLICATION_INFO package.

If you work with a complex code and get the current information to debug in any environment (not only dev), you can use dbms_application package.

you just set any value in anywhere of the code:
dbms_application_info.set_client_info(client_info => crow.case_history_id);

For example:

DECLARE
     CURSOR c1 IS
          SELECT * FROM all_source;
BEGIN
     FOR lrowc1 IN c1
     LOOP
          dbms_application_info.set_client_info(client_info => lrowc1.text);
     END LOOP;
END;

You can see the current client value in two method:

1.
SELECT t.client_info, t.* FROM v$session t --WHERE t.username = USER;
2.
DECLARE
     x VARCHAR2(100);
BEGIN
     dbms_application_info.read_client_info(x);
     dbms_output.put_line(x);
END;
You may see the other uses of this package
http://tonguc.wordpress.com/2006/12/29/expanded-supplied-packages-with-10g-part-2/
http://www.psoug.org/reference/dbms_applic_info.html
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s