Monday, December 5, 2011

apex_application.stop_apex_engine

Something that was upgraded in APEX 4.1 with little to no far fair was the addition of a new (supported) procedure called apex_application.stop_apex_engine. The apex_application.stop_apex_engine essentially stops the APEX engine and, as a result, will stop processing the rest of the page. It is most commonly used when forcing URL redirects from a page.

Before APEX 4.1 you could still use a similar feature however it was an unsupported variable in the apex_application package called g_unrecoverable_error.

I've included an example below of the before vs. after 4.1 ways to stop the APEX engine.
-- Pre-APEX 4.1 (not supported)
...
owa_util.redirect_url('http://www.clarifit.com');
apex_application.g_unrecoverable_error:= true; -- End APEX session

-- APEX 4.1 and above (supported)
...
owa_util.redirect_url('http://www.clarifit.com');
apex_application.stop_apex_engine; -- End APEX session

For those who have used the apex_application.g_unrecoverable_error variable in some of your applications you should go back and upgrade to the new procedure. For more information about the stop_apex_engine procedure please read the APEX documentation.

Thanks to Jason Long for posting the following comment about the differences between apex_application.g_unrecoverable_error and stop_apex_engine. Be sure to read it carefully because in the right conditions switching between the two calls can have significant impacts.

There appear to be a couple gotchas with simply replacing g_unrecoverable_error with stop_apex_engine.

1. If there is any PL/SQL code immediately following the g_unrecoverable_error it will still be executed. It looks like stop_apex_engine actually exits out of the routine.

2. It looks like stop_apex_engine will roll back any SQL inserts/updates/deletes that occur in the routine, where as g_unrecoverable_error allow them to be committed.

10 comments:

  1. When redirecting from a page isn't the page stopped from processing automatically?

    Raoul

    ReplyDelete
  2. "you should go back and upgrade to the new procedure." - is it not still provided for the likely many applications that have used this call?

    ReplyDelete
  3. When redirecting to a new page the rest of the page is still processed. I demoed this in the Ask the ClariFit Experts webinar (check http://presentations.clarifit.com in a few days for the video).

    Regarding the old method, I think it may work for a while. It was an undocumented feature so I'm not sure how long the APEX team will keep supporting it.

    Martin

    ReplyDelete
  4. An issue I have encountered:

    https://forums.oracle.com/forums/thread.jspa?threadID=2377957

    ReplyDelete
  5. There appear to be a couple gotchas with simply replacing g_unrecoverable_error with stop_apex_engine.

    1. If there is any PL/SQL code immediately following the g_unrecoverable_error it will still be executed. It looks like stop_apex_engine actually exits out of the routine.

    2. It looks like stop_apex_engine will roll back any SQL inserts/updates/deletes that occur in the routine, where as g_unrecoverable_error allow them to be committed.

    ReplyDelete
    Replies
    1. Thanks for your input. I've included your comments in the article itself since I want to ensure readers are aware of it.

      Delete
  6. I wonder if I call apex_application.stop_apex_engine to execute wpg_docload.download_file in order to download RTF file, would it affects other users to use that APEX application?

    Sam

    ReplyDelete
  7. Thank you very much, the article helped me a lot! But I still have some problems... The matter is I need to load several files from one process (checking several rows in a table, then loading each one into a separate file), the process is:

    BEGIN
    FOR I IN 1 .. APEX_APPLICATION.G_F01.COUNT
    LOOP
    cher_sp.load_ndfl6.download_file(TO_NUMBER(APEX_APPLICATION.G_F01(i)));
    END LOOP;
    END;

    and the code in cher_sp.load_ndfl6.download_file:
    ...
    htp.init;
    owa_util.mime_header('text/xml', false );
    htp.p( 'Content-Length: ' || dbms_lob.getlength( myfile ) );
    htp.p( 'Content-disposition: attachment; filename="'||c0.filename||'.xml";' );
    owa_util.http_header_close;
    wpg_docload.download_file(myfile);
    htmldb_application.g_unrecoverable_error := true;
    --apex_application.stop_apex_engine;
    ...

    With stop_apex_engine only the first file is loaded, with g_unrecoverable_error - only the last. Help me, please, what should I do with it?

    ReplyDelete
    Replies
    1. Stoping APEX aside, I don't think you can do what you want to do. When you download a file you can only download one file. What you probably need to do is zip your multiple files before calling your download_file function. APEX 5 has a zip function for that: https://docs.oracle.com/cd/E59726_01/doc.50/e39149/apex_zip.htm#BABHECBD

      Delete