Skip to Main Content

Oracle Forms

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

How to use event driven report facility to send email from a stored procedure

myluismMar 8 2024

Hi All.

I have to post this on Forms forums because I never get any reply from Reports one.

I'm using WLS Forms and Reports 12.2.1.4 and I will have a stored procedure running through a schedule (DBMS_SCHEDULER). If a certain condition is meet I need to send a notification mail . My report server is already configured to send mails and we can send reports with destination mail with attachment (actual report output) using RUN_REPORT_OBJECT.

I have already tested the event driven report sending the report to DESTYPE=FILLE. This is ok. It is working, but now I need to send a notification email (no attachment).

This is the code I'm using:

create or replace procedure event_driven_report_mail as
myPlist SRW_PARAMLIST;
myIdent SRW.Job_Ident;
v_email_from varchar2(100):='sender@abc.com';
v_email_to varchar2(100) :='someuser@gmail.com';
v_email_cc varchar2(100) :='additional_user@yahoo.com';
v_email_subject varchar2(100) := 'Prueba de correo desde la BD hacia el WLS_REPORT server 12c';
v_traza varchar2(100);
BEGIN
myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
v_traza :='Paso 1';
srw.add_parameter(myPlist,'GATEWAY','http://MyReportServer:9002/reports/rwservlet');
v_traza :='Paso 2';
srw.add_parameter(myPlist,'SERVER','rep_wls_reports_myreportservername');
v_traza :='Paso 3';
srw.add_parameter(myPlist,'USERID','user/passwd@service_name');
v_traza :='Paso 4';
srw.add_parameter(myPlist,'DESTYPE','mail');
v_traza :='Paso 5';
srw.add_parameter(myPlist,'REPORT_DESFORMAT','html');
v_traza :='Paso 6';
srw.add_parameter(myPlist,'REPORT_DESNAME',v_email_to);
v_traza :='Paso 6';
srw.add_parameter(myPlist, 'FROM', v_email_from);
v_traza :='Paso 7';
srw.add_parameter(myPlist, 'CC', v_email_cc);
v_traza :='Paso 8';
srw.add_parameter(myPlist, 'SUBJECT', v_email_subject);
v_traza :='Paso 9';
srw.add_parameter(myPlist, 'MESSAGE', 'Event driven report from stored procedure in Database');
srw.add_parameter(myPlist,'REPORT','/home/app/prod/prueba.rep'); – added afterward
myIdent := srw.run_report(myPlist);
v_traza :='Paso 10';
EXCEPTION
when others then
raise_application_error(-20100,' Error en: '||v_traza ||' '||substr(sqlerrm,1,250));
END;

When I initial run the report error from report server queue is:

REP-50004: No se ha especificado ningún informe en la línea de comandos..

This is because I did not specify a particular report to run.

So I added this line:

srw.add_parameter(myPlist,'REPORT','/home/app/prod/prueba.rep');

This time reports runs but fails because I'm not providing a distribution list.

Error from report server queue is:

El informe se ha generado correctamente, pero la distribución a los siguientes destinos ha fallado.

So in short my question is this:

How do i send a report with destination mail that will work as a notification for mail users?. That is, I just need a mail with subject and content body:

srw.add_parameter(myPlist, 'MESSAGE', 'Event driven report from stored procedure in Database')

Is message a proper parameter for the content of mail?

Do i need to create a letter sytle report to acomplish this and just run that report?

Is a distribution list always required? and if so, can it be built on the fly from a stored procedure in the database?

Hope this is clear!

I appreciate any hint, feedback or example.

This post has been answered by Matej D. on Mar 13 2024
Jump to Answer
Comments
Post Details
Added on Mar 8 2024
16 comments
274 views