PL/SQL logging

Once I was in the EBS financial team, created several PL/SQL packages. At that time, we use the FND_LOG.STRING() and FND_FILE.PUT_LINE() functions to logging the temporary results of the procedure/function to debug programs. At that time, I thought those two procedures are Oracle DB's facilities, but it turns out only EBS financial database has such procedures.

In common PL/SQL programs, we have to use the DBMS_OUTPUT.PUT_LINE() function to record the parameters and temporary results of the program.

Further details will be explored in the future.

Log4j also has a package for PL/SQL code.

For every major function/procedure in a package, we have to logging on its entry and exit of the method as well as exceptions and errors.

The following code is an example:

IF (PLOG.isDebugEnabled) THEN
 PLOG.debug(GV_MODULE_PREFIX || '.' || var_l_function_name || ' begins with the following parameters: ' || ...);
END IF;

IF(PLOG.isDebugEnabled) THEN
  PLOG.debug(GV_MODULE_PREFIX || '.' || var_l_function_name || ' ends with return code: '|| var_l_ret_code);
END IF;

Just another prove to say that Log4j is great.

DMBS_OUTPUT.put_line function can print VARCHAR2, DATE, and number but not BOOLEAN directly. So we have to use the following way to print the BOOELAN value PLOG.isDebugEnabled.

BEGIN
  IF(plog.isDebugEnabled) THEN
dbms_output.put_line('hello, DEBUG IS ENABLED');
ELSE
 dbms_output.PUT_LINE('hELLO, debug is not enabled.');
  END IF;
END;


The logging messages are stored in TLOG table.

Also, it's very important to log error message:
IF(plog.isErrorEnabled) THEN
  PLOG.error('Here is also an error message.');
END IF;

The error message is also in the table TLOG. Seems that they LLEVEL are all equals to 30.