Solution

REPORT:

Source:

SELECT empno, ename, job, mgr, deptno, hiredate, sal, comm
FROM emp
WHERE ename like '%'||UPPER(:P2_PAR_ENAME)||'%'

Attributes:

Static ID: report_emp

PAGE:

Javascript:

function refresh_report(oTrgEl){
//alert('refresh: ' + $v(oTrgEl));
//calling the application process which sets the session state of P2_PAR_ENAME
var oGet = new htmldb_Get(null, 54687, 'APPLICATION_PROCESS=set_session_state', 2);
oGet.addParam("x01", $v(oTrgEl));
oGet.get(); //the app process just sets something, it returns nothing
//refresh the report region $("#report_emp").trigger("apexrefresh");
};

function bind_events(){
//call this onload
$("#P2_PAR_ENAME").change(function(){refresh_report(this);});
};

When page loads:

bind_events();

AJAX CALLBACK PROCESS:

Name: set_session_state
Process: apex_util.set_session_state('P2_PAR_ENAME', apex_application.g_x01);
Query Parameters
Employees
EmpnoEnameJobMgrDeptnoHiredateSalComm
7839KINGPRESIDENT-ACCOUNTING17-NOV-82566011111
7698PICARDCHILE7839SALES01-MAY-812850400
7782TestUser1OIU7839ACCOUNTING17-JUN-812450100
7566SMITHTRUCKER7839RESEARCH02-APR-81290025
7788URUGUAYAPEX7566ACCOUNTING01-NOV-811020
7902FRANCINECLERK27566RESEARCH09-DEC-80123410
736911CLERK7902RESEARCH18-DEC-80121003
7499STATHAMJASON7698SALES20-FEB-811400111
7521M BENZ 2ndSALESMAN7698SALES21-FEB-812360010
9663TOM6--12-NOV-19--
9720TOM96--14-NOV-19--
7876ADAMSSDF7788RESEARCH18-JAN-836110011
7900JAMESCLERK7876SALES10-DEC-818881
7934MILLERCLERK7782ACCOUNTING23-JAN-82116011
9999dgfdsfdf-ACCOUNTING17-DEC-2134354123
  • 1 - 15
IR source
select /*+qb_name(apex$54687_2)*/i.* from (select "EMPNO","ENAME","JOB","MGR","DEPTNO","HIREDATE","SAL","COMM" from(select /*+ qb_name(apex$inner) */d."EMPNO",d."ENAME",d."JOB",d."MGR",nvl( "APX$LOV5"."D", d."DEPTNO") "DEPTNO","APX$LOV5"."D" "APX$LOV5_DISPLAY","APX$LOV5"."R" "APX$LOV5_RETURN",d."HIREDATE",d."SAL",d."COMM" from(SELECT empno, ename, job, mgr, deptno, hiredate, sal, comm from emp where ename like '%'||UPPER(:P2_PAR_ENAME)||'%' )d,(select dname d, deptno r from dept order by 1 )"APX$LOV5" where "APX$LOV5"."R"(+) = d."DEPTNO" )i )i where 1=1