Skip to Main Content

Tuesday, March 12, 2013

Redirect user if APEX classic report not return rows

APEX Tips and Tricks

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.

Pagination

Comments

  • Jari Laine 23 Jan 2014

    Hi Sriram,

    Sample application is not available for download. Sorry.

    Regards,
    Jari

  • Sriram Iyer 22 Jan 2014

    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 2013

    Hi Tony,

    Thank you.

    Unfortunately, I'm not able to come Kscope13 sad

    I hope we meet again some other time.

    Regards,
    Jari

  • tony miller 25 May 2013

    Great Solution!!

    Hopefully we will meet again @ Kscope 2013!!

    Thank you,

    Tony Miller

    LuvMuffin Software

    Ruckersville, VA

  • Jari Laine 12 Mar 2013

    Hi Martin,

    Thank you.

    Regards, Jari

  • Martin Giffy D'Souza 12 Mar 2013
    Very good idea!