Oracle Send HTML Email

This procedure sends and HTML email from Oracle.  This is usable code and can be customized as per required:

PROMPT CREATE OR REPLACE PROCEDURE html_email
CREATE OR REPLACE PROCEDURE html_email
(
p_to IN VARCHAR2, --comma seperated list
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
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;
l_dest VARCHAR2(32767);
l_dest_tmp VARCHAR2(32767);
BEGIN
l_dest := p_to;
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);
WHILE instr(l_dest, ',') != 0 LOOP
l_dest_tmp := substr(l_dest, 1, instr(l_dest, ',') - 1);
l_dest := substr(l_dest, instr(l_dest, ',') + 1);
utl_smtp.rcpt(l_connection, l_dest_tmp);
END LOOP;
utl_smtp.rcpt(l_connection, l_dest);
l_temp := l_temp || 'MIME-Version: 1.0' || 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=windows-1252' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.WRITE(l_body_html, length(l_temp), l_offset, l_temp);
----------------------------------------------------
-- 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; charset=windows-1252' ||
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);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;


/
GRANT EXECUTE ON html_email TO PUBLIC;

Oracle Getting rowcount in same query which is used to fetch rows

This is small thing but handy when you need to get the total rows being fetched and actual data from the single query.
This might not be issue if the user retrieves all the records  by given query because user can simply iterate over the resultset and count the number of records being fetched. But this wont help you when you display only specific number of records in your query(ex: where rownum between 10 and 20).
Now this solution comes into role. Simply use following portion of code in your sql query that is used to fetch the record:

            ROW_NUMBER() OVER (ORDER BY [any_field] desc) row_num,
            Count(*) OVER() cnt

where any_field is the fieldname that is being fetched by the sql.

Your complete query should be something like this:

SELECT FIELD1,FIELD2..........FIELDN,
            ROW_NUMBER() OVER (ORDER BY fileid desc) row_num,
            Count(*) OVER() cnt
FROM MYTABLE
WHERE ROWNUM>=1 AND ROWNUM<=100.

Now the resultset returned has the column CNT that holds the total records satisfied by the above sql with where condition being excluded.

This type of query becomes handy when you need to implement pagination in your application.
The above query is a part of working sql in Oracle 11g.

Thanks to the url http://stackoverflow.com/questions/2905199/oracle-how-get-total-number-of-results-when-using-a-pagination-query which gives some idea on this..

Oracle Coherence: Providing Extreme Performance, Predictable Scalability, and Continuous Availability for Mission-Critical Java Applications

Organizations can gain significant advantage by managing and exploiting their

information systems more effectively than their competitors. But with today’s tight

budget constraints and technology limitations, many miss the opportunity—

sometimes with disastrous results. This white paper addresses how you can gain a

measurable competitive edge without compromise—today.

For further details have a look at the valuable doc:

http://www.docstoc.com/Docs/DownloadFile.ashx?docId=110978612

saveOrUpdate(Object) Hibernate: a different object with the same identifier value was already associated with the session:

this common problem in hibernate transaction can be solved by defining a new session ojbect for each transaction and commit or rollback the transaction in the DataAccess Object level.

Following code snippet in DAO level was used to solve the problem:

Transaction tx=null;
Session sess = SessionFactoryUtils.getNewSession(getSessionFactory());
try {
tx = sess.beginTransaction();
sess.saveOrUpdate(updatedModel);
tx.commit();
}
catch (JDBCException e) {
return false;
}
catch(Exception ex){
System.out.println("****************************exception sent"+ex.getMessage());
}
finally {
sess.close();
}

Thanks,
Ramesh.