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&parameter='||: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;