 |
| This picture comes from Dmitry Vostokov site. |
I find it important to understand what I'm doing. EBS is big, but the more I get into it, the more I want to know what is going on (because the more I get into having issues ;-)).
Although I don't have the time right now to really dive into a detailed EBS-APEX debugging post, I quickly want to share my ideas.
In EBS people tend to use the hr_utility package to trace what is going on. You can do something like :
apps.hr_utility.set_trace_options ('TRACE_DEST:DBMS_OUTPUT');
Unfortunately if you don't have SQL Plus or SQL Developer access to the EBS database and you only have APEX, what do you do? It would be nice if the TRACE_DEST parameter would allow HTP as a value, but it doesn't.
So here is some pseudo-code I thought would help me to debug from within my APEX app (or SQL Workshop):
BEGIN
if APEX_APPLICATION.G_DEBUG
then
-- trace_dest possible values: DBMS_PIPE, DBMS_OUTPUT, PAY_LOG
apps.hr_utility.set_trace_options ('TRACE_DEST:DBMS_PIPE');
-- set trace on
apps.hr_utility.trace_on;
-- do EBS API call
/* EBS api call */
-- custom message in EBS trace output
apps.hr_utility.trace('Custom message in trace');
-- custom message in APEX debug
apex_debug_message.log_message (
p_message => 'My message',
p_enabled => TRUE,
p_level => 5
);
-- like to work with Tyler Muth's logger package
-- logger.log('if the logger package is installed, another way to log a message');
-- run some other code
/* my custom code */
-- set trace off
apps.hr_utility.trace_off;
-- log the EBS trace data to APEX or custom table
/* insert the data of the EBS HR Utility PIPE into APEX debug messages */
/* query debug output in a report or in APEX debug view */
end if;
EXCEPTION
WHEN OTHERS
THEN
-- store your error in some logging/debugging of the above
-- dbms_output.put_line(sqlerrm);
apps.hr_utility.trace_off;
END;
I hope by reading the code you understand what I think would be a good way to debug your EBS code in APEX. I didn't find the time yet to write an entire EBS-APEX debug package, but maybe some of you already did or if you want to complete the above code, feel free to share it in the comments.
Happy debugging :-)
Previous related posts:
- Updating EBS data from APEX using Triggers and APIs (3)
- Creating EBS data from APEX using APIs (2)
- Updating EBS data from APEX using APIs (1)
- Querying EBS data from APEX using Views
- E-Business Suite and APEX integration (overview)
- E-Business Suite and APEX installation
- APEX and E-Business Suite integration (OBUG event)
2 comments:
Hi Dimitri,
in the past, I've often found it useful to display dbms_output of server-side code into my Forms and Apex apps. Since it seems to be possible to configure EBS to write trace to DBMS_OUTPUT, you could use code like this:
(1) set up dbms_output
dbms_output.enable;
apps.hr_utility.set_trace_options ('TRACE_DEST:DBMS_OUTPUT');
(2) run ebs code
(3)
declare
l_lines sys.dbms_output.chararr;
l_count number;
begin
if apex_application.g_debug then
loop
l_count := 10000;
sys.dbms_output.get_lines (
lines => l_lines,
numlines => l_count );
exit when l_count = 0;
for i in 1 .. l_count loop
apex_application.debug(l_lines(i));
end loop;
end loop;
end if;
end;
In fact, we should probably add an API call for (3) to Apex itself.
Regards,
Christian
PS: recaptcha drives me crazy
Hi Dimitri,
I think you might want to use standard Oracle R12 API FND_LOG.STRING. Using this API allows you to record in table FND_LOG_MESSAGES a log of your PL/SQL code execution, and also the standard Oracle APIs and PL/SQL code. Similar to how Logger performs, this API reads the values defined in standard profile options to determine to log or not. You can set the following profiles at the user level for example:
1) FND: Debug Log Enabled = Yes
2) FND: Debug Log Level = Statement
3) FND: Debug Log Module = %
In your PL/SQL, start by defining constants for the API:
G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
G_MODULE_NAME CONSTANT VARCHAR2(50) := 'HR.PLSQL.HR_EXAMPLE_PKG.';
Optionally define a variable for the message to log:
lv_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
Then when you need to insert a log statement, enter it as follow:
if( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
lv_log_msg := 'your_debug_message';
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, G_MODULE_NAME, lv_log_msg);
end if;
The FND_LOG.G_CURRENT_RUNTIME_LEVEL global variable allows callers to avoid a function call if a log message is not for the current level. It is automatically populated by the FND_LOG_REPOSITORY package.
You can then have different logs fired or not depending on the profile option value set by a user, developer or sysadmin. So the following log entry will fire only if profile option value "FND: Debug Log Level" is set to statement:
if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
lv_log_msg := 'your_statement_debug_message';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, G_MODULE_NAME, lv_log_msg);
end if;
When your PL/SQL completes, you can query table FND_LOG_MESSAGES, or display it in APEX in an interactive report.
Hope this helps you finding a way to trace your PL/SQL code in EBS.
Sylvain Martel
Post a Comment