Redirect user if APEX classic report not return rows

Here is how you can redirect user to another page using PL/SQL if APEX classic report not return rows. You can do same whit e.g. JavaScript, but sometimes it is just better use PL/SQL. JavaScript runs client side and users might avoid redirect with different methods.

I did get idea for this blog post from OTN forum post.

In example I did create classic report from query:

SELECT empno
    ,ename
    ,job
    ,mgr
    ,hiredate
    ,sal
    ,comm
    ,dname
    ,loc
    ,(SELECT APEX_UTIL.SAVEKEY_VC2('Y') FROM DUAL) AS DUMMY -- Save value if query return rows
FROM (
SELECT e1.empno
    ,e1.ename
    ,e1.job
    ,e2.ename AS mgr
    ,e1.hiredate
    ,e1.sal
    ,e1.comm
    ,d.dname
    ,d.loc
FROM emp e1
LEFT JOIN emp e2
    ON e1.mgr = e2.empno
JOIN dept d ON e1.deptno = d.deptno)
WHERE (
 instr(upper("ENAME"),upper(nvl(:P33_REPORT_SEARCH,"ENAME"))) > 0  or
 instr(upper("JOB"),upper(nvl(:P33_REPORT_SEARCH,"JOB"))) > 0  or
 instr(upper("MGR"),upper(nvl(:P33_REPORT_SEARCH,"MGR"))) > 0  or
 instr(upper("DNAME"),upper(nvl(:P33_REPORT_SEARCH,"DNAME"))) > 0  or
 instr(upper("LOC"),upper(nvl(:P33_REPORT_SEARCH,"LOC"))) > 0 
)

In report query function APEX_UTIL.SAVEKEY_VC2 saves value to APEX package global variable, if query returns rows.

We can check that variable value in After Regions process using function APEX_UTIL.KEYVAL_VC2, and redirect user if value is null.

Example code for After Regions process:

IF APEX_UTIL.KEYVAL_VC2 IS NULL THEN
  APEX_UTIL.REDIRECT_URL('f?p=&APP_ID.:34:&APP_SESSION.');
END IF;

See working example.

Search from report and if your search do not return any rows, you will be redirected to another page.