Oracle Procedure to send html email with multiple attachment [ blob]

create or replace TYPE attach_info IS OBJECT (
        value         VARCHAR2(100),
        mimetype      VARCHAR2(100),
        content       BLOB
    );
/

create or replace TYPE array_attachments IS TABLE OF attach_info;
/

PROCEDURE HTML_EMAIL_MUlTI_BLOBS (
/* TO SEND HTML OR PLAN TEXT EMAIL WITH ATTACHMENTS (BLOB)
-- TWO TYPES SHOULD BE IN DATABASE FOR THIS PROCEDURE WHICH ARE FOLLOWING:
--   create or replace TYPE attach_info IS OBJECT (
--        value         VARCHAR2(100),
--        mimetype      VARCHAR2(100),
--        content       BLOB
--    );
-- create or replace TYPE array_attachments IS TABLE OF attach_info;
*/
                                       p_to          IN VARCHAR2,
                                       p_cc          IN VARCHAR2 DEFAULT NULL,
                                       p_from        IN VARCHAR2,
                                       p_subject     IN VARCHAR2,
                                       p_text_msg    IN VARCHAR2 DEFAULT NULL,
                                       p_html_msg    IN VARCHAR2 DEFAULT NULL,
                                       attachments   IN OUT array_attachments,
                                       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);
  IF TRIM(p_cc) IS NOT NULL THEN
    UTL_SMTP.rcpt(l_mail_conn, p_cc);
  END IF;
  UTL_SMTP.open_data(l_mail_conn);

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  IF TRIM(p_cc) IS NOT NULL THEN    
      UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('CC: ' || REPLACE(p_cc, ',', ';') || UTL_TCP.crlf));
  END IF;
  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);

  -- check plain text message and set
  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;

 -- check html message and set
 IF p_html_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/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_html_msg);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
  END IF;

  -- Attachment Part
  IF attachments IS NOT NULL THEN
    FOR i IN attachments.FIRST .. attachments.LAST
    LOOP
    -- Attach info
        UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('--' || l_boundary || UTL_TCP.crlf));
        UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).mimetype
                            || ' name="'|| attachments(i).value || '"' || UTL_TCP.crlf));
        UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
        UTL_SMTP.write_raw_data(l_mail_conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'
                            || attachments(i).value || '"' || UTL_TCP.crlf || UTL_TCP.crlf));

    -- Attach body
      FOR j IN 0 .. TRUNC((DBMS_LOB.getlength(attachments(i).content) - 1 )/l_step) LOOP
        UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(attachments(i).content, l_step, j * l_step + 1))));
      END LOOP;

         UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
    END LOOP;
  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;

/


How to initializing attachments:

declare:
attachments         array_attachments;

assign:
attachments := array_attachments();



attachments.extend(2);

attachments(1):= attach_info(null,null,null); /*extend it and initialize attach_info object and only then assign attachments(1).value, attachments(1).mimetype, attachments(1).content https://community.oracle.com/message/10085562*/


      SELECT oa0.VALUE||'.'||oa19.value,
            mty.mimetype,
            o.CONTENT
        INTO attachments(1).value,
             attachments(1).mimetype,
             attachments(1).content
        FROM sdms.OBJECTS o
        JOIN sdms.OBJECTATTRIBUTES oa0 ON (o.ID = oa0.OBJECT_ID AND oa0.ATTRIBUTEDESCRIPTION_ID = 0) -- FileName
        JOIN sdms.OBJECTATTRIBUTES oa19 ON (o.ID = oa19.OBJECT_ID AND oa19.ATTRIBUTEDESCRIPTION_ID = 19), -- FileExtension
        sdms.t_mime_types mty
        WHERE o.mime_id=mty.id AND o.id=9124;


Calling procedure:

HTML_EMAIL_MUlTI_BLOBS(v_to, v_cc, v_from, v_subject, null, cMessage, attachments, v_smtp_host);

Configure Oracle 11g Database with JBPM 6.2.0

Configure Oracle 11g Database with JBPM 6.2.0

JBPM 6.2.0 comes with Default "h2" database.

To Configure JBPM 6.2.0 with Oracle 11g database, you have to configure following files:

• jbpm-installer\db\jbpm-persistence-JPA2.xml
• jbpm-installer\standalone-*.xml
• jbpm-installer\build.properties

The following files define the persistence settings for the jbpm-installer demo:

• jbpm-installer/db/jbpm-persistence-JPA2.xml
• Application server configuration
• standalone-*.xml

Do the following:

Disable default H2 database and enable oracle 11g database in "jbpm-installer\build.properties"

# default is H2
#H2.version=1.3.168
#db.name=h2
#db.driver.jar.name=h2-${H2.version}.jar
#db.driver.download.url=http://repo1.maven.org/maven2/com/h2database/h2/${H2.version}/h2-${H2.version}.jar
#oracle
db.name=oracle
db.driver.module.prefix=oracle/jdbc
db.driver.jar.name=ojdbc7-12.1.0.2.jar
db.driver.download.url=https://code.lds.org/nexus/content/groups/main-repo/com/oracle/ojdbc7/12.1.0.2/ojdbc7-12.1.0.2.jar

Change in following file for Hibernate sql.dailect 

• jbpm-installer\db\jbpm-persistence-JPA2.xml

This is the JPA persistence file that defines the persistence settings used by jBPM for both the process engine information, the logging/BAM information and task service.
In this file, you will have to change the name of the hibernate dialect used for your database.

The original line is:
<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
In the case of an Oracle database, you need to change it to:
<property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

• jbpm-installer\standalone-*.xml

