All of the examples of using BMC_DEBUG we’ve seen so far involve making changes to your code to add calls to the BMC_DEBUG procedures and functions, but there will be cases where this is not possible.
For example, if you are running an application supplied by a 3rd party vendor which uses PL/SQL, they may provide a wrapped PL/SQL package for which the source code is not viewable. In this case, it is still possible to use some of the BMC_DEBUG functionality, by creating a ‘proxy’ package which is instrumented, and which forwards procedure and function calls on to the original package.
Imagine we have a live application which uses a PL/SQL package to perform its database operations. The package spec looks like this :-
CREATE OR REPLACE PACKAGE ordering AS
FUNCTION create_order (in_cust_id IN NUMBER,
in_order_value IN NUMBER) RETURN NUMBER;
PROCEDURE process_order (in_order_id IN NUMBER);
END ordering;
But the package body is wrapped, so the source code is not available to modify.
The package and application tables are owned by the user APP_OWNER.
The application connects to a user called APP_USER, which has been granted EXECUTE privilege on APP_OWNER.ORDERING, and has a private synonym for the package. So when the application connects as APP_USER, it can execute ORDERING.CREATE_ORDER and ORDERING.PROCESS_ORDER as needed.
To add instrumentation to such a system without modifying the existing code, we would create a ‘proxy’ package, which the application calls instead of the original package, and which forwards the procedure or function calls to the original package. To implement this we would need to do the following :-
1. Create a new user, which we’ll call APP_OWNER_INST, which will own the instrumented ‘proxy’ package.
2. EXECUTE privilege on the original package would need to be granted to our new user (e.g. GRANT EXECUTE ON APP_OWNER.ORDERING TO APP_OWNER_INST), and the new user would also need EXECUTE privilege on the BMC_DEBUG package and a synonym pointing to it.
3. A new package with same name and spec as the original, but with a package body which includes the calls to BMC_DEBUG BEGINCALL and ENDCALL procedures, would be created in the new APP_OWNER_INST user. For the ORDERING package, it would look like this :-
CREATE OR REPLACE PACKAGE ordering IS
FUNCTION create_order (in_cust_id IN NUMBER,
in_order_value IN NUMBER) RETURN NUMBER;
PROCEDURE process_order (in_order_id IN NUMBER);
END ordering;
CREATE OR REPLACE PACKAGE BODY ordering IS
FUNCTION create_order (in_cust_id IN NUMBER,
in_order_value IN NUMBER) RETURN NUMBER IS
new_order_id NUMBER;
BEGIN
bmc_debug.begincall(‘ORDERING’,’CREATE_ORDER’,’in_cust_id=’||in_cust_id||’, in_order_value=’||in_order_value);
new_order_id := app_owner.create_order(in_cust_id,in_order_value);
bmc_debug.msg(5,’Created new order ID = ‘||new_order_id);
bmc_debug.endcall(‘ORDERING’,’CREATE_ORDER);
RETURN new_order_id;
EXCEPTION
WHEN OTHERS THEN
bmc_error.error_handle();
RAISE;
END create_order;
PROCEDURE process_order (in_order_id IN NUMBER) IS
BEGIN
bmc_debug.begincall(‘ORDERING’,’PROCESS_ORDER’,’in_order_id=’||in_order_id);
app_owner.process_order(in_order_id);
bmc_debug.endcall(‘ORDERING’,’PROCESS_ORDER);
EXCEPTION
WHEN OTHERS THEN
bmc_error.error_handle();
RAISE;
END process_order;
END ordering;
4. Grant EXECUTE on the new APP_OWNER_INST.ORDERING package to the APP_USER user, and change its private synonym for the ORDERING package to point at the APP_OWNER_INST.ORDERING package.
Now when APP_USER executes ORDERING.CREATE_ORDER or ORDERING.PROCESS_ORDER, it will execute the versions in APP_OWNER_INST. These will execute BMC_DEBUG.BEGINCALL to setup the call stack and read the call parameters, and then execute the original procedure or function in the regular APP_OWNER schema, and then call BMC_DEBUG.ENDCALL before returning to the caller.
By doing this, you can :-
Log the start and end of each procedure, and record the parameters which are passed in to each call
Gather any statistics on execution, with call stats including the passed parameters
Ensure unhandled exceptions are recorded in the log table
Monitor the execution on a package/procedure level from V$SESSION or a global context
What you cannot do using this method is add BMC_DEBUG.MSG calls to the procedure bodies, so detailed information on their execution cannot be logged by increasing the DEBUG LEVEL, and will not be available in the message store or in any exception logs.