DBMS_APPLICATION_INFO package

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

Advertisement