Using Dynamic Parameters

As we’ve previously seen, BMC_DEBUG parameters such as DEBUG LEVEL are assigned their values by rows in the BMC_DEBUG_PARAMETER table, and one row in the parameter table can apply to one individual procedure, user or session, or to many depending on the values in certain parameter table columns.

It is also possible to apply parameter table values to procedures depending on which parameter values are passed into the procedure as arguments. This could allow, for example, a high DEBUG LEVEL to be applied to the ORDERING.PROCESS_ORDER procedure only when it is called with ORDER_ID 1576, if other instrumentation has shown the execution of the procedure for that order to be particularly slow.

Normally, to assign a DEBUG LEVEL to the ORDERING.PROCESS_ORDER procedure, we would add a row to the BMC_DEBUG_PARAMETER table which looks like this :-

SQL> SELECT param_name,param_value,inherit,package_name,procedure_name,sid,serial#,username

FROM bmc_debug_parameter WHERE param_name = 'DEBUG LEVEL' AND procedure_name = 'PROCESS_ORDER';

PARAM_NAME PARAM_VALUE INHERIT PACKAGE_NAME PROCEDURE_NAME SID SERIAL# USERNAME

------------- ----------- ------- -------------------- --------------------- --- ------- --------

DEBUG LEVEL 9 N ORDERING PROCESS_ORDER

So this would assign DEBUG LEVEL 9 to that package/procedure only, but would do so every time the procedure is executed.

In order to apply this parameter only when the passed ORDER_ID is 1576, we first have to ensure that the procedure is correctly instrumented, and will encapsulate its parameters into the CALL_INFO argument which is passed to BMC_DEBUG.BEGINCALL. The start of the procedure should look like this :-

PROCEDURE process_order (order_id IN NUMBER) IS

BEGIN

bmc_debug.begincall($$PLSQL_UNIT,'PROCESS_ORDER','order_id='||order_id);

So as well as passing the package and procedure names to BEGINCALL, it passes a string which contains the names and values of the passed parameters (in this case the order_id) as the third parameter, CALL_INFO.

To make the BMC_DEBUG_PARAMETER entry only apply when the parameter value we want is passed, we use the CALL_INFO_LIKE column in BMC_DEBUG_PARAMETER to define the match condition. Our parameter row would therefore look like this :-

SQL> SELECT param_name,param_value,inherit,package_name,procedure_name,sid,serial#,username,call_info_like

FROM bmc_debug_parameter WHERE param_name = 'DEBUG LEVEL' AND procedure_name = 'PROCESS_ORDER';

PARAM_NAME PARAM_VALUE INHERIT PACKAGE_NAME PROCEDURE_NAME SID SERIAL# USERNAME CALL_INFO_LIKE

------------- ----------- ------- -------------------- --------------------- --- ------- -------- ---------------------------

DEBUG LEVEL 9 N ORDERING PROCESS_ORDER %order_id=1576%

So when the CALL_INFO_LIKE column in BMC_DEBUG_PARAMETER contains a value, it will be matched to the CALL_INFO value passed to BEGINCALL using the LIKE operator. If it matches, and the other columns in BMC_DEBUG_PARAMETER match, the DEBUG LEVEL will be applied.

Here is a runnable example, in which we make a procedure which accepts a parameter, and outputs a message at a high debug level :-

SQL> CREATE OR REPLACE PROCEDURE dyntest (order_id IN NUMBER) IS

BEGIN

bmc_debug.begincall('PROCEDURE','DYNTEST','order_id='||order_id);

bmc_debug.msg(9,'Is the message displayed and logged?');

bmc_debug.endcall('PROCEDURE','DYNTEST');

END dyntest;

/

Procedure created.

We add a BMC_DEBUG_PARAMETER row to set the DEBUG LEVEL to 3, without a CALL_INFO_LIKE value :-

SQL> INSERT INTO bmc_debug_parameter (param_name, param_value, inherit, package_name, procedure_name)

VALUES ('DEBUG LEVEL',3,'N','PROCEDURE','DYNTEST');

1 row created.

SQL> commit;

Commit complete.

If we then add a BMC_DEBUG_PARAMETER row to set the DEBUG LEVEL to 9 only when a certain value is passed as the order_id :-

SQL> INSERT INTO bmc_debug_parameter (param_name, param_value, inherit, package_name, procedure_name, call_info_like) VALUES ('DEBUG LEVEL',9,'N','PROCEDURE','DYNTEST','%order_id=1521%');

1 row created.

SQL> commit;

Commit complete.

If we execute it with an order_id which is not the one we are looking for :-

SQL> exec dyntest(1234);

PL/SQL procedure successfully completed.

Then if we execute it with the order_id we are looking for :-

SQL> exec dyntest(1521);

PROCEDURE.DYNTEST begins

Call Info passed : order_id=1521

Is the message displayed and logged?

PROCEDURE.DYNTEST ends

PL/SQL procedure successfully completed.

The high DEBUG LEVEL is applied and the test message is displayed, along with some other BMC_DEBUG internal messages.