Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

jQuery Autocomplete for APEX – Update 1.2

Posted by Tyler Muth on March 16, 2010

A while back I modified this outstanding  jQuery Autocomplete plugin to work with APEX. Lately there’s been a lot of interest in a jQuery autocomplete plugin on the APEX forum so I put together a few examples and cleaned up the code a bit. Here’s how it works:

  • When the page is loaded the plugin adds an event listener to one or more items specified by jQuery selector.  For example $(‘P1_SEARCH’).autocomplete… will enable autocomplete for the P1_SEARCH item.
  • As a user enters text into this item, the plugin sends the text back to an APEX Application Process as an http get request.
  • The Application Process queries a table using the text it receives in one or more query predicates.
  • The process sends back a pipe (|) delimited list of results via htp.p
  • The user can use one or more columns from the result to populate one or more items on the page.

Take a look at the APEX examples here.  You should also explore the original author’s examples here as he demonstrates a lot more functionality.  The APEX version is capable of producing any of those examples as well.

Update: As requested in the comments, I’ve added parameters x02 – x10 to allow extra page items to be passed back to the application process. I’ve updated page 2 and the following example code to show this.

Here is the JavaScript in the page header of the “Multiple Fields” example:

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="#WORKSPACE_IMAGES#jquery.autocompleteApex1.1.js"></script>
<link rel="stylesheet" type="text/css" href="#WORKSPACE_IMAGES#jquery.autocomplete.css" />

<script type="text/javascript">

$(document).ready( function() {
    $("#P2_SEARCH").autocomplete('APEX', {
            apexProcess: 'EMPLOYEES_EXTENDED',
            width: 400,
            multiple: false,
            matchContains: true,
            cacheLength: 1,
            max: 100,
            delay: 150,
            minChars: 1,
            matchSubset: false,
            x02: 'foo',
            x03: $('#P2_DEPARTMENT').val()
        });
    // the following statement sends the result to multiple items on the page.
    $("#P2_SEARCH").result(function(event, data, formatted) {
        if (data){
            $("#P2_FIRST_NAME").val(data[1]);
            $("#P2_LAST_NAME").val(data[2]);
            $("#P2_DEPARTMENT").val(data[3]);
            $("#P2_EMAIL").val(data[4]);
        }
    });
});

</script>

And here’s the application process that drives the search results. Note the 2 alter session statements to make the search case-insensitive:

-- This Application Process is named EMPLOYEES_EXTENDED
declare
	l_search varchar2(255);
begin
    execute immediate 'alter session set NLS_SORT=BINARY_CI';
    execute immediate 'alter session set NLS_COMP=LINGUISTIC';

    l_search := replace(wwv_flow.g_x01,'*','%');

    for c1 in (select first_name||' '||last_name name,last_name,first_name,department_name,email
                 from employees_rollup
                where first_name like '%'||l_search||'%'
                   or last_name like '%'||l_search||'%')
    loop
        htp.p(c1.name||'|'||c1.first_name||'|'||c1.last_name||'|'||c1.department_name||'|'||c1.email);
    end loop;
end;

Update: If you want the loading animation, you need to upload the image and add the following in the head of your page template. Thanks Dean Attewell for commenting on this omission.

<style type="text/css">
.ac_loading {
	background: white url('#WORKSPACE_IMAGES#indicator.gif') right center no-repeat;
}
</style>

You can download the updated version 1.2 JavaScript, CSS, and image files here.

