Monday, February 13, 2012

Debugging EBS API calls in APEX

This picture comes from Dmitry Vostokov site.
While I was looking into E-Business Suite and especially the API calls, I wondered how I could debug what was going.

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:
at

2 comments:

  1. Chris2/23/2012 10:28 AM

    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

    ReplyDelete
  2. Anonymous12/11/2014 2:14 AM

    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

    ReplyDelete