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:

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

You can see the current client value in two method:

SELECT t.client_info, t.* FROM v$session t --WHERE t.username = USER;
     x VARCHAR2(100);
You may see the other uses of this package

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