33 Responses to “jQuery Autocomplete for APEX – Update 1.2”

  1. Sergio said

    Tyler,

    Thanks for putting this together. I find that it helps to turn off the browser’s built in autocomplete, which you can do by including autocomplete=”off” in the Form Element Attributes of an APEX item. I noticed that you did this in your demo.

    Sergio

  2. Denes said

    Hello Tyler,

    Thanks for this nice solution.

    Denes Kubicek

  3. John Scott said

    Sergio,

    There is now (introduced in 3.2 I believe) a “Form Auto Complete” setting at the page level which will do the same thing (as disabling it for individual items).

    John.

  4. Todd said

    Tyler,

    I like your simple solution of sending values to multiple items given your search result.

    Here is a related How To. In this example I like the use of instr and order by in the cursor of the application process. This logic gives preference to search values being found on the left side of the column ordered first.
    http://apex.oracle.com/pls/otn/f?p=18035:6


    CURSOR data_cur IS
    SELECT a.d,a.r
    FROM (
    SELECT e.last_name||', '||e.first_name d
    ,e.employee_id r
    ,instr(lower(e.last_name||', '||e.first_name),:F111_1) pos
    FROM SCHEMAX.employees e
    WHERE instr(lower(e.last_name||', '||e.first_name),:F111_1) > 0
    ORDER BY 3,1
    ) a
    WHERE rownum < 11;

    Thanks for the nice post!

    Todd

    • Tyler Muth said

      Todd,

      Unless you’re using function-based indexes, your query will not use an index.

      Tyler

      • Todd said

        Good point on the instr function in the where clause causing the index not to be used. So far my usage of this instr logic has been on small tables so I had not noticed. It is certainly something to consider if using a large table.

        Thanks!

  5. Dean Attewell aka Postie said

    Very nice..

    Are your instructions missing this? or something to show the image?

    #t20Logo span{white-space:nowrap;color:#ffffff;font-weight:bold;font-size:200%}

    .ac_loading {
    background: white url('wwv_flow_file_mgr.get_file?p_security_group_id=225060025660485460&p_fname=indicator.gif') right center no-repeat;
    }

  6. Stew said

    Tyler,

    Thanks as always for your great contributions to the Apex community.

    Stew

  7. This is great!
    Many thanks.
    I have been using Jquery for the past 12 months, it makes life so much simpler.
    I had been using the Yahoo UI autocomplete, but with this the same is a acheived with mush less code.

    Ian

  8. Tyler,

    Nice!

    What do I do if I have pipes in my data? If I replace them with | will they display OK?

    Regards,

    Colin

  9. Dan said

    Tyler,

    Did you see that this was added to the latest version of jQuery UI?
    http://jqueryui.com/demos/autocomplete/

    Regards,
    Dan

  10. Stijn said

    Nice solution.
    But maybe a better approach to get your query result, is by making use of the APEX_UTIL.JSON_FROM_SQL.

    Greetings
    Stijn

  11. Peter said

    great approach! thx for that.
    i would like to use the autocomplete in my own apex application.
    where can i get the most recent version of “jquery.autocompleteApex1.2.js”?

  12. Tobias said

    Hi Tyler,

    your really made a great example here. I just got some trouble with the character converting in German.

    Here is the string I used to get ‘ü,ö,ä’ to work with my database:

    l_search := replace(convert(wwv_flow.g_x01,’WE8ISO8859P1′,’UTF8′),’*’,’%’);

    Best regards,

    Tobias

  13. Ron said

    This helped me quite a bit today – thanks a ton!

    My scenario involved the construction of a street address, so there was a road direction, street name, suffix, city, state, and zip. We wanted a list to pop up of valid street names (queried from a table of all streets in our community – including direction and zip). Select the street name, and all of the other fields autocomplete. This solution worked great for that.

    I did put a couple of mods in for the ondemand process that may benefit some – depending on your scenario – so I’ll post. We put an Oracle Text index on the street_name (could be more columns using a multi-column datastore) and tokenized at a pretty granular level (3 chars and up). Oracle Text is case-insensitive by default, so the alter session statements could go away. With that granular tokenization I could get rid of the wildcards too. If I had multiple columns that I was searching, the multi-column datastore would let me search using a single CONTAINS too. My index is sync (on commit). You’ll want to adjust the REPLACE to take out reserved words/characters for Oracle Text.

    The Oracle Text addition wouldn’t be appropriate for every scenario, but it is working like a charm for our needs when combined with Tyler’s code at the top.

    -Ron

  14. oo I like the article, good buddy

  15. […] dem Beispiel von https://tylermuth.wordpress.com/2010/03/16/jquery-autocomplete-for-apex/ habe ich mir Gedanken gemacht, wie man dieses nette Feature für mehrere Textfelder wiederverwenden […]

  16. Santhosh said

    Thanks a lot for the nice post….:)

  17. Peter said

    hi tyler,
    while re-reading the documentation about teh various options i found this sentence

    Note (2010-06-23): This plugin is deprecated and not developed anymore. Its successor is part of jQuery UI, and this migration guide explains how to get from this plugin to the new one.

    at the website of the
    have you thought of updating your apex adaption?

  18. Bo said

    Tyler,

    Have you been able to recreate this plugin in apex 4.0 using the new plug-ins capability. I am new to apex and would love to see how this would work with the new plug-ins. This one great but it doesn’t seem to give the flexability that apex plug-ins would have.

  19. Chris said

    Tyler, much thanks for this. I’m using it now, but had a question.

    I have a report based off a users selection from this autocomplete. How could this work if I wanted to use multiple choices from the autocomplete in the report?

    For example, the autocomplete is searching through company names, and they want the report based off CompanyA and CompanyB. How could I select A, B, and THEN run the report?

    Thanks again!

    • Chris said

      Well, I should’ve looked at the authors demo first haha😛

      I was able to select multiple results using the two properties,

      multiple: true,
      multipleSeparator: “:”,

      Now, I need to modify my report’s where clause to strip out the separator.

    • Chris said

      Tyler,

      Trying to use multiple values does not appear to work in IE7. Any ideas?

      Thanks

  20. Udo said

    There was a problem with ORA-06502 that may occur in APEX 4.0 (possibly in previous versions as well) when using the German translation (possibly also other non-English languages) after several hours of operation. The errors affected the whole APEX instance and could be “worked around” by changing to english. The problem could be tracked down to the application process, to be specific to the alter session statement changing NLS_COMP. The solution was to save the original state of that parameter before changing it and restoring it afterwards. Since the database session isn’t necessarily used for one APEX session and other comparisons even in the same session might depend on exact comparisons, I’d recommend to do this even if no ORA-06502 occurs. Though not necessary to solve the ORA-06502, I think it would be preferable to do this with the second parameter NLS_SORT as well. The following snippet shows the additional section for restoring both parameters.

    declare
    l_search varchar2(255);
    l_nls_sort varchar2(64);
    l_nls_comp varchar2(64);
    begin
    select value into l_nls_sort from v$nls_parameters where parameter='NLS_SORT';
    select value into l_nls_comp from v$nls_parameters where parameter='NLS_COMP';
    execute immediate 'alter session set NLS_SORT=BINARY_CI';
    execute immediate 'alter session set NLS_COMP=LINGUISTIC';
    -- main code starts here
    -- ...
    -- main code ends here
    execute immediate 'alter session set NLS_SORT='||l_nls_sort;
    execute immediate 'alter session set NLS_COMP='||l_nls_comp;
    end;

    Probably it would be better to store the session parameters in application items, so they doesn’t have to be retrieved on every call. The snippet is just intended as “prove of concept”.

    For details on the ORA-06502, see the corresponding thread in the OTN forum

    If somebody has a better approach, please reply.

    Thanks,

    Udo

  21. Garima tomar said

    Hi , I am trying to write a query for autocomplete from database, for e.g if i write 95 , then it should display all group numbers starting with 95 and are present in database

  22. Peter said

    nice idea. but why do u post? have trouble?

  23. Max said

    This is not working for a new record. Any idea how to get this working?

  24. Arun said

    Hi Tyler,

    I need Auto Complete functionality in Interactive Report column. I am using APEX_ITEM.text().

    Using APEX 4.2. Any suggestion to use this in Interactive Report would be helpful.

  25. obi chuks said

    I tried this. But only the first row of my tabular form had a textfield autocomplete. When I went to other rows, it didn’t autocomplete. Any clues??

  26. Hi Taylor,

    I need to show search results in separate div. Pls help.
    Thanks,
    M

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: