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? |
|
