How to test an On-Demand Process used for AJAX

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 thoughts on “How to test an On-Demand Process used for AJAX

  1. 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, This tip just saved my bacon! Sometimes its the little stuff you overlook that really saves the day! KEEP UP THE GOOD WORK

  3. 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

  4. 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
    :P10_MAT,:P10_MARQUE,:P10_COULEUR,:P10_NB_CHEVEAUX,:P10_TYPE_VOIT,
    :P10_DESCRIPTION,:N_VIGNETTE2,:N_ASSURANCE2,:P10_EMPLACEMENT,
    :P10_PRIX_VOIT,:P10_COMPTEUR_VOIT,:N_CARTE_GRISE,BLOB_CONTENT
    from APEX_APPLICATION_FILES
    where name = :P10_PHOTO;
    htp.p(dd1);
    htp.p(:P10_PHOTO);
    end if;
    select max(ind1) into dd1 from voiture;

    delete from APEX_APPLICATION_FILES where name = :P10_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

  5. 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

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

Comments are closed.