SQL & PL/SQL

I customized this one:

http://jquery.bassistance.de/autocomplete/demo/

for APEX, but haven't posted it yet. Here's a link:
http://drop.io/ajg81mf3381#

Here's an example of enabling it on a page for an item called P11_EVENT_OWNER:

<script type="text/javascript">
    $(document).ready( function() {
        $("#P11_EVENT_OWNER").autocomplete('APEX', {
                apexProcess: 'AUTO_CURRENT_MEMBERS',
                width: 400,
                multiple: false,
                matchContains: false
            });
    });
</script>

Here is the application process code for the process AUTO_CURRENT_MEMBERS:

declare
	l_search varchar2(255);
begin
	l_search := wwv_flow.g_x01;
    
    for c1 in (select user_name,email_address
                 from app_users
                where user_name like '%'||upper(l_search)||'%')
    loop
        htp.p(c1.user_name||'|'||c1.email_address);
    end loop;
end;

I only ask that you let me blog about this as I was planning to do so in the near future. Also, since I don't have any working examples to post, you're kind of on your own. Good luck.

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://www.amazon.com/gp/product/0071613706?ie=UTF8&tag=tylsblo-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071613706]

Hi,

I was able to do this. All you need to do is override the URL generation in the autocomplete section. I'll try to post the code snippets tonight or tomorrow.

