How to test an On-Demand Process used for AJAX

4. December 2006

Oracle APEX, Tips & Tricks

You have created an On-Demand Process to do an AJAX call from JavaScript, like Carl Backstrom showed it in an example, but the On-Demand Process doesn’t return anything.

What’s wrong?

There can be multiple causes what can be wrong with an On-Demand Process. When you read the documentation you will notice that

Any failures of authentication, authorization, or process conditions do not result in visible error messages or other indicators of such failures and most often result in a blank page being displayed.

This also includes errors when the PL/SQL code of your On-Demand Process doesn’t compile or when you specify a wrong process name. This is because of security concerns as Scott Spadafore confirmed in a recent posting.

So what to do if APEX doesn’t tell us anything?

You first have to figure out where the problem of your On-Demand Process is.

  • Has a wrong process name be specified?
  • Does it raise an exception which causes a blank page?
  • Does the PL/SQL code not compile?

The best way to test it is to start the On-Demand Process directly through the proper URL. It’s much easier than calling it from some JavaScript code where you don’t see what is returned.

Use the following syntax to call it:

f?p=application_id:0:session:APPLICATION_PROCESS=process_name:::item-names:item-values

Where:

  • application_id is the application ID or alphanumeric alias
  • session is the session ID (run your application and get it from the URL)
  • APPLICATION_PROCESS=process_name is the keyword APPLICATION_PROCESS= followed by either the process ID or an alphanumeric name of an application-level process having a Process Point of On Demand
  • item-names are a list of application- or page item names which you want to set. They are seperated by a comma
  • item-values are a list of values corresponding to the item-names, also separated by a comma.

An example URL would be

http://apex.oracle.com/pls/otn/f?p=33231:0:1725326667635628:
APPLICATION_PROCESS=ApexLib_getLovResult:::APEXLIB_REFERENCE_TYPE,APEXLIB_REFERENCE_ID:ABC,123

The best way to get the correct process name is to copy-paste it from the process definition. It wouldn’t be the first time that there is a type. :-)

Didn’t work? The next step is to replace the existing PL/SQL code of the On-Demand Process with just the following code, to make sure that the call works at least.

HTP.p('Hallo world');

Didn’t work, too? Then I have no clue neither what to do next in such a case. Check again the URL and process name if they are ok.

If you got the “Hallo world” then it seems that your PL/SQL code is raising an exception or the PL/SQL code is syntactically wrong.

To handle the first possibility add an exception handler around your code, to show us the exception which is raised by Oracle.

BEGIN
  [...Here comes your existing code...]
EXCEPTION WHEN OTHERS THEN
  HTP.p('Error: '||SQLERRM);
END;

Does it show an error message? With this error message you should have a hint what’s going wrong in your code. If not, add some HTP.P calls for debugging purpose to your code.

If it still doesn’t show an error message, than it seems that your PL/SQL code is syntactically wrong. Have you already tried to run/compile the code stand alone in SQL*Plus/Toad/SQL Developer?

Happy testing!

