Debug Messages (PL/SQL API)
Use the extensive debug messages to troubleshoot in case of unexpected problems. These debugging messages are useful because an API would be difficult to debug otherwise. User can turn on debug messages with a certain profile option. These messages can be written to a log file as well.
The profiles for controlling the debug mechanism are:
· Name - HZ_API_FILE_DEBUG_ON
· User Profile Name - HZ: Turn On File Debug
· Name - HZ_API_DEBUG_FILE_NAME
· User Profile Name - HZ: API Debug File Name
· Name - HZ_API_DEBUG_FILE_PATH
· User Profile Name - HZ: API Debug File Directory
· Name - HZ_API_DBMS_DEBUG_ON
· User Profile Name - HZ: Turn On Development Debug.
If the HZ_API_FILE_DEBUG_ON profile is set to Y when any APIs are called, then debug messages are written to the file specified in HZ_API_DEBUG_FILE_PATH and HZ_API_DEBUG_FILE_NAME. If the HZ_API_FILE_DEBUG_ON profile is set to N, no debug messages are generated.
The value of the HZ_API_DEBUG_FILE_PATH profile specifies a directory file path that the database has write access to, as provided in init.ora. User can find path information by querying: select value from v$parameter where name equals utl_file_dir. If user turn the file debug mode on, but did not set a proper value for the HZ_API_DEBUG_FILE_PATH profile or the HZ_API_DEBUG_FILE_NAME profile is null, the API errors out.
Debug messages accumulate in the debug file. After collecting any debug messages, user must reset the HZ_API_FILE_DEBUG_ON profile back to N. If user do not, system might cause an exceeded file size error.
User should only use the HZ_API_DBMS_DEBUG_ON profile during the development phase or in SQLPLUS because of limitation of dbms_output.
There are two ways to run APIs in the debug mode:
From Oracle Applications the debug mode can be enabled or disabled by setting HZ_API_FILE_DEBUG_ON to Y or N, respectively. The default value is N. When the profile is set Y, user must set the proper values for the HZ_API_DEBUG_FILE_NAME and HZ_API_DEBUG_FILE_PATH profiles.
With SQLPLUS or server side PL/SQL custom code - user can enable or disable the debug mode by calling FND_PROFILE API.
This example assumes that the directory, /sqlcom/out/tca115, has write access that is specified by the utl_file_dir parameter in the init.ora for the relevant database.
From SQLPLUS
exec fnd_profile.put('HZ_API_DEBUG_FILE_PATH', '/sqlcom/out/tca115/');
exec fnd_profile.put('HZ_API_DEBUG_FILE_NAME', 'api_debug');
exec fnd_profile.put('HZ_API_FILE_DEBUG_ON', 'Y');
From PL/SQL code
fnd_profile.put('HZ_API_DEBUG_FILE_PATH', '/sqlcom/out/tca115/');
fnd_profile.put('HZ_API_DEBUG_FILE_NAME', 'api_debug');
fnd_profile.put('HZ_API_FILE_DEBUG_ON', 'Y');
Follow the same strategy for dbms debug mode, but be sure to use the set serveroutput on option.
From SQLPLUS
set serveroutput on exec fnd_profile.put('HZ_API_DBMS_DEBUG_ON', 'Y'); From PL/SQL
set serveroutput on
fnd_profile.put('HZ_API_DBMS_DEBUG_ON', 'Y');
This debug strategy is provided as a public utility procedure that can include in custom code. Refer to the HZ_UTILITY_PUB package for further details.
