Monday 24 September 2018

JQUERY AUTOCOMPLETE FOR TEXT FIELD ITEM IN ORACLE APEX


Process of enabling users to quickly find and select from a  list of values as they type, searching and filtering without Native text with auto-complete in APEX.

This process works fine with Apex 4 and 5 versions but not 18.

Steps in APEX :


ü  Step 1 : Create a text field item P67_ENAME.
ü  Step 2 : Copy Below code in to the HTML Header of the page.
<!doctype html>
<html lang="en">
<head>
            <meta charset="utf-8">
            <title>autocomplete demo</title>
            <link rel="stylesheet"       href="//code.jquery.com/ui/1.12.1/themes/smoothness/jquery-ui.css    ">
      <script src="//code.jquery.com/jquery-1.12.4.js"></script>
      <script src="//code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
</head>

ü  Step 3 : a) Create an Application Process GET_NAMES.
          b) Change the Process Point to On Demand - Run this Process when                                                   requested by AJAX.
c) Copy the Below PL/SQL Code as source.
    
Note : This Process returns values matching the literals typed in the text field item in the JSON Format. 

DECLARE
   CURSOR c_names(pc_term IN VARCHAR2)
   IS
      SELECT      '{"label":"'
               || ename
               || '","id":"'
               || empno
               || '"}' ename
          FROM emp
         WHERE UPPER (TO_CHAR (ename)) LIKE
             UPPER ('%' || pc_term) || '%'
              ORDER BY 1;

   l_num                NUMBER          := 0;
   l_term               VARCHAR2 (4000);
  
BEGIN
   l_term := apex_application.g_x01;
   HTP.prn ('[');

   FOR rec IN c_names(l_term)
   LOOP
      IF l_num != 0
      THEN
         HTP.prn (',');
      END IF;

      HTP.prn (rec.ename);
      l_num := l_num + 1;
   END LOOP;

   HTP.prn (']');
END;

ü  Step 4 :

a) Create a dynamic action on change of P67_ENAME (Text Field).

  b) Create a True action as Execute Javascript Code and Check Fire on Page                      Load. Copy the below code.

Note : Below Javascript Code calls the AJAX Process whenever user types in the text field and passes the values to the process and displays the values in the textfield.
    
$( "#P67_ENAME" ).autocomplete({
  source:
function(req, add){
      //call the page process get_contact_data and put its return in greturn
      //this process returns markup for a JSON object so this can easily be parsed in jquery
      //x01: a temporary variable simply used for passing on a value
      $.post('wwv_flow.show',
             {"p_request"      : "APPLICATION_PROCESS=GET_NAMES",
              "p_flow_id"      : $v('pFlowId'),
              "p_flow_step_id" : $v('pFlowStepId'),
              "p_instance"     : $v('pInstance'),
              "x01"            : req.term
             },
              function(data){
                 if(data){
                    add($.parseJSON(data));
                               $("#P67_ENAME").removeClass("ui-autocomplete-loading");
                 }
                         else
                         {
                   $("#P67_ENAME").removeClass("ui-autocomplete-loading");
                         };
                         
             }
            );              
   },
   select: function(event, ui){
      $("#P67_ENAME").val(ui.item.value);
     // $("#P26_EMPNO").val(ui.item.value);
      event.preventDefault();
   },
   delay: 500,
   minLength: 1,
   autoFocus: true
});

6 comments:

  1. This is also works in 18.2 also,we tested and working fine for us.

    ReplyDelete
    Replies
    1. Can you tell how did you remove or why TypeError: $(...).autocomplete is not a function is comming

      Delete
    2. using jquery.noconflict for jquery version loaded through script file did the trick for me :)

      Delete
  2. this is not works in my 18.1 application console show this error -----
    TypeError: $(...).autocomplete is not a function
    at Object.javascriptFunction

    ReplyDelete
  3. Such a nice blog, I really like what you write in this blog, I also have some relevant Information about Best HR Training In Hyderabad | Hr training institute in Hyderabad! if you want more information.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad

    ReplyDelete