Current Position:Home > PDF Report generation and email it from a DB trigger

PDF Report generation and email it from a DB trigger

Update:10-11Source: network consolidation
Advertisement
Dear all
Is it possible to run a report in PDF format ad email it to some clients after a specific envent through Database Trigger. For example whenever a client makes an entry into order entry table (through entry form), a trigger should execute on Orders table, this trigger should execute or generate a PDF formatted report and finally mail it to Sales team?
I'm using Oracle Database 10g. Rel.2 on Windows-XP.

The Best Answer

Advertisement
kamranpathan wrote:
Is it possible to run a report in PDF format ad email it to some clients after a specific envent through Database Trigger. No. Not the way you imagine.
A trigger is fired when? During the transaction. The transaction still is not committed and can be rolled back. So if you start doing notifications and what not in the trigger, and the transaction is rolled back, then that transaction never happened. But your notification code did. And the users have been informed incorrectly - about something that did not happen.
The same trigger can also be fired in the same transaction for the same row - more than once. This can happen in specific circumstances in Oracle, where basically Oracle itself does an undo of the transaction (trigger already fired) and then redo that transaction (trigger fire again).
So in such a case, your trigger will generate two notifications from the same trigger for the same event. Inside a transaction that still could be rolled back by the session owner.
The correct approach is not to perform the action in the trigger. It is to queue the action to be executed when the transaction is committed.
For example, the trigger is on the PRODUCTS table. When a new product is added, a notification needs to be send to customers that have selected to be informed of new product releases.
The first step is to write a package that performs this notification. The procedure that drives the notification processing, gets a product identifier as input and does the checks and notification.
After this package has been tested, it is implemented indirectly via a trigger on the PRODUCTS table. Instead of the trigger directly calling this package, the trigger needs to queue an action that will execute the notification package when the transaction is committed.
This can be done using DBMS_JOB. In the trigger, a job is submitted to execute that notification package for the current product ID. The job submission is part of the existing transaction. If the transaction is rolled back, so is the job and it is removed from the job queue. If the transaction is committed, the job is also committed to the job queue and executed.
  • PDF Report generation and email it from a DB trigger Update:10-11

    Dear all Is it possible to run a report in PDF format ad email it to some clients after a specific envent through Database Trigger. For example whenever a client makes an entry into order entry table (through entry form), a trigger should execute on

  • PDF reports generation problem from single oracle reports in 10G Update:11-30

    Hi All, i am generating 4 PDF reports from one Oracle reports in 10 G. i used RUN_REPORT_OBJECT built in 4 times with some different parameter list at a single time. some time it generates but some time it generate 2 or 3 out of 4. Can you anyone hel

  • Error only in PDF report generation not HTML Update:10-11

    The same report exports fine in HTML. This only happens when exporting a PDF report: com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: 26---- Error code:-2147467259 Error code name:failed The application was developed by our developer team

  • PDF report generation fails with v7.01 Update:10-11

    I have software that generates a PDF report. I upgraded FireFox to v7.01 and I now get an error that the printer is not activated even though I am saving the report to a disc file and not printing it. Any ideas or suggestions are appreciated.I just r

  • Problems with PDF report generation on WEB application Update:10-11

    Hi friends. I have the following problem. I heve many reports running on a client/server system. But we decide to move one by one, the menu options of the system to the WEB, using iAS. Most of the reports works just fine, but some of them works wrong

  • Bug in PDF Report generation? Update:10-11

    Hi everyone! We have a problem! We need to print user data, including an initial password, on paper, one user per page. In the report section there is a choice of number of users per page. However, if one(1) user per page is chosen, the PDF-generator

  • PDF Report Generation Update:11-30

    Hi, I would like to know how to generate reports in pdf format I use: EBS: Oracle 11.5.9 Database : 9i O/S : Solaris And I use Oracle Reports 6i versionHi; Please see: Oracle XML Publisher and Oracle Reports How To Generate And Print Reports In PDF F

  • Fully automated report generation and email Update:10-11

    I am trying to get a report (from a query) to run automatically every Tuesday morning. This is my job BEGIN      dbms_scheduler.create_job (           job_name          => 'GEN_XLS_RPT_AND_EMAIL',           job_type          => 'STORED_PROCEDURE',  

  • Query for report generation and email alert Update:10-11

    Hi, I do have a requirement in my project like the following. 1. We do have multiple interfaces sending request to one of the target Webservices 2. Receiving response back from Webservices 3. Also logging the same using log4J My requirement is to fil

  • Apex 4.1 PDF report generation Update:11-30

    On Apex 4.1 when I attempt to download a report in PDF mode, it appears that it downloads, but when I go to open it up I get an error that the file is of the wrong type or possibly corrupt. Upon opening the file in Text editor, there is an error Ora-