Follow Us on Twitter

[Oracle APEX 4.0] Text Field with autocomplete

by Peter van der Neut on February 25, 2010 · 5 comments

In previous APEX versions there is a lot of functionality you can add by simply incorporating some custom javascript. One example is a text field that shows an inline list of values for an item based on a query. Of course this is great functionality to provide to the user, but not that great for maintainability because of the custom code. APEX 4.0 now provides this functionality out of the box with the new item type Text Field with autocomplete.

Now you can declaratively create an item that provides an inline list of values that also filters while typing in the item. The list is populated by the list of values defined for the item, this can only be a dynamic list of values. There are several options when creating this item type.

An explanation of some of the options:

  • Search: this defines how the search is performed, search for any character (contains) or match the characters exactly. This option also defines if the search is case-sensitive or not.
  • Only Values from List: if set to yes, this means that only a value from the list of values is valid.
  • Lazy Loading: Yes means the list will be read from the database every time the user types a character. No means the list will only be populated when the page is rendered.
  • Maximum Values in List: this limits the number of values in the list.
  • Highlight Search: when set to yes, this will highlight the matching characters in the list.

We create an example autocomplete item P6_DEMO_STATE that list all of the states from the table demo_states. The search type is set to Contains & Ignore Case and the search is highlighted. This gives the following result in our page:

The new Text Field with autocomplete really gives something extra to the user interface. Of course you should be cautious on what table you define this item, because it generates a lot of queries, especially with the Lazy Loading set to yes. All in all, another nice new feature in APEX 4.0.

[Oracle APEX 4.0] Text Field with autocomplete, 4.9 out of 5 based on 8 ratings
VN:F [1.9.22_1171]
Rating: 4.9/5 (8 votes cast)

{ 5 comments… read them below or add one }

Toon Schilder March 2, 2011 at 12:52 am


Very nice functionality. Let’s hope Oracle adds much more functionality (master/detail etc.) in Apex 4.1!

Kind regards,
Toon Schilder


aminur January 11, 2013 at 4:31 pm

I’ve got a coursework on apex and i was wondering if you could show how this is done.


Anantha Narayanan February 26, 2013 at 5:57 pm

I am really confused with this field. Can you point to me an example of how to setup this?

I created a field with a query (which supports only 1 value), how should I save the id of the value selected?


Herne February 12, 2014 at 8:26 am

Nice tutorial.

Do you know what is the default Maximum Values in List?

I have a LOV with over 10,000 case insensitive unique values. With this field left blank, I cannot seem to find those starting with ‘Z’.

Thanks, Herne


Michel van Zoest March 10, 2014 at 2:56 pm

Hello Herne,

Thank you for your comment.

I’m not sure about this, but I can make an assumption.
When querying an Interactive Report, APEX will return no more than 10.000 rows and then raises a warning. I’m assuming that this maximum is also valid for LOV queries.

To make sure I’ll ask someone at Oracle. I’ve never created this big of a list myself.


Leave a Comment


Previous post:

Next post:

About Whitehorses
Company profile

Whitehorses website

Home page

Follow us
Blog post RSS
Comment RSS