11 Responses to “How to test an On-Demand Process used for AJAX”

  1. Anonymous Says:
    December 4th, 2006 at 23:34

    Just wanted to say you have a very nice blog with lots of useful info for Apex developers.

    Keep up the good work!

  2. Patrick Wolf Says:
    December 4th, 2006 at 23:46

    Thanks a lot! I really appreciate that! Feedback from the community cheers me on to continue my blog.

    Patrick

  3. Doug Gault Says:
    December 5th, 2006 at 13:13

    Patrick, This tip just saved my bacon! Sometimes its the little stuff you overlook that really saves the day! KEEP UP THE GOOD WORK

  4. Patrick Wolf Says:
    December 5th, 2006 at 13:35

    Hi Dough,

    you are welcome!

    Sometimes APEX makes life harder than it should be…

    Have you already checked out the debugger hack? :-)

  5. Sujay Says:
    January 31st, 2009 at 18:45

    Patrick,

    I am setting the variable using x01, how do I test that in the URL?

    Thanks
    Sujay

  6. Patrick Wolf Says:
    February 9th, 2009 at 20:06

    Sujay,

    the the parameters x01..x10 are not available in the “f” interface. This parameters are only available in the wwv_flow.show interface which is called with the htmldb_Get interface.

    Anyway I would use Firebug (Firefox Plugin) and there the Console to do the testing nowadays.

    Regards
    Patrick

  7. Vikas Handa Says:
    August 30th, 2009 at 04:51

    Dear Patrick,

    A big thank you for nice tip. It was very helpful and it saved me lot of time.

    Best regards,

    Vikas

  8. Patrick Wolf Says:
    September 3rd, 2009 at 10:06

    You are welcome!

  9. sihem Says:
    January 4th, 2010 at 05:49

    Hi you have a very good block.
    You know i have a very probleme
    i want to insert in my table by usin process on demand
    in first time it work but if i use insert field blob ,the process can’t insert this field why
    the source of process:
    declare
    dd number;
    dd1 number;

    begin
    IF (:P10_PHOTO IS NOT NULL) THEN
    insert into voiture
    (MAT,MARQUE,COULEUR,NB_CHEVEAUX,TYPE_VOIT,DESCRIPTION,N_VIGNETTE,
    N_ASSURANCE,EMPLACEMENT,PRIX_VOIT,COMPTEUR_VOIT,N_CARTE_GRISE,PHOTO)
    select
    :P 10_MAT,:P10_MARQUE,:P10_COULEUR,:P10_NB_CHEVEAUX,:P10_TYPE_VOIT,
    :P 10_DESCRIPTION,:N_VIGNETTE2,:N_ASSURANCE2,:P10_EMPLACEMENT,
    :P 10_PRIX_VOIT,:P10_COMPTEUR_VOIT,:N_CARTE_GRISE,BLOB_CONTENT
    from APEX_APPLICATION_FILES
    where name = :P 10_PHOTO;
    htp.p(dd1);
    htp.p(:P10_PHOTO);
    end if;
    select max(ind1) into dd1 from voiture;

    delete from APEX_APPLICATION_FILES where name = :P 10_PHOTO;

    insert into etat_voiture
    (nom,id_voit)
    values(:P10_etat, dd1);

    select max(id) into dd from etat_voiture;

    update voiture
    set etat=dd
    where ind1=dd1;

    end;

    can you help me please.
    regard sihem

  10. sihem Says:
    January 9th, 2010 at 08:28

    Hi you have a very good block.
    You know i have a very probleme
    i want to insert in my table by usin process on demand
    in first time it work but if i use insert field blob ,the process can’t insert this field why
    the source of process:
    declare
    dd number;
    dd1 number;

    begin
    IF (:P10_PHOTO IS NOT NULL) THEN
    insert into voiture
    (MAT,MARQUE,COULEUR,NB_CHEVEAUX,TYPE_VOIT,DESCRIPTION,N_VIGNETTE,
    N_ASSURANCE,EMPLACEMENT,PRIX_VOIT,COMPTEUR_VOIT,N_CARTE_GRISE,PHOTO)
    select
    10_MAT,:P10_MARQUE,:P10_COULEUR,:P10_NB_CHEVEAUX,:P10_TYPE_VOIT,
    10_DESCRIPTION,:N_VIGNETTE2,:N_ASSURANCE2,:P10_EMPLACEMENT,
    10_PRIX_VOIT,:P10_COMPTEUR_VOIT,:N_CARTE_GRISE,BLOB_CONTENT
    from APEX_APPLICATION_FILES
    where name = 10_PHOTO;
    htp.p(dd1);
    htp.p(:P10_PHOTO);
    end if;
    select max(ind1) into dd1 from voiture;

    delete from APEX_APPLICATION_FILES where name = 10_PHOTO;

    insert into etat_voiture
    (nom,id_voit)
    values(:P10_etat, dd1);

    select max(id) into dd from etat_voiture;

    update voiture
    set etat=dd
    where ind1=dd1;

    end;

    can you help me please.
    regard sihem

  11. Naren Says:
    February 10th, 2010 at 04:25

    Nice article. Faced an issue with the on-demand process, the article was very useful in debugging. Thanks.

Leave a Reply