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);
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