This discussion is archived
12 Replies Latest reply: Apr 27, 2013 5:02 AM by 1005877 RSS

Unexpected behaviour on apex.oracle.com

Andreas Weiden Guru
Currently Being Moderated
Hello to everybody,

i'm facing a strange issue with an application on apex.oracle.com. I uploaded an application consisting of about 10 pages which is more or less a helper application for a pl/sql-program which transforms an xml-report-definition into a pdf. The program runs perfectly on my local APEX 4.1.0-installation on XE 11G.
When running on apex.oracle.com the pl/sql-program simply "aborts" somewhere in the middle without any Oracle-error. Its started as a Before header-process on a page and simply returns a "Sorry! Page not found". When i run the code from SQL Workshop i get the same result, no Oracle-error, but "Sorry! Page not found". Whats even stranger: the package logs to a table using an autonomous transaction, running the same code with the same parameters several times causes a different number of logs written.

My only idea is that there is some kind of resource-restriction which simply "halts" the execution of the PL/SQl-program. Is that the case? And if so, is it correct that is shows up with this behaviour?

Here the link to the application http://apex.oracle.com/pls/apex/f?p=PLJRXML2PDF (user/pwd demo/demo)

I would appreciate any help.

Regards,
Andreas

Edited by: Andreas Weiden on 11.07.2012 21:12
  • 1. Re: Unexpected behaviour on apex.oracle.com
    VC Guru
    Currently Being Moderated
    Yes you are right, have you tried debug option?

    But f I try to access the page 7 directly by altering the URL it shows no data found error message with ok button....does it ring any bells?
  • 2. Re: Unexpected behaviour on apex.oracle.com
    Andreas Weiden Guru
    Currently Being Moderated
    The NO_DATA_FOUND is raised in the PL/SQL if the name of the report isn't set in the report-launcher-page 6, so thats expected behaviour.
  • 3. Re: Unexpected behaviour on apex.oracle.com
    VC Guru
    Currently Being Moderated
    Check the error log if it gives any clue
    select * from apex_activity_log
      where sqlerrm is not null 
      and time_stamp >= sysdate - 3
    order by time_stamp desc
  • 4. Re: Unexpected behaviour on apex.oracle.com
    Andreas Weiden Guru
    Currently Being Moderated
    No, just the NO_DATA_FOUND's from yesterday.
  • 5. Re: Unexpected behaviour on apex.oracle.com
    DietmarAust Oracle ACE
    Currently Being Moderated
    Hi Andreas,

    we need more details, possibly access to the workspace to troubleshoot that.

    *) Are you sure, the installation of the packages was successful? Perhaps something went wrong there.
    *) Are you doing a redirection of sorts (owa_util.redirect_url or something)? What page are you trying to load?

    Without having access to the application it is really hard to troubleshoot. I don't think it is a resource restriction. The APEX team uses dbms_resource_manager rules, but the rendering and display of the error message returns too fast for that.

    Or are you using recursion and you quickly hit a too deep level?

    Cheers,
    Dietmar.
  • 6. Re: Unexpected behaviour on apex.oracle.com
    Andreas Weiden Guru
    Currently Being Moderated
    Hi Dietmar,

    i've shrinked the code to just contain the problem and the result is quite strange. The remaining code does nothing more but taking an xmltype and splitting in down into a record-structure by using some cursors. I even hardcoded the used XML in the package instead of reading it from a table.

    There's one piece where i parse the different regions of the JRXML with the following cursor definition used
        vcText VARCHAR2(2000):='/' || i_vcBasenode || '/band';
        
        CURSOR crBand IS
          SELECT BAND
            FROM XMLTABLE(vcText PASSING i_oRegion
                                 COLUMNS BAND XMLTYPE PATH '.'
                         );
    The i_vcBaseNode is passed from outside the procedure and can be one of the valid jrxml-regions, e.g. 'detail'

    When i run the following code from SQL Workshop, it sometimes succeeds with a "Statement processed" and sometimes fails with a "Page not found". (The "sometimes" thing is what makes me nervous, and that there is no oracle-error but simply a Page not found which may be something like "No more data to read from socket" in local development tools). It would be interesting to know if there is some error in the alert.log.
    DECLARE
      lParams PK_JRXML2PDF_REPGEN.tParamList;
      bl BLOB;
    BEGIN
      bl :=PK_JRXML2PDF_REPGEN.FK_RUN(i_vcName =>'mini',
                                      i_lParams=>lParams);
    END;
    When i rewrite the code to use a static string in the XMLTABLE like
        CURSOR crBand IS
          SELECT BAND
            FROM XMLTABLE('/detail/band' PASSING i_oRegion
                                 COLUMNS BAND XMLTYPE PATH '.'
                         );
    the error vanishes. But that means a partial rewrite of the package because i have to multiply my cursors.

    Even if its unlikely, either i used an undocumented feature which doesn't work correctly, or this maybe is a bug.

    If you like to have a look at it yourself, i opened the workspace

    Workspace: WEIDEN
    User: worker
    Password: worker


    I'll see if i can bypass this error by some recoding.

    Edited by: Andreas Weiden on 12.07.2012 22:10
  • 7. Re: Unexpected behaviour on apex.oracle.com
    mobra Journeyer
    Currently Being Moderated
    My only idea is that there is some kind of resource-restriction which simply "halts" the execution of the PL/SQl-program. Is that the case?
    Actually, there is a Resource Manager plan in effect on apex.oracle.com, as described here:

    http://joelkallman.blogspot.com/2009/08/oracle-database-resource-manager-and.html

    But the blog post says sessions are killed only after 1800 seconds, which means your process has to be running for a looong time before being affected by this....

    - Morten

    http://ora-00001.blogspot.com
  • 8. Re: Unexpected behaviour on apex.oracle.com
    Andreas Weiden Guru
    Currently Being Moderated
    Ok, the issue is fixed.

    I rewrote all statements with an XMLTABLE in it so that they use fixed string literals instead of the variables.

    Now the application works as it should. It generates the PDF's and shows the result in the popup-page 7.

    Thank you for your asistance.

    Andreas
  • 9. Re: Unexpected behaviour on apex.oracle.com
    DietmarAust Oracle ACE
    Currently Being Moderated
    Hi Andreas,

    good that you have found a workaround. I had already contacted the APEX team directly so that they would have a look at the alert.log and such.

    Ok, then there is no need for that anymore.

    It seems like a bug, though.

    Cheers,
    Dietmar.
  • 10. Re: Unexpected behaviour on apex.oracle.com
    Andreas Weiden Guru
    Currently Being Moderated
    Ok, then there is no need for that anymore.
    Might be a good idea anyway to have a closer look at that. I can't open an SR as i only have an XE-edition.
  • 11. Re: Unexpected behaviour on apex.oracle.com
    DietmarAust Oracle ACE
    Currently Being Moderated
    Hi Andreas,

    I am sorry but I had already stopped that in the meantime. This really smells like a db bug. And you have a working solution now for all releases anyway.

    Cheers,
    Dietmar.
  • 12. Re: Unexpected behaviour on apex.oracle.com
    1005877 Newbie
    Currently Being Moderated
    hi everbody,
    i am working on an anti-plagiarism application. could you help me out with the code for anti-plagiarism.
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points