DEBUG VALUE pragma

The DEBUG VALUE pragma provides additional information for the following SQL statements.

• SET in routines (procedures and User Defined Functions (UDFs))

• RETURN in User Defined Functions (UDFs)

This information can be read using the SYS_ROUTINE_ERRORS view of the SYS_INFO_SCHEMA, see "SYS_ROUTINE_ERRORS".

The DEBUG VALUE pragma is currently only effective before these SQL statements.

---

DEBUG VALUE [LEVEL unsigned_integer ]

---

unsigned_integer

When unsigned_integer > 0, additional information is collected for the aforementioned statements when the DEBUG ROUTINE pragma is positioned ahead of the SQL statement CALL or ahead of a DML statement (for routines contained in this). In addition, unsigned_integer for DEBUG ROUTINE must be greater than or equal to unsigned_integer for DEBUG VALUE.

The following information is then collected:

• • • In the case of SET, the assigned value and the name of the target field (parameter or local variable)

    • In the case of RETURN, the value returned

In the case of strings, long values are, if required, truncated.

unsigned_integer = 1 is the default value when the LEVEL clause is not specified.

When unsigned_integer = 0, the pragma has no effect.

The following approach makes sense:

The pragma is initially active in an application with a value > 0 in, and then later (without changing the text length) disabled by the value 0.

The DEBUG VALUE pragma can also remain in the text of a routine after the end of a test or debugging phase provided the calling SQL statements do not use the corresponding DEBUG ROUTINE pragma.

Example

The SET statements of a procedure can be prefixed with the DEBUG VALUE pragma with various values for unsigned_integer . Calling the routine with the DEBUG ROUTINE pragma and different values for unsigned_integer causes information to be collected in various scopes.

CREATE PROCEDURE P (OUT par1 INTEGER,OUT par2 INTEGER)
       MODIFIES SQL DATA
       BEGIN
       --%PRAGMA DEBUG VALUE LEVEL 3
          SET par1 = 42;
       --%PRAGMA DEBUG VALUE LEVEL 10
          SET par2 = 43;
       END

With the procedure call below, only the first assignment (par1=42) is recorded:

-- %PRAGMA DEBUG ROUTINE LEVEL 5

CALL P(mypar1, mypar2)

Both assignments are recorded in the case of the procedure call below:

-- %PRAGMA DEBUG ROUTINE LEVEL 20

CALL P(mypar1, mypar2)

The DEBUG VALUE pragmas can remain unchanged in the text of the routine. They only have an effect when there is a corresponding unsigned_integer in the DEBUG ROUTINE pragma.