Enhancing DBMS_OUTPUT using systemtap

This is a short and quick note to show how we can enhance DBMS_OUTPUT capabilities using a small systemtap script without modifying the source code.Basically it will allow us to display the DBMS_OUTPUT message incrementally (the program don’t need to finish it’s execution) by attaching to an already running session (no need to enable DBMS_OUTPUT). The output can also be easily redirected to a file.

The idea is to try to access the function parameters.This can become complex in case of different arguments types and number but in our case there is only one argument of type varchar2.

TEST ENV : oracle 12.2.0.1/OEL 6 /UEK 4.1/systemtap 3.1

Script :

stap -v -e ‘probe process(“oracle”).function(“pfrinstr_MOVA”).return { printf(“%s\n”,user_string(user_int64(user_int64(register(“rcx”)))) ) } ‘ -x 16413

This is a very simple script that can be enhanced in many ways (ex: targeting only dbms_output package).

Targeting function/procedure with multiple arguments of type strings can also be done by targeting the function “pfrinstr_MOVAN” which read the parameters value in a loop.

That’s it 😀