Sending mail by PL Sql in oracle

A stored procedure, to send mail can be as:

We are using a new type in the SP for containing the CC mail list. For this purpose, we can do as:


CREATE OR REPLACE TYPE mail_ccs AS TABLE OF VARCHAR2;

SP Definition:

CREATE OR REPLACE PROCEDURE sendMail(
    username       IN VARCHAR2,
    password       IN VARCHAR2,
    smtpHost       IN VARCHAR2,
    smtpPort       IN PLS_INTEGER DEFAULT 25,
    mailFrom       IN VARCHAR2,
    rcptTo         IN VARCHAR2,
    ccs            IN mail_ccs,
    messageSubject IN VARCHAR2,
    messageBody    IN VARCHAR2)
IS
  l_conn UTL_SMTP.connection;
  l_ccs              VARCHAR2(2000);
  l_encoded_username VARCHAR2(200);
  l_encoded_password VARCHAR2(200);
BEGIN
  --open connection
  /*
  l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);
  UTL_SMTP.helo(l_conn, smtpHost);
  */
  --In case of authentication needed
  l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));
  l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));
  l_conn             := UTL_SMTP.open_connection(smtpHost, smtpPort);
  UTL_SMTP.ehlo(l_conn, smtpHost);--DO NOT USE HELO
  UTL_SMTP.command(l_conn, 'AUTH', 'LOGIN');
  UTL_SMTP.command(l_conn, l_encoded_username);
  UTL_SMTP.command(l_conn, l_encoded_password);
  --prepare headers
  UTL_SMTP.mail(l_conn, mailFrom);
  UTL_SMTP.rcpt(l_conn, rcptTo);
  /*if we have multiple recipients or CCs, we must call UTL_SMTP.rcpt once for each one
  however, we shall specify that there are CCs in the mail header in order for them to appear as such*/
  IF ccs IS NOT NULL THEN
    FOR i IN ccs.FIRST..ccs.LAST
    LOOP
      UTL_SMTP.rcpt(l_conn, ccs(i));--add recipient
      l_ccs:=l_ccs||ccs(i)||',';    --mark as CC
    END LOOP;
    --now remove the trailing comma at the end of l_ccs
    l_ccs:=SUBSTR(l_ccs,0,LENGTH(l_ccs)-1 );
  END IF;
  --start multi line message
  UTL_SMTP.open_data(l_conn);
  --prepare mail header
  /*DO NOT USE MON instead of MM in the date pattern if you run the script on machines with different locales as it will be misunderstood
  and the mail date will appear as 01/01/1970*/
  UTL_SMTP.write_data(l_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'To: ' || rcptTo || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Cc: ' || l_ccs || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'From: ' || mailFrom || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Subject: ' || messageSubject || UTL_TCP.crlf || UTL_TCP.crlf);
  --include the message body
  UTL_SMTP.write_data(l_conn, messageBody || UTL_TCP.crlf || UTL_TCP.crlf);
  --send the email
  UTL_SMTP.close_data(l_conn);
  UTL_SMTP.quit(l_conn);
END; 



To use the above SP, we can execute that by calling below code:

DECLARE
  USERNAME VARCHAR2(200);
  PASSWORD VARCHAR2(200);
  SMTPHOST VARCHAR2(200);
  SMTPPORT BINARY_INTEGER;
  MAILFROM VARCHAR2(200);
  RCPTTO VARCHAR2(200);
  CCS PAUL.MAIL_CCS;
  MESSAGESUBJECT VARCHAR2(200);
  MESSAGEBODY VARCHAR2(200);
BEGIN
  USERNAME := 'paul.pronabananda';
  PASSWORD := 'paulpassword';
  SMTPHOST := '172.20.1.1';
  SMTPPORT := 50;
  MAILFROM := 'paul.pronabananda';
  RCPTTO := 'paul.pronabananda@gmail.com';
  -- Modify the code to initialize the variable
  -- CCS := NULL;
  MESSAGESUBJECT := 'Test';
  MESSAGEBODY := 'Test Body';

  SENDMAIL(
    USERNAME => USERNAME,
    PASSWORD => PASSWORD,
    SMTPHOST => SMTPHOST,
    SMTPPORT => SMTPPORT,
    MAILFROM => MAILFROM,
    RCPTTO => RCPTTO,
    CCS => CCS,
    MESSAGESUBJECT => MESSAGESUBJECT,
    MESSAGEBODY => MESSAGEBODY
  );
--rollback; 

END;

If mail server address is not already added in the acl list. in that case we will have to add that info in the acl:

If ACL not exists, at first we need to create ACL by below code:

