Skip to main content

AJAX Select List Code Generator

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!

Comments

Anonymous said…
Sorry to ask a newbie question, but in the context of HTML DB what does AJAX acheive? What does it improve upon? Why would you use it? What does the example demonstrate?
Anonymous said…
This is cool. Thanks for the example. Makes life so much simpler.., was not comfortable with the J and X part :-) These kind of examples helps people like me to get a good idea and get into the grove..;

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.
Scott said…
Newbie,

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 -
Anonymous said…
Ah yes, it did.

I hope we can see more of it!

Thank you for the reply!
Anonymous said…
Hey Scott,

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.
Scott said…
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.

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 -
Scott said…
AJAX is working with Select List. Will it work with popup list of values?

Sure, you can use AJAX almost anywhere, really. The code, of course, would be slightly different.

- Scott -
Anonymous said…
Hi 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
Scott said…
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?

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 -
Anonymous said…
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
Scott said…
...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.

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 -
Anonymous said…
Hi, the post is old, but i hope, that someone will read it :)
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?
Anonymous said…
Scott,
Keep up the good work. Your code really provided great introduction to ajax and apex. Thanks a lot.

Steven Ho
(Java developer Intern - Chicago)
Anonymous said…
Hi Scoot,
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
Scott said…
Candy,

Can you put your application on htmldb.oracle.com, so that I can take a look?

Thanks,

- Scot t-
Anonymous said…
Scott,

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
Scott said…
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,

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 -
Anonymous said…
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
Scott said…
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.

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 -
Anonymous said…
Hmmm... I've checked and everything looks good. The only difference is my items start with P3.., but I took that into consideration in the code generator. I am still getting the NAME in the JOB field when I run the application and select a name from the Select List.

John
Scott said…
Hmmm... I've checked and everything looks good. The only difference is my items start with P3.., but I took that into consideration in the code generator. I am still getting the NAME in the JOB field when I run the application and select a name from the Select List.

Try using "job" as the Lookup Column name. It appears that there's a typo in the help.

Thanks,

- Scott -
Anonymous said…
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.
Scott said…
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.

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! :)
Anonymous said…
I am living in NH now but I wouldn't doubt your theory on 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
Scott said…
I agree - figure someone would have noticed by now...

In any case, I've updated the example application so that it is correct.

Thanks for pointing it out.

- Scott -
Anonymous said…
Hello 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.
Anonymous said…
Thank you for your work, I don't know why they don't have an easy way to do this in Apex, I hope you can create a similar generator for tabular forms too

-- Jasem

Popular posts from this blog

Custom Export to CSV

It's been a while since I've updated my blog. I've been quite busy lately, and just have not had the time that I used to. We're expecting our 1st child in just a few short weeks now, so most of my free time has been spent learning Lamaze breathing, making the weekly run to Babies R Us, and relocating my office from the larger room upstairs to the smaller one downstairs - which I do happen to like MUCH more than I had anticipated. I have everything I need within a short walk - a bathroom, beer fridge, and 52" HD TV. I only need to go upstairs to eat and sleep now, but alas, this will all change soon... Recently, I was asked if you could change the way Export to CSV in ApEx works. The short answer is, of course, no. But it's not too difficult to "roll your own" CSV export procedure. Why would you want to do this? Well, the customer's requirement was to manipulate some data when the Export link was clicked, and then export it to CSV in a forma

Refreshing PL/SQL Regions in APEX

If you've been using APEX long enough, you've probably used a PL/SQL Region to render some sort of HTML that the APEX built-in components simply can't handle. Perhaps a complex chart or region that has a lot of custom content and/or layout. While best practices may be to use an APEX component, or if not, build a plugin, we all know that sometimes reality doesn't give us that kind of time or flexibility. While the PL/SQL Region is quite powerful, it still lacks a key feature: the ability to be refreshed by a Dynamic Action. This is true even in APEX 5. Fortunately, there's a simple workaround that only requires a small change to your code: change your procedure to a function and call it from a Classic Report region. In changing your procedure to a function, you'll likely only need to make one type of change: converting and htp.prn calls to instead populate and return a variable at the end of the function. Most, if not all of the rest of the code can rem

Logging APEX Report Downloads

A customer recently asked how APEX could track who clicked “download” from an Interactive Grid.  After some quick searching of the logs, I realized that APEX simply does not record this type of activity, aside from a simple page view type of “AJAX” entry.  This was not specific enough, and of course, led to the next question - can we prevent users from downloading data from a grid entirely? I knew that any Javascript-based solution would fall short of their security requirements, since it is trivial to reconstruct the URL pattern required to initiate a download, even if the Javascript had removed the option from the menu.  Thus, I had to consider a PL/SQL-based approach - one that could not be bypassed by a malicious end user. To solve this problem, I turned to APEX’s Initialization PL/SQL Code parameter.  Any PL/SQL code entered in this region will be executed before any other APEX-related process.  Thus, it is literally the first place that a developer can interact with an APEX p