Wednesday, September 15, 2010

APEX IR: Subscriptions with Bind Variables and VPD

Starting in APEX 4.0, Interactive Reports (IR) now have the ability for users to automatically get reports emailed to them. This is a great feature to allow end users to subscribe to data extracts rather than developers writing custom code.

To enable subscriptions select the Subscription option in the IR Report Attributes page:


Users can subscribe to email notifications by selecting the Subscription option in the Actions menu



Before implementing subscriptions they're some things that you should be aware of.

If your query uses bind variables, they will not be bound in the emailed reports. For example, supposed you had a select list of departments, P1_DEPTNO. If you then had an IR that listed all the employees in the department it would look like:


SELECT *
FROM emp
WHERE deptno = :p1_deptno

When a user is viewing the report in the application they'll get some employees for each department. If they subscribe to this IR then they'll get no rows returned as no value is bound for :P1_DEPTNO.

If you use the VPD feature in Oracle (Shared Components > Security Attributes > Virtual Private Database) it doesn't appear to be fired before the query is run as part of the subscription. I tested this by calling a log procedure in the VPD section. When the report is generated for an email subscription no log was registered in my log table.

I wouldn't classify either of these cases as bugs, however it's important for you to know what subscriptions can and can't handle before leveraging the subscriptions feature in a production application.

15 comments:

  1. Not sure I agree about the page items not being bound for subscriptions. I think Ape should substitute the page items for the current values of the page items when creating the subscription.

    I can't think of a reason why you would leave them there for subscriptions, given that the report will never work!

    ReplyDelete
  2. Hi Rob,

    I agree that their should be a way/option to remember the values for the bind variables.

    Instead of substituting the values APEX would need to keep a list of bind variables and their values for security purposes.

    This is a new feature that was released with 4.0 and I'm sure we'll see some enhancements in the upcoming releases.

    Martin

    ReplyDelete
  3. Hi Martin,
    I imported my application through database on production environment as APEX development instance is not available on production. Upon importing I obeserved that "search bar" in all the Interactive reports is not functioning.
    I cant select more number of rows, if i press go button writing some criteria in serach bar-nothing happens, cant scroll down wheel for options like select columns, filters, highlighter etc. The environment from where this script was exported, everything works fine in it. I dont know where to look out to resolve this issue as I dont have APEX development instance at prod.
    Please suggest.

    Thanks,
    Trupti

    ReplyDelete
  4. Hi Trupti,

    Did you check that all your JS files are being loaded properly?

    Martin

    ReplyDelete
  5. Hi Martin,

    I sent three sql files to DBA to load as I am not authorised to run scripts on production.
    1) Applicaton file
    2) Application image file
    3) Static files.
    I use apex version 3.2.1.00.12

    Have I missed anything???

    Regards,
    Trupti

    ReplyDelete
  6. Hi Martin,

    I have tried using the subscription feature to get email sent but nothing seems to happen. The rest of the IR functionality (filter, slect columns etc) works fine.

    Is there a PL/SQL process that I need to add to the page processes to get it working?

    Thanks

    ReplyDelete
  7. Hi Chris,

    There could be a number of reasons for this. Can you send an email using the apex_mail package? If you're having difficulties with that then the issue is not with the subscription feature but something else (could be numerous things).

    If you can't send an email using the package and you're using 11g you may want to ensure that your network ACLs are configured properly. Joel Kallman has an excellent post on this: http://joelkallman.blogspot.com/2010/10/application-express-network-acls-and.html

    ReplyDelete
  8. Martin,
    Not sure if you deal with different environments (development/production,etc.) Have you had any issues with subscriptions when importing/exporting applications between different environments?
    Thank you,
    Joshua

    ReplyDelete
  9. Hi Joshua,

    I haven't encountered that particular issue but have some links that may help.

    First read this post by David Peak: http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html It explains what happens to IRs when you update an application.

    This post by Joel Kallman may be able to resolve your issue: http://joelkallman.blogspot.com/2010/07/where-did-my-saved-interactive-reports.html

    - Martin

    ReplyDelete
  10. Hi all,

    How can we send in CSV format, not HTML? Is any hack or tips to do that?

    Thank you,

    ATK

    ReplyDelete
  11. Hi ATK,

    Unfortunately only the HTML option is available. If you wanted a CSV option you'd need to build that yourself.

    Martin

    ReplyDelete
  12. Once I read that Apex best practice said to make views and show them in IR (for security reason ... data could relay on different schema) normally that includes many Apex binding and variables.

    So this subscription without them is useless from mine point of view, regardless in which point is executed.

    For real practice using, this should be placed somehow else...

    ReplyDelete
  13. Is there any workaround for Interactive report subscription with bind variables?

    ReplyDelete
    Replies
    1. I don't think so. Worst case you may need to write your own wrapper functions.

      Delete
    2. Thank you for your response.

      Delete