BEGIN 

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( 

acl => 'utlpkg.xml', 

description => 'created by paul', 

principal => 'PAUL', 

is_grant => TRUE, 

privilege => 'connect', 

start_date => null, 

end_date => null); 

END;

After creation, we are in need to add the host IP and port in that ACL using below:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => 'utlpkg.xml',

    host        => '172.20.1.1', 

    lower_port  => 1,

    upper_port  => 100);   

  COMMIT;

END;

--

Disk Consumption with Average Incremental Value

SELECT b.tsname tablespace_name ,
  MAX(b.used_size_mb) cur_used_size_mb ,
  ROUND(AVG(inc_used_size_mb),2)avg_increas_mb
FROM
  (SELECT a.days,
    a.tsname ,
    used_size_mb ,
    used_size_mb - LAG (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
  FROM
    (SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days ,
      ts.tsname ,
      MAX(ROUND((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
    FROM DBA_HIST_TBSPC_SPACE_USAGE tsu ,
      DBA_HIST_TABLESPACE_STAT ts ,
      DBA_HIST_SNAPSHOT sp,
      DBA_TABLESPACES dt
    WHERE tsu.tablespace_id    = ts.ts#
    AND tsu.snap_id            = sp.snap_id
    AND ts.tsname              = dt.tablespace_name
    AND sp.begin_interval_time > sysdate-7
    GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'),
      ts.tsname
    ORDER BY ts.tsname,
      days
    ) a
  ) b
GROUP BY b.tsname
ORDER BY b.tsname;

Java Code to send SMS through SMPP

Java Code to send sms using smpp:

import java.io.IOException;
import org.jsmpp.InvalidResponseException;
import org.jsmpp.PDUException;
import org.jsmpp.bean.Alphabet;
import org.jsmpp.bean.BindType;
import org.jsmpp.bean.ESMClass;
import org.jsmpp.bean.GeneralDataCoding;
import org.jsmpp.bean.MessageClass;
import org.jsmpp.bean.NumberingPlanIndicator;
import org.jsmpp.bean.RegisteredDelivery;
import org.jsmpp.bean.SMSCDeliveryReceipt;
import org.jsmpp.bean.TypeOfNumber;
import org.jsmpp.extra.NegativeResponseException;
import org.jsmpp.extra.ResponseTimeoutException;
import org.jsmpp.extra.SessionState;
import org.jsmpp.session.BindParameter;
import org.jsmpp.session.SMPPSession;

/**
 *
 * @author paul.pronabananda
 */
public class SMS {

    private String serverAddress = "xxx.xx.xxx.xx"; (ip address of smpp server)
    private int port = xxxx; (port no to connect)
    private String systemID = "paul";  (user id to establish connection)
    private String systemType = "paul";
    private String password = "paul";
    private TypeOfNumber sourceTON = TypeOfNumber.valueOf((byte) 5);
    private TypeOfNumber destinationTON = TypeOfNumber.valueOf((byte) 1);
    private NumberingPlanIndicator sourceNPI;
    private NumberingPlanIndicator destinationNPI;
//    private String fromNumber = "01732444471";
    private String prefixToNumber = "88";
//    private String toNumber = "01553466667";// private TimeFormatter tF = new AbsoluteTimeFormatter();

    public void sendTextMessage(String MSISDN,String fromNumber,String msgBody) {
        sourceNPI = NumberingPlanIndicator.valueOf((byte) 9);
        destinationNPI = NumberingPlanIndicator.valueOf((byte) 1);

        BindParameter bindParam;
        bindParam = new BindParameter(BindType.BIND_TX, systemID, password, systemType, sourceTON, sourceNPI, null);
        SMPPSession smppSession = null;
        try {
            // smpp session is created using the bindparam and the smsc ip address/port
            smppSession = new SMPPSession();
            smppSession.unbindAndClose();
            smppSession.connectAndBind(serverAddress, port, bindParam);
        } catch (IOException e1) {
            System.err.println("Problem to create Session");
            e1.printStackTrace();
        }

      //  String msgBody = "This is a Test Message";

        String messageID = "";
        try {
            messageID = smppSession.submitShortMessage("CMT", sourceTON, sourceNPI, fromNumber,
                    destinationTON, destinationNPI, prefixToNumber + MSISDN,
                    new ESMClass(), (byte) 0, (byte) 0, null, null,
                    new RegisteredDelivery(SMSCDeliveryReceipt.DEFAULT), (byte) 1,
                    new GeneralDataCoding(false, true, MessageClass.CLASS1, Alphabet.ALPHA_DEFAULT), (byte) 0,
                    msgBody.getBytes());
        } catch (PDUException e) {
            //    log.error("Thread[" + nThreadNo + "] -> Failed [" + toNumber+"] : "+"Invalid PDU parameter",e);
            System.err.println("Invalid PDU parameter");
            e.printStackTrace();
        } catch (ResponseTimeoutException e) {
            //    log.error("Thread[" + nThreadNo + "] -> Failed [" + toNumber+"] : "+"Response timeout",e);
            System.err.println("Response timeout");
            e.printStackTrace();
        } catch (InvalidResponseException e) {
            //    log.error("Thread[" + nThreadNo + "] -> Failed [" + toNumber+"] : "+"Receive invalid respose",e);
            System.err.println("Receive invalid respose");
            e.printStackTrace();
        } catch (NegativeResponseException e) {
            //  log.error("Thread[" + nThreadNo + "] -> Failed [" + toNumber+"] : "+"Receive negative response",e);
            System.err.println("Receive negative response");
            e.printStackTrace();
        } catch (IOException e) {
            //   log.error("Thread[" + nThreadNo + "] -> Failed [" + toNumber+"] : "+"IO error occur",e);
            System.err.println("IO error occur");
            e.printStackTrace();
        } catch (Exception e) {
            //   log.error("Thread[" + nThreadNo + "] -> Failed [" + toNumber+"] : "+"Unknown error occur",e);
            System.err.println("Unknown error occur");
            e.printStackTrace();
        } finally {
            System.out.print(messageID);
        }

    }
}

servlet to export information in excel file

We can use servlet to export information in excel format. The code for this purpose may be as below:

String folder = "/excels/";

Three parameters have been sent using request as per requirement.fileNameToShow contains the value for the name of the file to save in client side

String fileNameToShow=request.getParameter("fileName");
String dbName=request.getParameter("dbName");
String query=request.getParameter("query");
String file_name = folder;

System.out.println("Report Name: " + file_name);
try {


ServletOutputStream servletOutputStream = response.getOutputStream();
byte[] bytes = null;


try {
ExcelHelper exlHelper = new ExcelHelper();
bytes = exlHelper.createExcell(getServletConfig().getServletContext().getRealPath(file_name), query, dbName);
response.setHeader("Content-Disposition", "inline;filename="+fileNameToShow+".xls");
response.setContentType("application/vnd.ms-excel");
response.setContentLength(bytes.length);
servletOutputStream.write(bytes, 0, bytes.length);

servletOutputStream.flush();
servletOutputStream.close();
mailMessage = mailMessage + ". REPORT produced successfully";
} catch (Exception e) {
// display stack trace in the browser
logger.error(e);
StringWriter stringWriter = new StringWriter();
PrintWriter printWriter = new PrintWriter(stringWriter);
e.printStackTrace(printWriter);
response.setContentType("text/plain");
response.getOutputStream().print("Could not display report temporarily...");
//response.getOutputStream().print(stringWriter.toString());
mailMessage = mailMessage + ".ERROR catched when creating report.";
}
} catch (Exception e) {
System.out.println(e.getMessage());
logger.error(e);
} finally {
logger.info(mailMessage);
}

Expecting your kind suggestion for moderation of the code.

to create excel file using java

Apache POI API has been used for this purpose. This is used for manipulating various microsoft files.

This is a function of a class which generate an excel file after executing query in the database. Executing query in the database is not concerned here. This code insists on the creating file using POI. The function return the file in byte array( i do it as one of my requirement).

public byte[] createExcell(String fileLoc, String query, String dbName) {

Comments:
fileLoc: provide the location where to keep the file after generation.
query: database query on which you want to create excel
dbname: if you work with multiple database in your project

try {
String file_name = fileLoc +"/"+ (new Date()).getTime() + ".xls";
FileOutputStream fileOut = new FileOutputStream(file_name);
File file = new File(file_name);
if (file.exists()) {
file.delete();
}

delete file if a file name generated before with same name.

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("Candidate List");
byte[] bytes;

GatewayPersonalInfo gwPersonalInfo = new GatewayPersonalInfo();
List lstPerson;
lstPerson = gwPersonalInfo.getAllPersonalInfo(query, dbName);

Above three lines has been used to import data from databases in list.

HSSFRow rowNew;
HSSFCell cellA, cellB, cellC, cellD, cellE;
rowNew = worksheet.createRow(0);
cellA = rowNew.createCell(0);
cellA.setCellValue("Sl. No");
cellB = rowNew.createCell(1);
cellB.setCellValue("App Serial No");
cellC = rowNew.createCell(2);
cellC.setCellValue("Full name");
cellD = rowNew.createCell(3);
cellD.setCellValue("Contact No.");
cellE = rowNew.createCell(4);
cellE.setCellValue("Email ID");

Just to create header in the file

if (lstPerson != null) {
if (!lstPerson.isEmpty()) {
Iterator itr = lstPerson.iterator();
PersonalInfo person;
int rowno = 1;
while (itr.hasNext()) {

person = (PersonalInfo) itr.next();
rowNew = worksheet.createRow(rowno);
cellA = rowNew.createCell(0);
cellA.setCellValue(rowno);
cellB = rowNew.createCell(1);
cellB.setCellValue(person.getSerialNo());
cellC = rowNew.createCell(2);
cellC.setCellValue(person.getFullName());
cellD = rowNew.createCell(3);
cellD.setCellValue(person.getContactNo());
cellE = rowNew.createCell(4);
cellE.setCellValue(person.getEmail());
System.out.println(rowno + " row added.");
rowno = rowno + 1;
}
}
}
workbook.write(fileOut);

fileOut.flush();
fileOut.close();

FileInputStream fin = new FileInputStream(file);
bytes = new byte[(int) file.length()];
fin.read(bytes);
file.delete();

After taking in the byte array, file is being deleted. But it depends on your requirements. You can keep the file.

return bytes;

} catch (Exception ex) {
System.out.println(ex);
logger.error(ex);
} finally {
}
return null;
}

Kindly provide your suggestion for further modification.

Servlet to export jasper report in excel

Its very important issue to export jasper report to excel format. This can be done through a servlet in JSP application. The servlet code may be as bellow:

In tis case I have used some code as part of many times edit. It can be summarize.


This servlet is able to handle different no. of parameters and different reports.

We just need to call this servlet as below:

link = "/JRExcelExport?rname=candidateList&pname=posiCode=" + posiCode + "@qValue1=" + qValue1 + "@qValue2=" + qValue2 ;
RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(link);
dispatcher.forward(request, response);

The value of rname is the name of the report. And all the parameters would be attached after the pname

package dbbl.paul.recruitment.servlets;

import dbbl.paul.recruitment.dal.dbconnector.ConnectionHandler;
import java.io.*;
import java.sql.Connection;
import java.util.HashMap;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.jasperreports.engine.*;

import net.sf.jasperreports.engine.export.JExcelApiExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;
import org.apache.log4j.Logger;


public class JRExcelExport extends HttpServlet {

final static Logger logger = Logger.getLogger(ReportManager.class);

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
logger.trace("Trying to Generate report:processRequest");

String mailMessage = "";
String folder = "/reports/";
JasperPrint jasperPrint = null;

String rpt_name = request.getParameter("rname");
String report_name = rpt_name + ".jasper";
String file_name = folder + report_name;

mailMessage = "RPTNAME=" + rpt_name;
System.out.println("Report Name: " + file_name);
try {
HashMap hm = new HashMap();
String param_name = request.getParameter("pname");
System.out.println(param_name);

if (param_name != null) {

if (!param_name.equals("")) {
String split_param[] = param_name.split("@");
String tmp_param[];
for (int i = 0; i < split_param.length; i++) { System.out.println(split_param); tmp_param = split_param[i].split("="); hm.put(tmp_param[0], tmp_param[1].replaceAll("NULL", "")); } } mailMessage = mailMessage + ". PARAMETERS collected successfully"; } ServletOutputStream servletOutputStream = response.getOutputStream(); File reportFile = new File(getServletConfig().getServletContext().getRealPath(file_name)); Connection con = null; try { con = ConnectionHandler.getConnection_OnlineJob(); jasperPrint = JasperFillManager.fillReport(reportFile.getPath(), hm, con); generateXLSOutput(rpt_name, jasperPrint, response); } catch (JRException e) { // display stack trace in the browser logger.error(e); StringWriter stringWriter = new StringWriter(); PrintWriter printWriter = new PrintWriter(stringWriter); e.printStackTrace(printWriter); response.setContentType("text/plain"); response.getOutputStream().print("Could not display report temporarily..."); //response.getOutputStream().print(stringWriter.toString()); mailMessage = mailMessage + ".ERROR catched when creating report."; } } catch (Exception e) { System.out.println(e.getMessage()); logger.error(e); } finally { logger.info(mailMessage); } } private void generateXLSOutput(String reportname, JasperPrint jasperPrint, HttpServletResponse resp) { String reportfilename = reportname + ".xls"; try { JExcelApiExporter exporterXLS = new JExcelApiExporter(); exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint); exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE); exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE); exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE); exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, resp.getOutputStream()); resp.setHeader("Content-Disposition", "inline;filename=" + reportfilename); resp.setContentType("application/vnd.ms-excel"); exporterXLS.exportReport(); } catch (Exception ex) { logger.trace(ex); } } @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } @Override public String getServletInfo() { return "Short description"; }//
}