Email Jasper Report in Oracle Apex (Attachment email)
Download Apache Tomcat 7 from the tomcat website
https://tomcat.apache.org/
Installation & Configuration of Apache Tomcat
The first step is to create the ACL and define the privileges for it:
The general syntax is as follows:
Conn sys as sysdba
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',
description => 'WWW ACL',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',
principal => 'SCOTT',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',
host => 'localhost', --Apache Tomcat URL
lower_port => 8080, --Apache Tomcat port
upper_port => 8080); --Apache Tomcat port
END;
COMMIT;
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl => 'www.xml',
description => 'WWW ACL',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'www.xml',
principal => 'SCOTT',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'www.xml',
host => 'smtp.mycompany.com'); --- mail server address
END;
COMMIT;
grant execute on sys.utl_http to scott;
Download JasperReportsIntegration File from the link below
http://www.opal-consulting.de/downloads/free_tools/JasperReportsIntegration/2.4.0/
Now copy the library file ojdbc6, orai18n from the directory C:\JasperReportsIntegration\lib & paste into
the directory C:\Program Files\Apache Software Foundation\Tomcat 7.0\lib
Now copy the JasperReportsIntegration.war file from the directory C:\JasperReportsIntegration-2.4.0.0\webapp\JasperReportsIntegration.war and paste in the directory C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps
After starting the service of Apache Tomcat you will see there is a new folder JasperReportsIntegration has
been created under the directory C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps
Now edit the application file under the directory C:\Program Files\Apache Software Foundation\Tomcat
7.0\webapps\JasperReportsIntegration\WEB-INF\conf
Open the application file with any editor like notepad/notepad++/word pad etc. in the content you will find
edit application file
[datasource:default]
type=jdbc
name=default
url=jdbc:oracle:thin:@localhost:1521:Oracle
username=SCOTT -- Database user
password=tiger
In the URL text : localhost = the host name, 1521 = Database port, Oracle = Oracle DB Service Name
open application file and make changes
After editing the application file, do not forget to save the content.
Now open a browser window and type the link http://localhost:8080/manager (from remote/local
machine) or http://localhost:8080/manager (from local machine)
You will see the home page of Apache Tomcat and the /JasperReportsIntegration has been added in the
application list and the running status is showing true.
Now click the /JasperReportsIntegration link and you will be redirected to the sample
Now click the show report button (Senden), you will see the PDF report
Conn scott/tiger
Create Function
CREATE OR REPLACE FUNCTION load_binary_from_url (p_url IN VARCHAR2)
RETURN BLOB
AS
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_blob BLOB;
l_raw RAW(32767);
BEGIN
-- Initialize the BLOB.
DBMS_LOB.createtemporary(l_blob, FALSE);
-- Make a HTTP request and get the response.
l_http_request := UTL_HTTP.begin_request(p_url);
l_http_response := UTL_HTTP.get_response(l_http_request);
-- Copy the response into the BLOB.
BEGIN
LOOP
UTL_HTTP.read_raw(l_http_response, l_raw, 32767);
DBMS_LOB.writeappend (l_blob, UTL_RAW.length(l_raw), l_raw);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;
RETURN l_blob;
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response(l_http_response);
DBMS_LOB.freetemporary(l_blob);
RAISE;
END load_binary_from_url;
Create Attachment email procedure
CREATE OR REPLACE PROCEDURE attach_mail
(p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_text_msg IN VARCHAR2 DEFAULT NULL,
p_attach_name IN VARCHAR2 DEFAULT NULL,
p_attach_mime IN VARCHAR2 DEFAULT NULL,
p_attach_blob IN BLOB DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_text_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_text_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
IF p_attach_name IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP
UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));
END LOOP;
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
Create Ireport with parameter and generate jasper file
Now Copy JASPER FILE and paste in the directory C:\Program Files\Apache Software Foundation\Tomcat
7.0\webapps\JasperReportsIntegration\WEB-INF\reports
send attachment email using plsql code
DECLARE
l_blob BLOB;
l_name varchar2(30);
RECIPIENTS_V VARCHAR2 (200);
BEGIN
l_blob:= load_binary_from_url('http://localhost:8080/JasperReportsIntegration/report?_repName=test&_repFormat=pdf&_dataSource=default¶meter='||:P_PARAMETER);
l_name:= 'test - '||to_date(sysdate)||'.pdf';
attach_mail(
p_to => '',
p_from => '',
p_subject => 'Report',
p_text_msg => 'This is a test message.',
p_attach_name => l_name,
p_attach_mime => 'application/pdf',
p_attach_blob => l_blob,
p_smtp_host => 'mail.com' --
);
END;