Tabular Form Validation & Autocomplete with jQuery

Here is how to set autocomplete for tabular form field  and store key value with validation to database.
I did get lot of help to this when reading Patrick Wolf blog APEX Tips & Tricks.

If you are on APEX 3 load jQuery and jQuery UI library and css in page template header or page HTML header.

On APEX 4 add to page HTML header:

<link type="text/css" href="#IMAGE_PREFIX#libraries/jquery-ui/1.8/themes/base/jquery.ui.autocomplete.css" rel="stylesheet" />
<script type="text/javascript" src="#IMAGE_PREFIX#libraries/jquery-ui/1.8/ui/minified/jquery.ui.autocomplete.min.js"></script>

First, I did create tabular form on EMP table using wizard.
In the sample I did set Allowed Operations to "Update Only" and only MGR column can be updated.
You can have "Update, Insert and Delete" and select more columns to be updated.

Then create page process before regions:

IF APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => 'MGR_NAME_COL') THEN
  APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => 'MGR_NAME_COL');
END IF;
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
  p_collection_name => 'MGR_NAME_COL',
  p_query => '
    SELECT e.empno,
      m.ename AS mgrname
    FROM emp e,
      emp m
    WHERE e.mgr = m.empno(+)
  '
);

This is needed to create collection to store user entered values when validation error occurs.
Set process condition "No InLine Validation Errors Displayed".

Then modify your form Region Source:

SELECT e.empno,
  e.empno AS empno_display,
  e.ename,
  e.job,
  e.mgr,
  e.hiredate,
  e.sal,
  e.comm,
  e.deptno,
  c002 AS mgr_name,
  seq_id
FROM emp e,
  apex_collections
WHERE collection_name = 'MGR_NAME_COL'
  AND e.empno = c001

Go to Report Attributes.
Edit MGR column attributes and set Display As to "Hidden".

Edit SEQ_ID column attributes and set Show Column to "No".

Edit MGR_NAME column attributes and set Display As to "Standard Report Column".
Enter to HTML Expression:

<input type="text" value="#MGR_NAME#" name="f40" id="f40_#ROWNUM#" />
<input type="hidden" value="#SEQ_ID#" name="f41" id="f41_#ROWNUM#" />

If you are on APEX 3.2 create application On Demand process.
On APEX 4 you can create page process and set process point to "On Demand".
Process name GET_MGR. Source:

DECLARE
  l_sql VARCHAR2(32700);
BEGIN

  /* Query column aliases are important. Used in JavaScript */
  l_sql := '
    SELECT ename AS DIS,
      ename AS RET
    FROM emp
    WHERE ename LIKE ''' || UPPER(APEX_APPLICATION.G_X01) || '%''
    ORDER BY 1
  ';

  APEX_UTIL.JSON_FROM_SQL(l_sql);

EXCEPTION
  /* 
   Exception handler needed on APEX 3.2
   See Martin Giffy D'Souza blog post:
   http://www.talkapex.com/2009/05/apexutiljsonfromsql-no-rows-bug-fix.html 
  */
  WHEN OTHERS THEN
    HTP.prn('{"row":[]}');
END;

Create page level validation type "Function Returning Error Text":

DECLARE
  l_mgrno     NUMBER;
  l_err_mesg  VARCHAR2(32700);
BEGIN

  /* Save all values to collection and validate. Note: 
      g_f40 is the text field column MGR_NAME
      g_f41 is the hidden column SEQ_ID
  */

  FOR i IN 1 .. APEX_APPLICATION.G_F40.COUNT
  LOOP

    l_mgrno := NULL;  

    /* Save value to collection */
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE(
      p_collection_name => 'PAGE202COL',
      p_seq => APEX_APPLICATION.G_F41(i),
      p_attr_number => 2,
      p_attr_value  => APEX_APPLICATION.G_F40(i)
    );
    
    IF TRIM(APEX_APPLICATION.G_F40(i)) IS NOT NULL THEN

      /* Check that value exists and fetch return value */
      BEGIN
      
        SELECT empno
        INTO l_mgrno
        FROM wwv_demo_emp
        WHERE UPPER(ename) = TRIM(UPPER(APEX_APPLICATION.G_F40(i)))
        ;

      /* Set error message and collect rows in error */      
      EXCEPTION WHEN NO_DATA_FOUND THEN
      
        IF l_err_mesg IS NULL THEN
          l_err_mesg := 'Employee(s) not exists.<script type="text/javascript">$(function(){$("#f40_' || i;
        ELSE
          l_err_mesg := l_err_mesg || ',#f40_' || i;
        END IF;

      END;
  
    END IF;

    /* Set manager empno */
    APEX_APPLICATION.G_F02(i) := l_mgrno;

  END LOOP;
  
  /* If errors complete error message JavaScript and return error message */
  IF l_err_mesg IS NOT NULL THEN
    l_err_mesg := l_err_mesg || '").addClass("ui-state-error");});</script>';
    RETURN l_err_mesg;
  END IF;
  
  RETURN NULL;

END;

Set Error message display location to "Inline in Notification".

Place to page HTML header:

<script type="text/javascript">
$(function(){
 /* Set autocomplete */
 $('input[name="f40"]').autocomplete({
  source:function(req,res){

   /* Parameters for jQuery ajax */ 
   var opt={
    url:"wwv_flow.show",
    dataType:"json",
    traditional:true,
    cache:false,
    type:"POST",
    data:{
     p_flow_id:$v("pFlowId"),
     p_flow_step_id:$v("pFlowStepId"),
     p_instance:$v("pInstance"),
     p_request:"APPLICATION_PROCESS=GET_MGR",
     x01:req.term
    },
    success:function(jsonData){
     res(
      $.map(jsonData.row,function(jsonRow){
       return{
        label:jsonRow.RET,
        value:jsonRow.DIS
       };
      })
     );
    }
   };
   /* Call Ajax to get list */
   $.ajax(opt);
  },
  focus:function(){
   /* Prevent value change when list get focus */
   return false;
  }
 });
});
</script>