SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Generate a Excel file and then send it as attachment using PL/SQL

Zafar IqbalApr 10 2013 — edited Jun 12 2013

Dear All,

I have a requirement to send email with attachment using PL/SQL procedure of 10g database. We will be querying data from database, generating excel(csv) file and attaching to email. How can I achieve this goal.
I have a procedure that will send email as HTML but it has limitations of sending 32K data.

CREATE OR REPLACE procedure send_email(
    p_to            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in varchar2 default null,
    p_html          in varchar2 default null,
    p_smtp_hostname in varchar2,
    p_smtp_portnum  in varchar2)
is
    l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1';
    l_connection    utl_smtp.connection;
    l_body_html     clob := empty_clob;  --This LOB will be the email message
    l_offset        number;
    l_ammount       number;
    l_temp          varchar2(32767) default null;
begin
    l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
    utl_smtp.helo( l_connection, p_smtp_hostname );
    utl_smtp.mail( l_connection, p_from );
    utl_smtp.rcpt( l_connection, p_to );

    l_temp := l_temp || 'MIME-Version: 1.0' ||  chr(13) || chr(10);
    l_temp := l_temp || 'Date: ' || to_char( sysdate, 'dd Mon yy hh24:mi:ss' ) || chr(13) || chr(10);
    l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
    l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
    l_temp := l_temp || 'Reply-To: ' || p_from ||  chr(13) || chr(10);
    l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' || 
                         chr(34) || l_boundary ||  chr(34) || chr(13) || 
                         chr(10);

    ----------------------------------------------------
    -- Write the headers
    dbms_lob.createtemporary( l_body_html, false, 10 );
    dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


    ----------------------------------------------------
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp   := '--' || l_boundary || chr(13)||chr(10);
    l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' || 
                  chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    ----------------------------------------------------
    -- Write the plain text portion of the email
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

    ----------------------------------------------------
    -- Write the HTML boundary
    l_temp   := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary || 
                    chr(13) || chr(10);
    l_temp   := l_temp || 'content-type: text/html;' || 
                   chr(13) || chr(10) || chr(13) || chr(10);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    ----------------------------------------------------
    -- Write the HTML portion of the message
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

    ----------------------------------------------------
    -- Write the final html boundary
    l_temp   := chr(13) || chr(10) || '--' ||  l_boundary || '--' || chr(13);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


    ----------------------------------------------------
    -- Send the email in 1900 byte chunks to UTL_SMTP
    l_offset  := 1;
    l_ammount := 1900;
    utl_smtp.open_data(l_connection);
    while l_offset < dbms_lob.getlength(l_body_html) loop
        utl_smtp.write_data(l_connection,
                            dbms_lob.substr(l_body_html,l_ammount,l_offset));
        l_offset  := l_offset + l_ammount ;
        l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
    end loop;
    utl_smtp.close_data(l_connection);
    utl_smtp.quit( l_connection );
    dbms_lob.freetemporary(l_body_html);
end;
/

Database Version:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Edited by: Zafar Iqbal on Apr 10, 2013 2:28 PM

Zlatko Sirotic

Try this:

"jdbc:oracle:thin:jts_db_user/*******@gpdv6.sandia.gov:1525:gpdv6..world"

:gpdv6..world instead of /gpdv6..world

Regards,

Zlatko

beckyjmcd

That appears to be the SID style connection. I just tried that but I am still getting the "Invalid Oracle URL specified". Interestingly, if I use the ojdbc7.jar file instead of ojdbc8.jar, the SID and Service URLs both works fine. It appears to be an issue with ojdbc8.jar but all of the documentation indicates you can pass username/password on the URL.

beckyjmcd

I wrote this test code to debug a jdbc connection issue I'm having with a COTS application and that COTS app requires jdbc8.jar.

Zlatko Sirotic

I tried to work with two database versions (11.2.0.4 and 12.1.0.2) and with three versions of ojdbc.jar (ojdbc7.jar, ojdbc8.jar / 12.2, ojdbc8.jar / 18.3).

In all six combinations getConnection(url) works well, with both variants (:SID and /SERVICE_NAME).

I use Java SE 8u202.

Regards,

Zlatko

Gaz in Oz

One issue will be you have 2 dots in your database string, between gpdv6 and world:

String url = "jdbc:oracle:thin:jts_db_user/********@gpdv6.sandia.gov:1525/gpdv6..world";

// ^

Use a single dot.

beckyjmcd

The service name has two dots. I tried removing one of the dots but I am still getting:

SQLSTate = "99999"

vendorCode = 17067

detailMessage = "Invalid Oracle URL specified"

cause = (SQLException@1307) "java.sql.SQLException:Invalid Oracle URL specified"

The weird thing is that the 2 dot syntax works fine with ojdbc7.jar.

This is against an Oracle 11.2.04 database but we tried it with a 12.2 database and both give the same error.

It's almost like we have something configured incorrectly at the database level. My DBA has submitted an Oracle Service Request to help troubleshoot.

beckyjmcd

It's like we have some sort of database configuration issue because I cannot get ojdbc8.jar to work with 11.2.04 or 12.2 but the ojdbc7.jar works fine. My DBA has submitted an Oracle Service Request to help troubleshoot the issue.

beckyjmcd

This is the configuration for the Oracle listener:

LISTENER11_gpdv6 =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = gpdv6.world))

(ADDRESS = (PROTOCOL = IPC)(KEY = gpdv6))

(ADDRESS = (PROTOCOL = TCP)(Host = gpdv6)(Port = 1525))

)

PASSWORDS_LISTENER11_gpdv6 = (xxxxxxxxxxxxx)

VALID_NODE_CHECKING_REGISTRATION_LISTENER11_gpdv6 = ON

SID_LIST_LISTENER11_gpdv6 =

(SID_LIST =

(SID_DESC =

(SID_NAME = gpdv6)

(GLOBAL_DBNAME = gpdv6..world)

)

Gaz in Oz

The service name has two dots.

Indeed, 2 dot service names seem to be acceptable and work, as you have mentioned.

Setting up a 2 dot service_name for my 11.2.0.2.0 XE db by adding "xe..world" in listener.ora and compiling below java code with

ojdbc8.jar - Oracle JDBC driver: Release 12.2.0.1.0 from instantclient_12_2 and using JDK 1.8.0_144, works.

import java.sql.*;
import java.io.*;
import java.util.*;
public class conn {
   public static void main(String[] argv) throws SQLException, ClassNotFoundException {
      try {
         Class.forName("oracle.jdbc.driver.OracleDriver");
         String url = "jdbc:oracle:thin:gaz/gaz@localhost:1521/xe..world";
         System.out.println("Connect String: " + url);
         Connection conn = DriverManager.getConnection(url);
         System.out.println("Connected.");
         conn.close();
         conn = null;
      } catch(SQLException e) {
         // e.printStackTrace();
         System.out.println("ERROR: " + e.getMessage());
      }
   }
}

F:\java>javac conn.java

F:\java>java conn

Connect String: jdbc:oracle:thin:gaz/gaz@localhost:1521/xe..world

Connected.

F:\java>

beckyjmcd

I have had other people say they are able to get the exact same Java code to connect with ojdbc8.jar so I'm thinking there must be an issue on the Oracle server side (maybe a configuration setting) that is causing this not to work.

My DBA has opened a support case with Oracle technical support. Still waiting for a resolution. This is very odd.

1 - 10

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 10 2013

Added on Apr 10 2013

#pl-sql, #plsql

21 comments

23,076 views

SQL & PL/SQL

Generate a Excel file and then send it as attachment using PL/SQL

Comments

Post Details