Sometimes it happens that you want to track and trace your query or block of code in the Shared SQL area (v$sql) or v$session. For this particular purpose, Oracle is armed with the DBMS_APPLICATION_INFO package.
With that, you can tag your codes (even each step individually) so you can better track and monitor your queries. Let’s have a closer look at the procedures you can use.
- SET_MODULE – you can set a module name for a currently running code
- READ_MODULE – you can get a module name for a currently running code
- SET_ACTION – you can set an action name for a currently running code in a module
- SET_CLIENT_INFO – you can set additional information about the process/client
- READ_CLIENT_INFO – you can get additional information about the process/client
- SET_SESSION_LONGOPS – you can log your process execution to track the on-going progress of your application (i.e. database backup progress tracking) in v$session_longops
I will only touch two of them: SET_MODULE and SET_ACTION. These I use the most and the rest is pretty much self-descriptive anyway 🙂
You can use it either for a single query:
-- set the action and module BEGIN dmbs_application_info.set_action('Action name'); -- to describe the action dbms_application_info.set_module('Module name','Action name'); -- params: (Module, Action) END; -- run a query (withing the same window/session) SELECT * FROM oracle_world.employees; -- check the query in the v$sql (module, action) SELECT module , action , s.* FROM v$sql s WHERE module = 'Module name'; -- or you can use your client_info data or action_name of course as well -- or check it in the v$session (module, action, client_info) SELECT module , action , client_info , ssn.* FROM v$session ssn WHERE action = 'Action name'; -- or you can use your client_info data or module_name of course as well
This is a good approach, for example, as a performance tuning tracking (so you can easily identify your query in between the bunch of others.
On the other side, you can use this for tracking/logging complex procedures, packages, … this is a sample withdrawal procedure ..
CREATE PROCEDURE p_sample_withdraw_money( p_id_account NUMBER IN, p_amount NUMBER IN, p_status VARCHAR2 OUT ) AS l_funds account.funds%TYPE; l_err_msg VARCHAR2(255); NOT_ENOUGH_FUNDS EXCEPTION; BEGIN DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'check available funds', action_name => 'ATM Sample withdrawal'); SELECT funds INTO l_funds FROM account WHERE 1=1 AND id_account = p_id_account; IF l_funds < p_amount THEN RAISE NOT_ENOUGH_FUNDS; END IF; DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'process withdrawal', action_name => 'ATM Sample withdrawal'); UPDATE account SET funds = funds - p_amount WHERE 1=1 AND id_account = p_id_account; DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'finishing withdrawal', action_name => 'ATM Sample withdrawal'); p_status := 'Successfully withdrawn'; commit; DBMS_APPLICATION_INFO.SET_MODULE(null,null); EXCEPTION WHEN NOT_ENOUGH_FUNDS THEN p_status := 'Insufficient funds'; l_err_msg := 'You do not have enough funds. Please try a lower amount'; dbms_output.put_line(l_err_msg); RAISE_APPLICATION_ERROR(-20001,l_err_msg); WHEN OTHERS THEN p_status := 'Other error'; dbms_output.put_line('Ooops, something went wrong'); END;