Sunday 23 September 2012

How to FTP flat file from BPEL

Recently I had a requirement, I’ve to publish a flat file which is position based to FTP server from BPEL.But the problem is I don’t have any nXSD/DTD to use in FTP adapter.Client will pick the file from FTP location based on image of file content.I first tried with opaque option in FTP adapter in vain because from BPEL I’m generating the whole data in String format and opaque require base64 encoding.Another constraint is that if I publish any file with a predefined schema then the actual content will be enclosed by XML tag which i don’t want. So here is my solution…

At first download ftp4j jar from http://www.sauronsoftware.it/projects/ftp4j/ which makes your life more easier.In BPEL I’ve the data in string format , I’ve to convert the same in a file content , then need to put the same in FTP server.

Place the ftp4j file in SCA-INF/lib folder of your BPEL project, obviously I’m using SOA11.1.1.5.So when you compile or deploy the project it will be referenced in the classpath.You need not to place that jar anywhere else :)

Then add the following import in your .bpel file,

<bpelx:exec import="java.util.*"/>
<bpelx:exec import="java.lang.*"/>
<bpelx:exec import="it.sauronsoftware.ftp4j.FTPClient"/>
<bpelx:exec import="it.sauronsoftware.ftp4j.FTPFile"/>
<bpelx:exec import="java.io.File"/>
<bpelx:exec import="java.io.FileWriter"/>
<bpelx:exec import="oracle.xml.parser.v2.XMLElement"/>

Here is the code which will FTP your file,

try{    
                String FTP_SERVER="XXXX";    
                String USRE_NAME="YYY";    
                String PASS="ZZZ";    
                String PDATA =(String)getVariableData("filePDATA");  
                addAuditTrailEntry(PDATA);   
                XMLElement fileIn=(XMLElement)getVariableData("SelectPDATAOfFileDB_InputVariable","SelectPDATAOfFileDBInput_msg","/ns6:SelectPDATAOfFileDBInput/ns6:FILE_NAME");
                addAuditTrailEntry(fileIn.getTextContent()); 
                FTPClient client=new FTPClient();  
                client.connect(FTP_SERVER);  
                client.login(USRE_NAME,PASS);  
                System.out.println("Connected to server");  
                addAuditTrailEntry("Connected to FTP server");   
                File makefile=new File(fileIn.getTextContent());  
                FileWriter fwrite = new FileWriter(makefile);  
                fwrite.write(PDATA);  
                fwrite.flush();  
                fwrite.close();  
                client.upload(makefile);  
                client.logout();  
                client.disconnect(true);   
            }catch(Exception e){    
                System.out.println(e.getMessage());    
                addAuditTrailEntry(e.getMessage());     
            }

Here you have to provide your FTP server URL , username and password.You can create 3 preference variable and refer the same using getVariableData from Java.In my program filePDATA variable store file content in BPEL and I’m also passing the filename as input to my program and at last using the method of FTPClient of ftp4j library I’m placing the file in remote server.As the file content is String , so you have full control on that , I mean you can manipulate data as you need. 

Looks so simple , but took some amount of time to figure out :)

Sunday 27 May 2012

Configure SSL in FMW DataSource

Few days back I was looking into how to enable SSL in datasource and database.I didn’t find any good document with step by step instruction, however at last I was able to configure it successfully after reading a whitepaper at http://www.oracle.com/technetwork/database/enterprise-edition/wp-oracle-jdbc-thin-ssl-130128.pdf.

NOTE :SSL can not be configured in XE database.In this blog I mentioned the path as per my system , you need to configure accordingly.

Enabling SSL in DB:

First create a directory under C:\shrik\Oracle\Database\product\11.2.0\dbhome_1\BIN\owm\wallets\test where I'll place ewallet.p12.

orapki wallet create -wallet C:\shrik\Oracle\Database\product\11.2.0\dbhome_1\BIN\owm\wallets\Shreekanta -auto_login

image

that will create ewallet.p12.Open OWM and wallet,

image

Just need to add a cert here,

image

image

Now basically you need to export CSR and submit to CA.

image

For my case I've used free CA available over net at http://www.getacert.com/signacert.html. Upon submitting CSR it will give CAcert and test cert.so here is my directory structure,[refer http://shrikworld.blogspot.in/2011/07/securing-soa-11g-environment.html]

image

Now we need to enter CA into trust store of wallet and test cert into certificate branch like below,

image

image

After that wallet should show cert as ready, save and exit the same.

image

Now we need to modify the following files accordingly,

 

listener.ora

# listener.ora Network Configuration File: C:\shrik\Oracle\Database\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (SID_NAME = CLRExtProc)

      (ORACLE_HOME = C:\shrik\Oracle\Database\product\11.2.0\dbhome_1)

      (PROGRAM = extproc)

      (ENVS = "EXTPROC_DLLS=ONLY:C:\shrik\Oracle\Database\product\11.2.0\dbhome_1\bin\oraclr11.dll")

    )

  )

LISTENER =

(DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

                          (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 2490))

    )

  )

ADR_BASE_LISTENER = C:\shrik\Oracle\Database

WALLET_LOCATION = (SOURCE = (METHOD=file) (METHOD_DATA=(DIRECTORY=C:\shrik\Oracle\Database\product\11.2.0\dbhome_1\BIN\owm\wallets\Shreekanta)))

SSL_CLIENT_AUTHENTICATION=FALSE

tnsnames.ora

# tnsnames.ora Network Configuration File: C:\shrik\Oracle\Database\product\11.2.0\dbhome_1\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

LISTENER_ORCL =

  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (CONNECT_DATA =

      (SID = CLRExtProc)

      (PRESENTATION = RO)

    )

  )

ORCL =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

    )

  )

  SSL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 2490))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = orcl)

    )

    (SECURITY=(SSL_SERVER_CERT_DN=\"CN=test,C=IN\"))

  )

sqlnet.ora

# sqlnet.ora Network Configuration File: C:\shrik\Oracle\Database\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

# Generated by Oracle configuration tools.

# This file is actually generated by netca. But if customers choose to

# install "Software Only", this file wont exist and without the native

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES= (BEQ,TCPS,NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

WALLET_LOCATION = (SOURCE = (METHOD=file) (METHOD_DATA=(DIRECTORY=C:\shrik\Oracle\Database\product\11.2.0\dbhome_1\BIN\owm\wallets\Shreekanta)))

SSL_CLIENT_AUTHENTICATION=FALSE

 

Then restart the DB and listener and your database is now SSL enabled over TCPS.

Configuring SSL in DataSource of FMW:

Add the certs that you generated in above steps in weblogic trust store.[please refer http://shrikworld.blogspot.in/2011/07/securing-soa-11g-environment.html]

restart weblogic server.Go to the datasource,

Click on connection pool,  add the url as below,

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCPS)(HOST=localhost)(PORT=2490)))(CONNECT_DATA=(SERVICE_NAME=orcl))(SECURITY=(SSL_SERVER_CERT_DN="CN=test,C=IN")))

In the properties section add the below property accordingly and test

javax.net.ssl.trustStorePassword=Password

user=DEV_MDS

javax.net.ssl.trustStore=C:\shrik\Oracle\fmw11.1.1.5\wlserver_10.3\server\lib\shrikTrust.jks

javax.net.ssl.trustStoreType=JKS

 

Test the same.

Now the datasource is SSL enabled.