Oracle APEX AJAX calls fails

1
votes

When executing an Oracle Apex onDemand procedure, I get an error: ORA-01403: no data found. I've researched this for 4 days and still can't get it to work.

  1. I created a select list P1_PLANTS and a text field P1_LIGHT_REQ

  2. In the custom attributes field of P1_PLANTS I entered

    onchange="getLightRequirements(this, 'P1_LIGHT_REQ')";

  3. In the HTML Page Header I entered the following JavaScript which will call an onDemand process.

    function getLightRequirements( pThis , pLightReq){

    alert('In getLightRequirements\nThis.value is '+pThis.value+'\npLightReq is '+pLightReq);

    var ajaxRequest = new htmldb_Get(null,null, 'APPLICATION_PROCESS=getLightRequirements');

     ajaxRequest.add( 'P1_PLANTS' , pThis.value);

     var ajaxResult = ajaxRequest.get(); 

     alert('ajaxResult is '+ajaxResult);
}

  4. Below is the onDemand process

    declare
  light_req varchar2(20);
  P1_PLANTS number;
begin
  select LIGHT_REQUIREMENTS into light_req from PLANTS
  where PLANT_ID = P1_PLANTS;

  htp.prn( light_req );
end;

When I choose a plant, I get my alert message showing that all parameter values are as expected and the error shown below.

<html><body><h1>Error occurred while painting error page: ORA-01403: no data found</h1></body></html>

Additionally, it doesn’t populate the ‘Light Requirements’ text field with the error message. (Although the text field DID get populated in the past.) The Session State Protection for P1_LIGHT_REQ is ‘unrestricted’.

If I execute the onDemand process in the Apex SQL Workshop, hard coding a value for P1_PLANTS, the query runs fine.

If I minimize the onDemand process to the code shown below, I still get the no data found error.

begin
   htp.prn( light_req );
end;
2
oracleoracle-apex

2 Answers

1
votes

You probably have collated the code you used from some online sources. It's old.

Since apex 4.2 we've had the apex.server.process function (apex 5.0 doc link) which will execute AJAX calls. htmldb_Get has been undocumented since ever and even if it would be, it'd be deprecated by now. And unless you use the async call on it, it's snchronuous. That's not what the first "A" in "AJAX" stands for. There is a reason browsers are alerting you in the console about how a synchronuous request is deprecated, just like there is a reason why apex 5.1 will no longer have some built-in sync calls such as in dynamic actions. Prepare yourself for the future (which is now) and perform your calls as they should be. Don't teach yourself old junk.

apex.server.process("getLightRequirements"
                   ,{pageItems:"#P1_PLANTS"}
                   ,{dataType:"text"})
  .done(function(pReturn){ 
    alert("result is: " + pReturn); 
  })

Take note, apex.server.process by default expects JSON back. Either comply (shouldn't be hard) or tell it to expect text back.

Now, don't put javascript code in the "HTML page header" in script tags. Again, since 4.2 you can put javascript code in therefor-designed boxes on the page level.

Similarly, stop using these "onxxx" attributes. What point is there? Dynamic actions can easily help here and will visualize where you have JS going on. These "onxxx" attributes start ending up all over the place: in SQL, in attributes, in code,... It's a pest and it's detrimental to maintenance.

Create a dynamic action which acts on change of the required item. You can then execute javascript code, such as the code above, or call a function you've specified, or even execute PLSQL code from there. It's possible that in your case here it'd be possible to put everything in a dynamic action - unless you're doing this as a simple introductory exercise for example.

If you're still struggling, you may want to consider going to apex.oracle.com . Create a new workspace, then add a new account for which you can share the credentials. Create your application and page(s) as an example, and get back to us (or anyone) and share the workspace name + developer credentials. People can then go to your application and look around, copy things, change things,...

0
votes

On your on demand process you don't need to declare P1_PLANTS, you need to use it as a bind variable (that is why you add it to your ajax request) like this:

declare
    light_req varchar2(20);

begin
    select LIGHT_REQUIREMENTS into light_req 
    from PLANTS
    where PLANT_ID = :P1_PLANTS; /*Notice the colon in front of P1_PLANTS this indicates a bind variable*/

    htp.prn( light_req );

end;

And thats it.

Edit:

I didn't notice it before but another thing you should do is use the $v() function to get the value of an item on javascript like this:

ajaxRequest.add( 'P1_PLANTS' , $v('P1_PLANTS'));

When you declare:

onchange="getLightRequirements(this, 'P1_LIGHT_REQ')";

the instance "this" is referencing the event object, not the item itself.