Martin
-----
[http://apex-smb.blogspot.com/]

BTW, I chose it because it has soooo many options for output as you can see from the demos on the authors page.

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://www.amazon.com/gp/product/0071613706?ie=UTF8&tag=tylsblo-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071613706]

Tyler - I did the customary Google searches and came across a nice [summary page|http://choosedaily.com/1308/15-jquery-autocomplete-plugins-tutorials/] for JQuery autocomplete plugins. The plugin you mentioned is the "official" version but looks like the maintainers are not sure whether to include it in JQuery core or JQuery UI. Jörn Zaefferer has added support for a callback/function as the data source in the latest dev build but I try to stay away from such bleeding edge software.

I see that you have implemented the server request using JQuery's $.get which is a synchronous AJAX call. Did you try using the asynchronous version ($.ajax) or does that have timing issues?

Also, in your code example, I am not sure I understand your first argument to autocomplete (APEX). Isn't that supposed to be a data hash or a callback function?

Thanks for the help. I am more than happy to wait for you to blog about it, I love reading your blog posts. Hopefully the JQuery libraries hosted by Google include this plugin with support for callback source soon so we can just link it from there instead of hosting our own!

I blogged about my experience with YUI auto complete and APEX. I have been using it for about a year in a production environment with no trouble. If you are interested:

[http://jfortney.blogspot.com/2009/10/oracle-apex-and-yui-autofill-or.html|http://jfortney.blogspot.com/2009/10/oracle-apex-and-yui-autofill-or.html]

Jon Fortney
[http://www.sds-cg.com|http://www.sds-cg.com]

Hi,

I'm very interested by your solution. I would like to implement it for a "customer" field list. Is it possible to retrieve the id, so I could save the id value (_hidden_value) instead of the customer name?

Thank you.

Yes that is what I do. If you look in the frsAutoComplete javascript in the blog post you will see this line

 YAHOO.util.Dom.get(apexItemId).value = result[1];

This sets the apexItemId (the item you passed to frsAutoComplete to hold the ID value) with the ID value returned from your SQL query. At this point when the APEX page is submitted you can save the apexItemId as opposed to the apexItemValue.

Thanks and sorry I was trying to ask Tyler, I probably replied to the wrong user.

gfrancoeur,

Yes, it's VERY flexible. Lets assume your search field is P1_LAST_NAME, your results return first_name|last_name|phone. You could do something like this:

$("#P1_LAST_NAME").autocomplete('APEX', {
    apexProcess: 'AUTO_CURRENT_MEMBERS',
    width: 400,
    multiple: false,
    matchContains: false
});

$("#P1_LAST_NAME").result(function(event, data, formatted) {
    $("#P1_FIRST_NAME").val(data[0]);
    $("#P1_LAST_NAME").val(data[1]);
    $("#P1_PHONE").val(data[2]);
});

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://www.amazon.com/gp/product/0071613706?ie=UTF8&tag=tylsblo-20&linkCode=as2&camp=1789&creative=9325&creativeASIN=0071613706]

Vikas2,

Yes, I should have used $.ajax which is asynchronous vs $.get which is synchronous. Thank you for pointing that out. This was the first jQuery plug-in that I dug into, so I'm sure there will be changes when I actually post it... probably on samplecode.oracle.com.

On a related note, my jApex plugin does use $.ajax.

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://www.amazon.com/gp/product/0071613706?ie=UTF8&tag=tylsblo-20&linkCode=as2&camp=1789&cr

This module rocks!!!!

I just got it working with one of my apps (doing a person search) and I expect to apply it to more pages and more applications, as well as more types of searches.

I did notice some odd filtering going on in one test case - I need to investigate that a bit more - may be some caching of results which is not appropriate in this case.

This is using your version with the $.get("wwv_flow.show" call rather than the $.ajax call.

Many thanks for sharing your work!

Tyler,

Figured out my searching issue (matching item vs displaying item).

Follow up question - my underlying database search has a flag "IS_ACTIVE". I would like to have a checkbox on the search page (or a radio button) - and pass that setting to the On Demand Procedure, so it can pass it along to the PL/SQL search routine.

From a program design standpoint, I think I would like to have the javascript that sets up the call to autocomplete, query the current value of the item and pass it as an extra parameter to the on demand procedure. Does this seem reasonable? How would I reference that in the on demand procedure? This would allow me to have on "PERSON_SEARCH" on demand procedure in the application that could be called from different pages in the application.

Jon,
I think what you'd need to do is modify the plugin slightly to allow you to declaratively pass in multiple parameters. You can see he only coded in one place for a variable that gets put in x01 - I think if you see how he does it in his japex plugin, you can add those options in. Or maybe he will be kind enough to give some help.

I'm thinking it would end up something like this:

$("#P1_LAST_NAME").autocomplete('APEX', {
    apexProcess: 'AUTO_CURRENT_MEMBERS',
    x01: $("#P1_LAST_NAME"),
    x02: $("#P1_CHECKBOX"), 
    width: 400,
    multiple: false,
    matchContains: false
});

But basically in your on-demand process, you would use wwv_flow.g_x02 to reference the extra item once you coded the jquery part. The apex side is the easy part - jquery, maybe or maybe not.

Here you can find some code if you want to use the jquery.autocomplete.js without modification.
Download the autocomplete jQuery plugin and upload then to your "Shared Components"/"Cascading Style Sheets".
Also upload the basic jquery library there.

Download Links:
http://bassistance.de/jquery-plugins/jquery-plugin-autocomplete/
http://jquery.com/

Add the following to your Page HTML Header where you want to use the autocomplete list:

<script type="text/javascript" src="#WORKSPACE_IMAGES#jquery.ajaxQueue.js"></script>
<script type="text/javascript" src="#WORKSPACE_IMAGES#jquery.autocomplete.js"></script>
<link rel="stylesheet" type="text/css" href="#WORKSPACE_IMAGES#jquery.autocomplete.css" />
<script type="text/javascript">
$(document).ready(function(){

$.ajaxSetup({
  type: "POST",
  dataType: "text",
  beforeSend: function (xhr) {
    var apexParams = { 
     p_flow_id: $("#pFlowId").val(), 
     p_flow_step_id: $("#pFlowStepId").val(), 
     p_instance: $("#pInstance").val(),
     p_request: 'APPLICATION_PROCESS=AJAX_LIST_OF_VALUES'
    };
    this.data = this.data.replace('q=','x01=');
    this.data = this.data.replace('limit=','x02=');
    this.data = this.data.replace('timestamp=','x03=');    
    this.data = this.data+"&"+jQuery.param(apexParams);
  }  
});

$("#P7_CUST_FIRST_NAME").autocomplete('wwv_flow.show', {
    extraParams: { x04: 'some extra information', x05: 'for your on demand process'},
    multiple: false,
    matchContains: false
});

$("#P7_CUST_LAST_NAME").autocomplete('wwv_flow.show', {
    extraParams: { x04: 'some extra information', x05: 'for your on demand process'},
    multiple: false,
    matchContains: false
});

}); // end of jQuery
</script>

This looks complex, but the only thing your have to change is to change the name of the item where you want to have a autocomplete list.

have fun, willi

I am trying to get Tyler's example going. I think I am missing something since I assume that I have to put some javascript in the HTML Form Element Attribute section?

No need for JS in form element attributes. The "P11_EVENT_OWNER" is the item in my example that gets configured for autocomplete. It's using a jQuery selector.

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://sn.im/aos.book]

OK. I have uploaded jquery.autocompleteApex.min.js as a static file and put the following in the page header.

<script type="text/javascript" src="#WORKSPACE_IMAGES#jquery.autocompleteApex.min.js"></script>
<script type="text/javascript">
$(document).ready( function() {
$("#P11_EVENT_OWNER").autocomplete('APEX', {
apexProcess: 'AUTO_CURRENT_MEMBERS',
width: 400,
multiple: false,
matchContains: false
});
});
</script>

I created a page item in an HTML region item as P11_EVENT_OWNER and created the on-demand process but am not getting any autocomplete result. I think I am missing a step. TIA.

I have put my example at:

http://apex.oracle.com/pls/otn/f?p=40199:1

If someone would kindly have a look and provide some insight about where I am gong wrong, I would appreciate it.

Tyler

I have also tried to complete as you specified.. but it won't work..

Do you have any more details?

Thanks
Dean

PSpence,

I've looked at your app. There are 2 JavaScript files required for this plugin to work and you are only loading 1. Use the Firebug plugin for Firefox to debug this ("Net" tab). And yes, I'm being intentionally vague as learning to debug this stuff will help you more than you can imagine. So, what are the two files and which one is not loading?

Once you have that working, you simply can't live without the "Net" panel to debug the request / response traffic of an AJAX control. You might also look at my Logger utility as I wrote it when debugging AJAX controls in APEX.

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://sn.im/aos.book]

Tyler

thanks.. works a treat..

Not sure how this works.. but it does...

$("#P11_EVENT_OWNER").result(function(event, data, formatted) {
$("#P11_EVENT_OWNER").val(data[0]);
$("#P1_X").val(data[1]);
}); 

OK. So I have FireBug fired up. I have the Net panel enabled. I am looking for some undefined reference due to the missing file but do not see any errors. The request to jquery.autocompleteApex.min.js has the correct response. A hint perhaps?

What file is required for every jQuery plugin?

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://sn.im/aos.book]

Ahh. The jquery core file would be helpful. I added jquery.js (v. 1.3.2) but still not luck.

Postie,

You really need to take some time to learn about jQuery "selectors". Head on over to jquery.com and do some reading then go through [this tutorial|http://docs.jquery.com/Tutorials:Getting_Started_with_jQuery].

Experiment a bit too. Run your page, open firebug, then open the console and try these commands:

$("#P11_EVENT_OWNER").css('background-color','red');
$("#P11_EVENT_OWNER").toggle();  // run it several times.
$("#P11_EVENT_OWNER").closest('td').css('border','1px #00ff00 dashed');
$("#P11_EVENT_OWNER").closest('table').css('border','1px #0000ff solid');
$("#P11_EVENT_OWNER").closest('table').find('td')
    .mouseover(function(){
        $(this).css('background-color','#ff0000');
    })
    .mouseleave(function(){
        $(this).css('background-color','#ffffff');
});

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://sn.im/aos.book]

OK, you're getting closer. Now it's an issue of the order in which they load. You had a 50/50 chance and you lost ;)

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://sn.im/aos.book]

I lost on a 50/50 bet, thus why I never gamble. :) Thanks for the help. Just curious, where should I have seen this error in FireBug? I have used firebug some in the past, mostly just to debug javascript I have written. As you can tell, I am new to the AJAX libraries.

I have implemented Tyler's code and that works fine. Can we change the appearance of the autocomplete list. I want it to have the names as a select list and when the user scrolls on the names it should get colored in the background.

Thanks
Kumar

Kumar,

Take a look at the first example here:
http://jquery.bassistance.de/autocomplete/demo/

Make sure you understand jQuey and how this plugin works before you get too fancy.

Tyler Muth
http://tylermuth.wordpress.com
[Applied Oracle Security: Developing Secure Database and Middleware Environments|http://sn.im/aos.book]

Tyler

Workign nicely, except I am trying to get a scroll list working.. but it won't scrol in the list

I am using

$("#P11_EVENT_OWNER").autocomplete('APEX', {
  apexProcess: 'AUTO_CURRENT_MEMBERS',
  scroll:true,
  scrollHeight:100,
  matchContains: false
});

Any ideas on how to get working

Thanks
Dean

Hi Tyler

I emailed Jörn.. the developer of this.. and here is his response

From: Jörn Zaefferer 
Subject: Re: autocomplete

Hi Dean,

I currently don''t plan any further releases of my standalone autocomplete plugin. 
jQuery UI 1.8, due in about a week, will include a way better Autocomplete widget. 
Details are here: http://wiki.jqueryui.com/Autocomplete

Jörn 

Will you be modifying your code to fit with the official jQuery version of Autocomplete?

Tx
Dean

So I have a somewhat odd issue going on. My example at:

http://apex.oracle.com/pls/otn/f?p=40199:1

only works when I am running FireBug. When I first go to the site, I do not get the autocomplete functionality until I start FireBug. This is with FireFox 3.5.7. Anyone?

Try.. refreshing webpage a couple of times.. IE is F5 key.. sometimes for some reason the scripts have not loaded... completely..

You also need to look at putting in CSS

Dean

Refreshing does not work, in IE (6.0.29) or FireFox. It still only works when FireBug is running.

Could someone do me the favor of posting a working example of this to apex.oracle.com so that I can see where I am going wrong. TIA.

Strange.. I had this working yesterday on IE? http://apex.oracle.com/pls/apex/f?p=44076:1
does it work on Firefox? I don't have firefox at work...

Just wait until the new Autocomplete is released next week.. looks much better..
http://wiki.jqueryui.com/Autocomplete
http://jquery-ui.googlecode.com/svn/trunk/demos/autocomplete/remote-jsonp.html

Dean

working now..
http://apex.oracle.com/pls/apex/f?p=44076:1

make sure you wait until page fully loaded... say 15 sec.. then start typing..

OK, I see that mine is working as well, it just takes a long time to load. For some reason FireFox takes slightly longer than IE. Thanks for the help.

Hi Willi,

.ajaxSetup is clashing with the functionality of PPR reports, causing them to stop functioning - do you know how to keep both autocomplete and PPR reports?

thanks,

Davor

Tyler,
In this case what will be your on demand process looks like, I mean what should I put in the

htp.p

procedure.

also I am not able to download the file for the plugin and the sample example.

Thanx in advance

Hi Dean,

Any luck with Tyler regarding this issue.

Thanks,
Omar

Sorry it took me so long, but here's a more complete description and an updated version of the plugin:
http://tylermuth.wordpress.com/2010/03/16/jquery-autocomplete-for-apex/

Tyler Muth
http://tylermuth.wordpress.com
"Applied Oracle Security: Developing Secure Database and Middleware Environments": http://sn.im/aos.book

Tyler,

I saw your blog about the auto completion box for Apex here http://tylermuth.wordpress.com/ . I compared your javascript to the original version javascript for the program and looked at the documents for it..

Here is my question..

The Application Process that is called from the autocompletion box needs to also get a value from another Apex Field since my select is trying to do a left outer join to see if the "other field" already has data entered or not, I do not want to eliminate the ones that do not have data entered, I just would indicate the ones that have been entered with a (*) in front.

Here is my actual Select in the Apex Operation...

for c1 in (select distinct DECODE(TRIM(v('P310_CDBILL')), NULL, NULL, DECODE(b.nuxrgroup, NULL, NULL, '(*) '))||a.nagroup nalabel, a.nagroup
from bl46groups a
LEFT OUTER JOIN (SELECT * from BD46BILLMEMO WHERE cdstatus = 'A') b ON (a.nuxrgroup = b.nuxrgroup)
where UPPER(a.nagroup) like '%'||UPPER(l_search)||'%'
ORDER BY 1)

Note: l_search is on a field called P310_NAGROUP (Group Name) which is what shows the autoselection box, P310_CDBILL will have a bill#.

Here is an Example:

Bill#: S1009 Group:(Has Autosuggestion box): SA
Sample Suggestion Box Output:
Uncle Sam's Fish Fry
(*) Sailors Anonymous
Sad
Drunk Sailors Group
Savvy People

In this example, BD46BILLMEMO has information entered for S1009/*Sa*ilors Anonymous and S1009/*Sa*vvy People, but not for Uncle Sam's Fish Fry. The bl46groups table has all of these groups listed above...

How do I get the information from the CDBILL field back to the Apex Process? I know how to do it with the get.add(.....) iwhen you call the Application Process from javascript but not sure with this autosuggestion box in the mix. I also noticed http://docs.jquery.com/Plugins/Autocomplete/autocomplete#url_or_dataoptions but extraParams eliminates values from the suggestion box so I would like to pass the CDBILL values to the Apex Application as well.


Thank you in advance,
- Brian

Someone asked this earlier in this thread (go back to the first page) and I added my suggestion. I think you'd need to modify the plugin slightly to allow you to pass extra parameters - right now it is hard-coded to post just to x01 - you'd need to extend it to allow you to post to x02..x10 and then you could reference that in your on demand process.

Hi,

I have alternative solution here
http://actionet.homelinux.net/htmldb/f?p=100:73

It need jQuery 1.4.2 and jQuery-UI 1.8.0
Source table is Apex Demonstrative application DEMO_STATES

How it works
I have text item P73_STATE and Page HTML header

<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.0/themes/redmond/jquery-ui.css" type="text/css" />
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
  google.load("jquery", "1.4.2");
  google.load("jqueryui", "1.8.0");
</script>
<script type="text/javascript">
$(function(){
 $("#P73_STATE").autocomplete({ // Change P73_STATE to your text item id
  source: function(r,s){
   $.ajax({
    type:"POST",
    url:"wwv_flow.show",
    dataType:"json",
    data:{
     p_flow_id:$v('pFlowId'),
     p_instance:$v('pInstance'),
     p_flow_step_id:$v('pFlowStepId'),
     x01:$v('P73_STATE'), // Change P73_STATE to your text item id
     p_request:"APPLICATION_PROCESS=P73_AUTOCOMPLETE"},// Change P73_AUTOCOMPLETE to your On Demand Process name
    success:function(d){
     s($.map(d.row,function(l){
      return{
       label:l.STATE_NAME,value:l.STATE_NAME // Change STATE_NAME to column name you return from On Demand Process
      }
     }));
    }
   });
  },
  open:function(){$(this).removeClass("ui-corner-all").addClass("ui-corner-top");},
  close:function(){$(this).removeClass("ui-corner-top").addClass("ui-corner-all");}});
});
</script>

And On Demand Process P73_AUTOCOMPLETE

DECLARE
  l_sql VARCHAR2(32000);
BEGIN
  l_sql := '
    SELECT state_name
    FROM demo_states
    WHERE state_name LIKE ''' || UPPER(APEX_APPLICATION.G_x01) || '%''
    ORDER BY 1
  ';
  APEX_UTIL.JSON_FROM_SQL(l_sql);
EXCEPTION
  WHEN OTHERS THEN
    HTP.p ('{"row":[]}');
END;

Hope this helps

Br,Jari

I updated my plugin code as well as the blog post here:
http://tylermuth.wordpress.com/2010/03/16/jquery-autocomplete-for-apex/

In short, you can now use x02-x10 for extra parameters:

$("#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()
        });

Tyler Muth
http://tylermuth.wordpress.com
"Applied Oracle Security: Developing Secure Database and Middleware Environments": http://sn.im/aos.book

Hi,

I think now we can have JQuery Autocomplete without any additional Plugin using JQuery UI...
Here is what I have tried using html db Get method

<link type="text/css" rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.0/themes/redmond/jquery-ui.css">
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.0/jquery-ui.js"></script>

.ui-autocomplete-loading { background: pink  url('http://apex.oracle.com/i/htmldb/icons/pt_process.png') right center no-repeat}

</style>
<script type="text/javascript">
$( function() {
    
    $("#P8_EMPLOYEE").autocomplete({
        source : function( request , response) { 
                        data = getEmployee(request.term);
                        response( data );
                 } ,
        focus  : function(event , ui){
                    event.preventDefault();
                 }

    });
});

function getEmployee(key)
{ 
      var ajaxRequest = new htmldb_Get( null , '&APP_ID.' , 'APPLICATION_PROCESS=getEmployee' , 0);
   ajaxRequest.addParam( 'x01' , key);
   ajaxResult = ajaxRequest.get(); 
   
   return ((ajaxResult.length>0)? ajaxResult.split( ',' ):null);

}
</script>

ondemand

declare
   l_str varchar2(100);
   i number;
begin
   l_str := wwv_flow.g_x01;
   i := 0;
   for c1 in ( select first_name , last_name  from employees where upper(first_name || ' ' || last_name) like upper(l_str) || '%')
   loop
       if i = 0 then
          htp.prn( c1.first_name || ' ' || c1.last_name   );
       else
         htp.prn( ',' || c1.first_name || ' ' || c1.last_name   );
       end if; 
       i := i + 1;
   end loop;

end;

We can add cache variable for better performance


Regards,
Shijesh

I'm not a sofware engineer so i don't have any knowedge whether the above code is good or bad

Hi,

Did you read my post ?
It does not use plugin, just jQuery-UI 1.8

Br,Jari

Opps , I missed your post , my apology... by the way I am your fan and i have learned a lot from you post..
In my post i have used htmldb_get instead of jquery's $.ajax()


Regards,
Shijesh

The difference between the plugin I posted and the solution using jQuery UI is the stand-alone plugin is very small and lightweight whereas jQuery UI has pretty large footprint. If you plan to use multiple components of jQuery UI, then you should explore that option. If all you want is auto-complete, then jQuery UI is a pretty heavy option.

Also keep in mind the APEX 4.0 will ship with jQuery UI and probably and auto-complete control, so for 4.0+, it's a moot point.

Tyler Muth
http://tylermuth.wordpress.com
"Applied Oracle Security: Developing Secure Database and Middleware Environments": http://sn.im/aos.book