Skip to Main Content

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.

Comments

  • Jari Laine 23-JAN-14 03.57.34.000000 AM

    Hi Sriram,

    Sample application is not available for download. Sorry.

    Regards,
    Jari

  • Sriram Iyer 22-JAN-14 09.37.24.000000 PM

    I came to your site via your sample apex app on apex.oracle.com. App # 39006. Is this application available for download on your blog site?

    Thanks

    Sriram

  • Jari Laine 25-MAY-13 07.32.51.000000 AM

    Hi Tony,

    Thank you.

    Unfortunately, I'm not able to come Kscope13 😞

    I hope we meet again some other time.

    Regards,
    Jari

  • tony miller 25-MAY-13 02.28.58.000000 AM

    Great Solution!!

    Hopefully we will meet again @ Kscope 2013!!

    Thank you,

    Tony Miller

    LuvMuffin Software

    Ruckersville, VA

  • Jari Laine 12-MAR-13 07.06.57.000000 PM

    Hi Martin,

    Thank you.

    Regards, Jari

  • Martin Giffy D'Souza 12-MAR-13 03.45.18.000000 PM
    Very good idea!