track the parts of my application that are in use
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
As Solution Using Built-in Packages
When I first started developing applications in Oracle Database 7.3.4 I found some tools that are as valuable today as they were the day the package was first made available. The issue at hand is how to track where users are in a complex code base where procedures call procedures, use functions, fire triggers, and sometimes hang or appear to hang. How to track what is going on is critical for DBAs when the application goes into production and equally valuable for developers through testing.

First lets review the built-in pieces of the puzzle.

dbms_application_info.set_client_info(client_info IN VARCHAR2(64));

dbms_application_info.set_module(module_name IN VARCHAR2(48), action_name IN VARCHAR2(32));

dbms_session.set_identifier(client_id IN VARCHAR2);

There are many additional things one can use this package to do but for this "How Can I" we are going to keep a narrow focus.

So lets see these capabilities in action:
col username format a20
col module format a20
col action format a20
col client_info format a20
col client_identifier format a20

SELECT username, client_info, module, action, client_identifier
FROM gv$session
WHERE service_name <> 'SYS$BACKGROUND'
AND username IS NOT NULL;

exec dbms_application_info.set_client_info('AAA');
exec dbms_application_info.set_module('BBB','CCC');
exec dbms_session.set_identifier('DDD');

SELECT username, client_info, module, action, client_identifier
FROM gv$session
WHERE service_name <> 'SYS$BACKGROUND'
AND username IS NOT NULL;
Looks easy enough and it is. Start a proc or function and make the first line a call to one of these procedures. Leave the procedure and reset the information. And since gv$ or v$ "magic" views are just peeking at memory there is very little overhead.

Lets reset the values to NULL to simulate leaving our PL/SQL code.
SELECT username, client_info, module, action, client_identifier
FROM gv$session
WHERE service_name <> 'SYS$BACKGROUND'
AND username IS NOT NULL;

exec dbms_application_info.set_client_info(NULL);
exec dbms_application_info.set_module(NULL,NULL);
exec dbms_session.clear_identifier;

SELECT username, client_info, module, action, client_identifier
FROM gv$session
WHERE service_name <> 'SYS$BACKGROUND'
AND username IS NOT NULL;
Three of the four clear their values... client_info does not nor does there seem to be a way to clear it: C'est dommage. But no matter the point is that we can use these capabilities to set markers in a easy to view location that can be used to track where we are and, even, how we got there. Lets take a look at a couple of capabilities that will help us in our development.

The first is the OWA_UTIL procedure WHO_CALLED_ME.
owa_util.who_called_me(owner OUT VARCHAR2,name OUT VARCHAR2,lineno
OUT NUMBER, caller_t OUT VARCHAR2);


The second, what Oracle calls predefined inquiry directive:
$$PLSQL_LINE and $$PLSQL_UNIT.

Lets take a look at what they do in the context of the capabilities already demonstrated. We will do so with two stored procedures. One that is the driver for the application "driver" and "testproc" which we will call twice. After the two procedures are built open a second SQL*Plus window. In the first run "driver" (SQL>
exec driver;) and in the second observer the result by running the SELECT from gv$session query repeating the query once each second by entering slash "/" and hitting the <ENTER> key..

Note: If you have not installed userlock (
SQL> ?/rdbms/admin/userlock.sql) this might be a good time to do so.
CREATE OR REPLACE PROCEDURE testproc(iteration IN PLS_INTEGER) IS
 oname VARCHAR2(100);
 pname VARCHAR2(100);
 lnumb VARCHAR2(100);
 callr VARCHAR2(100);
BEGIN
  owa_util.who_called_me(oname, pname, lnumb, callr);

  IF iteration = 1 THEN
    dbms_application_info.set_client_info(oname||'-'||pname||'-'||lnumb||'-'||callr);
    dbms_application_info.set_module($$PLSQL_UNIT,'line: ' || TO_CHAR($$PLSQL_LINE));
    dbms_session.set_identifier(SQLERRM);
  ELSIF iteration = 2 THEN
    dbms_application_info.set_client_info(oname||'-'||pname||'-'||lnumb||'-'||callr);
    dbms_application_info.set_module($$PLSQL_UNIT,'line: ' || TO_CHAR($$PLSQL_LINE));
    dbms_session.set_identifier(SQLERRM);
  ELSIF iteration = 3 THEN
    dbms_application_info.set_client_info(oname||'-'||pname||'-'||lnumb||'-'||callr);
    dbms_application_info.set_module($$PLSQL_UNIT,'line: ' || TO_CHAR($$PLSQL_LINE));
    dbms_session.set_identifier(SQLERRM);
  ELSE
    RAISE no_data_found;
  END IF;
EXCEPTION
  WHEN others THEN
    dbms_session.set_identifier(SQLERRM);
END testproc;
/

CREATE OR REPLACE PROCEDURE driver IS
BEGIN
  testproc(1);
  user_lock.sleep(500);
  testproc(2);
  user_lock.sleep(500);
  testproc(3);
  user_lock.sleep(500);
  testproc(4);
  user_lock.sleep(500);
  dbms_application_info.set_client_info(' ');
  dbms_application_info.set_module(NULL,NULL);
  dbms_session.clear_identifier;
END;
/

SELECT username, client_info, module, action, client_identifier
FROM gv$session
WHERE service_name <> 'SYS$BACKGROUND'
AND username IS NOT NULL;
Take a look at the output. Notice how we can track the line in driver that has called testproc as well as the line of testproc that is executing.

Any questions?
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx