Carl Backstrom has provided some truly amazing HTML DB AJAX & DHTML examples on his demo page on htmldb.oracle.com. Most of them are documented, but they assume that you have more of a technical background than not.
With Carl’s blessings, I took one of his examples – AJAX Process on Demand – and created a working example of an AJAX-based Select List. The example is quite simple: select an Employee Name, and the Job field will automatically be populated with the corresponding Job from the EMP table. This is a common behavior in Oracle Forms (when a user select one thing, another field is auto-populated) and will make the transition for those coming from a Forms environment a bit easier.
To take it one step further, I created an AJAX code generator of sorts. Give it the required six fields, and it will automatically generate the PL/SQL Application Process, the JavaScript Event and the JavaScript function. Now you can harness the power of AJAX (or at least this one simple example) without knowing J or X!
With Carl’s blessings, I took one of his examples – AJAX Process on Demand – and created a working example of an AJAX-based Select List. The example is quite simple: select an Employee Name, and the Job field will automatically be populated with the corresponding Job from the EMP table. This is a common behavior in Oracle Forms (when a user select one thing, another field is auto-populated) and will make the transition for those coming from a Forms environment a bit easier.
To take it one step further, I created an AJAX code generator of sorts. Give it the required six fields, and it will automatically generate the PL/SQL Application Process, the JavaScript Event and the JavaScript function. Now you can harness the power of AJAX (or at least this one simple example) without knowing J or X!
Comments
I remember the workaround that I used in my first HTMLDB application with a select list with submit, I needed a dummy flag item and had to change all my validations to check the dummy flag item before validations and had to set the dummy flag when i wanted to save.
AJAX stands for Asynchronous JavaScript & XML. It's nothing new technology-wise, but the term itself is relatively new.
Think of AJAX as a set of technologies that allows your HTML applications (HTML DB and otherwise) to behave more like traditional Client/Server applications. Things like Drag & Drop (as implemented in either Google Maps or the HTML DB 2.0 Query Builder) and queries of the database without refreshing the entire page are possible using AJAX.
Like all new technologies, AJAX is still trying to "find its groove" in the everyday application. Places that I see it making a lot of sense is anytime you want to change just a portion of your page. Rather then refreshing the entire page, there are several ways that you can refresh just an item or two using AJAX.
In my example, I do just that. When you select an Employee Name, there is a simple query that executes in the background and retrives that Employee's respective Job from the database and places it in the appropriate field. If you view the HTML source, you will not find any of the Job titles, as I only load each one as its requested.
This is helpful for a couple of reasons: 1) Forms users are used to this type of behavior. Thus, using AJAX makes Forms users more comfortable transitioning to the web and HTML DB 2) It's more efficient. Imagine that there were thousands of users using this application; one simple query vs. a complete page reload will reduce the load on the server.
I hope this helps clear things up some.
Thanks,
- Scott -
I hope we can see more of it!
Thank you for the reply!
I just wanted to say that the work you and Carl (and the rest of the HTMLDB team) do to help the developer community to get more and more out of HTMLDB is fantastic.. This example is great and just what I needed to start to get to grips with AJAX.
I've been using HTMLDB for about a year and a half and am impressed with the rate at which its developing and yet maturing.
Cheers,
Paul.
Thanks, Paul, but I am no longer with the HTML DB Development team or even with Oracle. However, I am still working with HTML DB on a daily basis with my own company now.
Thanks,
- Scott -
Sure, you can use AJAX almost anywhere, really. The code, of course, would be slightly different.
- Scott -
This is awesome! Unfortunately we are still in 1.6...upgrading to 2.0 someday but of course I need this functionality now. Is it possible in 1.6 to populate a text field or requery a Select List based on the LOV w/o submitting?
Thanks,
Janel
It is, but I do not have the code off the top of my head. You can try to search the ApEx forum on OTN...
Thanks,
- Scott -
as in the past your solutions are right on the mark. But, since I am a newbie i am not sure of what to put into the code generator for my application. If i ref an item on my page do i use the :Pxx_ITEM type of notation. I am actually looking for a solution just like Carls example but using your generator to achieve it. If you could post what you would use in the generator for your working example it might help. Thanks
Simply click on the label for each item to get some help. Each item has help which spells out what to put in the field for the EMP example.
Thanks,
- Scott -
Does ajax work with select, but in such a way, that it updates the content of the select list?
Imagine that i enter new name -> john, and press ok button. does the name 'john' appear inside the select, so that i can pick him?
Keep up the good work. Your code really provided great introduction to ajax and apex. Thanks a lot.
Steven Ho
(Java developer Intern - Chicago)
I've just follwed your ajax mempry tree tutorial, everything works fine apart from when i search for a node, the corresponding report displayed, but the tree doesn't expand accordingly.
Can you help?
Candy
Can you put your application on htmldb.oracle.com, so that I can take a look?
Thanks,
- Scot t-
I'm missing something - do you provide the code for this example? I see how it is being used but it doesn't help me learn how to do it.
John
John,
Not sure what exactly you're after. The sample application which I set up generates the Ajax code - you can then copy & paste it into your own application.
Thanks,
- Scott -
Sorry - I figured out to use the code generator. Oddly, the problem I am having now is, I build the page and followed your instuctions, but when I select a name, I get that same name in the JOB field, instead of the job. I went back and made sure I used the code in all the right places. I'm stumped.
John
Jon,
If you re-run the generator and click on the labels of each item and use the default values, then follow the steps, it should work.
If that still doesn't work, then please post your application to apex.oracle.com so that I can take a look.
Thanks,
- Scott -
John
Try using "job" as the Lookup Column name. It appears that there's a typo in the help.
Thanks,
- Scott -
Using "job" in the Lookup Colmn Name actually made things worse. With that change I get the job title in the select list where names should be.
John
PS - Did you go to Syracuse U.? I grew up in Cortland, just down the interstate.
At this point, can you put your application on apex.oracle.com and send the credentials to scott@sumnertech.com so that I can take a look at it? There's not much else I can do...
PS - Did you go to Syracuse U.? I grew up in Cortland, just down the interstate.
Yes, I did - 1992-1996. Is there snow there yet? If anyplace will escape global warming, it will be Upstate NY! :)
I found the problem. In the getJob process this is the code that is to be placed in the process (step 2):
declare
l_ename varchar2(100);
begin
select ename into l_ename from emp
where ename = :P3_ENAME;
htp.prn(l_ename);
end;
When I substitute 'job' for 'ename' in the line:
select ename into l_ename from emp, it works correctly.
Has anyone else pointed this out? Seems odd to find something like this after this post has been out on the web as long as it has been out here.
John
In any case, I've updated the example application so that it is correct.
Thanks for pointing it out.
- Scott -
Thanks for the excellent code generator. It works fine to populate one item. Can pls help me in populating more than one item in the form. In this case if I want to populate MGR, SAL as well as HIREDATE along with JOB, what changes shall I do? Any help would be highly appreciated. Thanks in advance.
-- Jasem