This file is the configuration for the standalone JBoss application server. When the installer installs the demo, it copies these files to the standalone/configuration directory in the jboss server directory.

We need to change the datasource configuration in standalone.xml so that the jBPM process engine can use our Oracle 11g database
The original file contains the following lines:


<datasource jndi-name="java:jboss/datasources/jbpmDS" enabled="true" use-java-context="true" pool-name="H2DS">
    <connection-url>jdbc:h2:tcp://localhost/runtime/jbpm-demo</connection-url>
    <driver>h2</driver>
    <pool></pool>
    <security>
       <user-name>sa</user-name>
       <password></password>
    </security>
</datasource>
<drivers>
    <driver name="h2" module="com.h2database.h2">
        <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
    </driver>
</drivers>

Change the line to the following:

<datasource jta="true" jndi-name="java:jboss/datasources/jbpmDS" pool-name="OracleDS" enabled="true" use-java-context="true" use-ccm="true">
    <connection-url>jdbc:oracle:thin:@xxx.xxx.xxx.xxx:XXXX:sid</connection-url>
        <driver>oracle</driver>
        <security>
            <user-name>jbpm</user-name>
            <password>jbpm</password>
        </security>
</datasource>
<drivers>
    <driver name="oracle" module="oracle.jdbc">
        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
    </driver>
</drivers>

Change same for "ExampleDS"

<datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
 <connection-url>jdbc:oracle:thin:@xxx.xxx.xxx.xxx:XXXX:sid</connection-url>
        <driver>oracle</driver>
        <security>
            <user-name>jbpm</user-name>
            <password>jbpm</password>
        </security>
</datasource>

NOTES: 

• xxx.xxx.xxx.xxx = database ip address, XXXX= database port number, sid = SID

• Create a User called "jbmp" in database

Run "jBPM data base schema scripts (DDL scripts)" which you can find in following path:
jbpm-installer/db/ddl-scripts/oracle

• Change the db.driver.module.prefix property in build.properties to the same “value” you used for the module name in standalone.xml. In the example above, I used “oracle.jdbc” which means that I should then use oracle/jdbc for the db.driver.module.prefix property.

Note: It's already done above.

• Lastly, you'll have to create the db/${db.name}_module.xml file. As an example you can use db/mysql_module.xml, so just make a copy of it and:
• Change the name of the module to match the db.driver.module.prefix property above
• Change the name of the module resource to the name of the JDBC driver jar that was downloaded.

The top of the original file looks like this:

<module xmlns="urn:jboss:module:1.0" name="com.mysql">

   <resources>

     <resource-root path="mysql-connector-java.jar"/>

   </resources>

So we will create "oracle_module.xml" in db folder which looks like following file:

<module xmlns="urn:jboss:module:1.0" name="oracle.jdbc">

   <resources>

     <resource-root path="ojdbc7-12.1.0.2.jar"/>

   </resources> 

    <dependencies>

      <module name="javax.api"/>

      <module name="javax.transaction.api"/>

    </dependencies>

</module>

After all this changes, you have to install this changes, for that:

• If demo is running then, stop it

ant stop.demo

• clean old installation 

ant clean.demo

• reinstall demo

ant install.demo

• start demo

ant start.demo

Reference:
http://docs.jboss.org/jbpm/v6.2/userguide/

    

Installing JDK on windows and set JAVA_HOME

 Installing JDK on windows and set JAVA_HOME

First download latest jdk or older one which you want to install.

Download following version for different windows 

32 bit windows	Windows x86
64 bit windows	Windows x64

You can find this, like following picture

To see system type

• In windows 7

click start on  button then right click on computer and select Properties

At new open window you can see System type like below.


  
After completing download double click on .exe file (jdk-7u60-windows-i586.exe)  to initiate the installation process.

it open a pop up window which asked you like follows
:- Do you want to allow the following program to make changes to this computer.
 
Then press okay of initiate the installation process or press no to cancel it.

Then press next






press next or change to change the default location on installation directory


After changing location press next.

press next or press changes to change the JRE location and press next

Then you will get a message that JDK is successfully installed.


Now press close. Congratulation you have successfully installed JDK.

To Set JAVA_HOME

click on start button -> right click on computer then select properties so a new window will open from that window select Advance system settings



It will open a new window called "System Properties", select tab Advanced and Environment Variable.



In new window click new button in System variable.






write following in new window
Variable name = JAVA_HOME
Variable value = full path of the directory where JDK is installed.
then press ok.



now select path (system variable) from the opened window and click edit



 

From newly open window select complete value of variable_value


and paste it to notepad like follows

C:\app\abc\product\12.1.0\client_1;c:\app\abc\product\11.2.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\TortoiseSVN\bin;%systemroot%\System32\WindowsPowerShell\v1.0\;%systemroot%\System32\WindowsPowerShell\v1.0\;%JAVA_HOME%\bin;%MAVEN_HOME%\bin;%TNS_ADMIN%

upend it by ;%JAVA_HOME\bin%

C:\app\abc\product\12.1.0\client_1;c:\app\abc\product\11.2.0\client_1\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\TortoiseSVN\bin;%systemroot%\System32\WindowsPowerShell\v1.0\;%systemroot%\System32\WindowsPowerShell\v1.0\;%JAVA_HOME%\bin;%MAVEN_HOME%\bin;%TNS_ADMIN%;%JAVA_HOME\bin%

copy the update path and paste in open window and click ok and close all windows

now open command prompt
click start button -> write cmd like follows and press enter 



on command prompt write java -version and press enter, so you will get information like follows