Wednesday, 21 June 2017

Debugging causes Oracle Apex page with plugin to crash


Today I was presented with mysterious behavior from an Apex page. I had developed a dashboard in one application and transferred it to another application connected to the same Oracle schema.
When I ran the page it gave the error:
Error in PLSQL code raised during plug-in processing.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

The error back trace did not give much info:

ORA-06512: at "APEX_050100.WWV_FLOW_DEBUG", line 561
ORA-06512: at "APEX_050100.WWV_FLOW_DEBUG", line 985
ORA-06512: at "APEX_050100.WWV_FLOW_DEBUG", line 355
ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 165
ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1855
ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 1894
ORA-06512: at "APEX_050100.WWV_FLOW_DYNAMIC_EXEC", line 935
ORA-06512: at "APEX_050100.WWV_FLOW_PLUGIN", line 1296
What was the matter?
I added an exception handler to the render function of the plugin, but no errors were reported from there.
I added a debug statement at the beginning of the render function and there was no output from it in the debug log.
I studied the case with my colleague Alex Nuijten and we could not find a solution. Until he had the bright idea to run the page not in debug mode. And the plugin rendered without errors! Thanks, Alex.

Now everything fell into place. The debugging outputs the PL/SQL Code section of the plugin.
And this code was not fully shown until the last end statement. So the error was generated because the amount of PL/SQL code was too much to output when debugging :-( . The code was 21K long in total and up to about 16K was shown in the output.

This provides yet another reason to move the PL/SQL code of a plugin to a database package.

Happy Apexing

at

2 comments:

  1. Chris22 June 2017 at 12:11

    Hi Dick,

    I think you had a unicode character in that debug output. APEX_DEBUG truncates parameters to 4000 characters, but it should really truncate to 4000 bytes:

    APEX_050200@apx52dev> exec apex_debug.error(rpad('o',4000))

    PL/SQL procedure successfully completed.

    APEX_050200@apx52dev> exec apex_debug.error(rpad('ö',4000))
    BEGIN apex_debug.error(rpad('ö',4000)); END;

    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at "APEX_050200.WWV_FLOW_DEBUG", line 563
    ORA-06512: at "APEX_050200.WWV_FLOW_DEBUG", line 1009
    ORA-06512: at "APEX_050200.WWV_FLOW_DEBUG_API", line 181
    ORA-06512: at line 1

    I filed bug #26328880 for this issue.

    ReplyDelete
  2. Dick Dral5 July 2017 at 13:24

    Hi Chris,

    I get your point, but unfortunately I did not find any Unicode sign in the code, so this is not the cause :-(.

    Thanks,
    Dick

    ReplyDelete