Pillai’s

Technology and Everything…..

String to Fields – Converting CSV file to Table Data

Posted by Shibu on March 17, 2008

Recently in one of my project’s work I had to read a csv file and insert into Oracle database. Though sql loader is an option I had to use PL/SQL as we have to do some processing before inserting into the table.

So initially each record from the file is read and inserted into a temporary table. This table just has one Varchar2 field and few other fields to hold the metadata. Basically each record from the file is inserted as a Varchar2 field in this generic table.

I created a function which will receive one record as a parameter and then split the string into individual fields and populate an array of strings.

If you are interested in, following is the source code:

Declare
type v_array is table of varchar2(300) index by binary_integer;
in_string VARCHAR2(2000);
v_index NUMBER := 0;
v_data_array v_array;
o_index NUMBER := 0;
i NUMBER;
BEGIN
— Assign a value to the string for test purpose
in_string:=”;
WHILE 1 0
THEN
v_index := v_index + 1;
END IF;
IF SUBSTR (in_string, v_index, 1) = ‘,’ OR v_index = 0
THEN
/* We have to process one field here */
— Get data into the first array */
— Increment index of result array here
o_index := o_index + 1;
v_data_array (o_index) := ”;
WHILE 1 = LENGTH (in_string);
v_data_array (o_index) :=
v_data_array (o_index)
SUBSTR (in_string, v_index, 1);
— we will exit this loop when we find next , character or the end of input string
END LOOP; — for end of each field
— if it is not end of string we should reduce index by one otherwise we will miss next field !!!
IF v_index = LENGTH (in_string); — exit when last characte of the string is read
END LOOP; — for the string
END;

Posted in Technology | Leave a Comment »

Deploying Oracle BPEL Process to Different Instances Using ANT and Parmeters

Posted by Shibu on December 18, 2007

If you are a Oracle SOA Developer, I am sure you will be using Oracle Jdeveloper for the development of BPEL process. In this Blog I will show you how you can build and deploy your BPEL process from command line using ant. Also I will explain you how you can pass parameters while compiling, so that you can deploy to different servers by using server name as parameter. Also I have used parmater to specify directory locations for the file adpaters used in the BPEL process.

This blog assumes that you already installed JDeveloper and created a BPEL process and deployed to development server using JDevloper.

Open command window by running cmd command. Find out where you have installed your JDeveloper. In my case I have installed it in the D:\jdev10132 directory. This is going to be your ORACLE_HOME. Issue following command from the command window.

SET ORACLE_HOME=d:\jdev10132 (This should be your directory where you installed JDeveloper)

Now make sure the command worked by issuing echo %ORACLE_HOME%

Next you have to set ANT_HOME variable. Issue following command,

SET ANT_HOME=d:\jdev10132\ant

Now you have to include ant in the PATH. Issue following command,

SET PATH=d:\jdev10132\ant\bin;%PATH%

Now type ant you should get message like below
Buildfile: build.xml does not exit!
Build Failed.

(Of course if you have build.xml file in the directory where you are now, ant will try to compile the default target mentioned in the build file and you may get different message)

Next we have to make sure we have all jar files in the ant lib directory. Go to ANT_HOME\lib directory. You should see many jar files in this directory. I am attaching the screen print of the files which I have in my lib directory.

Next we have to make sure we have all jar file in the bpel lib directory. Go to your ORACLE_HOME. In my case it is d:\jdev10132. Next go to integration\bpel\lib directory. Here you should have many files. In my case I had only one jar file here. I will explain you how you can get all required files. If you have already installed BPEL Server then you may have all files. Required files are orabpel-ant.jar, orabpel.jar, orabpel-boot.jar, orabpel-common.jar, orabpel-exts.jar, orabpel-thirdparty.jar, oracle_http_client.jar.

If you do not have these file you can download these jar files from the server if it is installed in different machine. In my case we have our SOA server installed in a linux machine. We installed our SOA server in /d01/orsoa directory. So all the jar files for bpel are in /d01/orsoa/10.1.3/bpel/lib directory. I downloaded all jar files from this directory to the ORACLE_HOME\integration\bpel\lib directory.

Now you have finished all pre-requisite steps.

Find out the directory where you have your application/project saved from JDeveloper. In my case my project files are in the directory d:\oaframework\jdevhome\jdev\mywork\BPELExample\BPELProcess1 directory. BPELProcess1 is my BPEL process name.

So find out where you have saved your BPEL project and cd to that directory in the command window. Just list all files using DIR command. You should see a build.xml file which is created by JDeveloper.

Now we will prepare for our deployment using ant.

As I mentioned before I have a file adapter in my BPEL process which reads file and archives it too. For different servers, the physical directory location is different. So while deploying I want to make the directory location as parameter so that DBA who will deploy to production server will change it accordingly. I will show how we can achieve this.

In Oracle JDeveloper when defining a file adapter you have mention the location of files where it reads from or write to. If you have a file adapter open the file adapter wizard and go to the step where you define the file location. If you notice there is an option “Directory Names are Specified as Physical Path or Logical Name. Choose Logical Name here. In my case I have mentioned input file location as “indir” and archive file location as “archdir”. Choose appropriate names for your case and save all files. Build from JDeveloper.

Now that you have mentioned logical name for your directory, you have to mention the mapping to physical path somewhere right? Oh yeah, let us do that. Go to your project home directory. In my case it is d:\oaframework\…\BPELProcess1. Under this you should see another directory bpel. Open the file bpel.xml in this directory and search for the logical name you used. I will see like below,

JDeveloper created these two for you. We have to mention the physical path here as value. What we will do is introduce two variables here instead of values. These variables can be passed as parameters while compiling using ant. So DBA can supply file location as parameters while compiling and deploying. So I modified these two lines like below,

@@archdir@@
@@indir@@

Save this file.

Navigate one directory above to your project home directory. Here you should have build.xml file.

Create a new file pre-build.xml file in this directory. When you use ant to compile it will first process commands written in this file. We will replace our variables in the pre-build process. So create pre-build.xml file like below,

<?xml version = ‘1.0’ encoding = ‘UTF-8’?>
<project name=”bpel.deploy” basedir=”.”>
<property name=”process.dir” value=”${basedir}”/>
<copy file=”${basedir}\bpel\bpel.xml” tofile=”${basedir}\bpel\bpel.xml.orig”/>
<replaceregexp file=”${basedir}\bpel\bpel.xml” match=”@@archdir@@” replace=”${archdir}” />
<replaceregexp file=”${basedir}\bpel\bpel.xml” match=”@@indir@@” replace=”${indir}” />
</project>

As you can see I am replacing the @@archdir@@ using the parameter archdir. Parameter can be accessed using syntax ${ }

Also you might have noticed that I am copying bpel.xml file to bpel.xml.orig. This is because when you deploy using ant command bpel.xml file will be changed. @@@@ will be replaced with the value passed as parameter. So when you try to compile/deploy second time ant will not able to replace as there will not be any @@@@ in the bpel.xml file !!

Now we will create another file post-build.xml file. In this file we will copy the bpel.xml.orig file back to bpel.xml as below,

<?xml version = ‘1.0’ encoding = ‘UTF-8’?>
<project name=”bpel.deploy” basedir=”.”>
<property name=”process.dir” value=”${basedir}”/>
<copy file=”${basedir}\bpel\bpel.xml.orig” tofile=”${basedir}\bpel\bpel.xml”/>
</project>

So far we have parameterized directory location for the file adapter. Now another variables needed are server name, port, user name and password. All these will vary from instance to instance. So we have to provide flexibility to DBA to change these during deploying. We will make all these parameters.

Open the file build.properties. Here is where you will mention all these variables.

Uncomment and update following lines like below,

admin.user=${adminuser}
admin.password=${adminpass}
http.hostname=${dephost}
http.port=${depport}

Alright, you have finished all steps for deployment ….Ready? Ok, deploy using command

ant –Dadminuser=administrator –Dadminpass=your_password -Ddephost=your_host_name
-Ddepport=your_port_address -Darchdir=physical_location_of_archiving
-Dindir=physical_location_of_input_directory

If everything done as mentioned, you should be able to deploy your process !!!

Posted in Technology | 2 Comments »

ORACLE BPEL Batch Processing Using Transform Function

Posted by Shibu on December 3, 2007

In my previous blog I explaied how to do batch process using while process. One issue with while loop is that in the BPEL Console, when you try to view the process instance, it may take a long time for the page to display the instance if there are lot of iterations. Because each iteration is displayed in the instance diagram. Just imagine if you file has 200-300 records?
Another way to achieve batch processing is to use Transfrom function. Overall diagram will be as shown on the left. Here you do not have to define variable for count of records or iterations. After the initial “ReadFile (Invoke)” activity, create a new transfrom activity. Edit your transformation as shown below (Click on image to view in detail)
Note that for-each function is added to the Customer Node so that the next process is repeated for each Customer Node.

Posted in Technology | Leave a Comment »

Batch Processing Using Oracle BPEL

Posted by Shibu on December 2, 2007


Ever needed to create a BPEL process where you have to do batch processing? I will explain how you can create a simple BPEL process which will process a customer file and once all customer records are processed invoke next process. Figure on the left shows the overall process diagram.
Input is a simple csv file which has three fields, Customer Name, Address1 and Address2. Just create a simple file which has three records.
Now Create a partner link using File Reader Adapter. You should enable the “Files Contain Multiple Messages”. Choose a very high number (999999999) in the “Publish Messages in Batches of” field. like shown below

Now we have to create two new variables. One to hold the total number of records(count) and another one to keep track of the iterations (iterator). You can get the total record count using countNodes function. Use this function for you customer node (ora:countNodes(‘receiveInput_Read_InputVariable_1′,’Root-Element’,’/ns2:Root-Element/ns2:customer’)
Assign a constan value 1 to the iteration function. Next you have to create a while process. Use the expression ‘iterator'<=’count’ as your while condition. Now in the while process, you can assign each file record field to input variable of the next invoke process. Remember that when we enabled multiple messages to be loaded, BPEL will load all records into memmory array. So now you have to use iterator as index of the array to process record by record. You can use the getElement function for this purpose (ora:getElement(‘receiveInput_Read_InputVariable_1′,’Root-Element’,’/ns2:Root-Element/ns2:customer/ns2:Name’,
bpws:getVariableData(‘iterator’)))
See how I am using iterator as index value. This expression fetches Name element from the first node of Customer. Once all elements from the Customer nodes are copied to destination variables, increment ‘iterator’ by one. Now you can add your next process after the while loop, so that will be started after processing all file records.

Posted in Technology | 2 Comments »

A Good E-Commerce Website

Posted by Shibu on September 21, 2007

I recently purchased a DVI/HDMI cable from MonoPrice. If you been on market for these kind of cables you will know how costly it can be if you go to shops like BestBuy, CircuitCity etc. Of course quality of the material might be good. But I don’t think quality is that different with the non-branded cables, at least not for human eye/ear. Not only price was cheap, user interface and response time is amazing. They have auto-responded e-mails immediately (normal with most of the e-commerce sites) with all details, including a USPS shipping number. Great site, I highly recommend if you are looking for any electronic accessories like this.

Posted in General | Leave a Comment »

Joost – New way of watching full screen TV !

Posted by Shibu on August 22, 2007

You gotta watch this. I am not a fan of watching internet videos as you know most of the time poor quality and if you increase the size quality will be even worse. I changed my opinion of watching video over internet after seeing Joost. It’s from the founders of Skype. TV like programs for totally free.

Posted in General | Leave a Comment »

Kudos to Mirth Project !!

Posted by Shibu on August 10, 2007

Recently I had to use an integration engine to integrate data from MySql and Oracle database to another system using LLP. I did a search for open source integration engines and browsed through few of them. Read through them and Mirth Project sounded interesting. Downloaded and could install in a few minutes. Guess what, it is one of the best open source projects I have used. I used version 1.5 and could install in both Windows and RedHat Linux. Currently I am using this for messaging HL7 messages (in xml format).

Kudos to Mirth Project team !!

I could create a channel which reads data from MySql and send xml file using LLP and more importantly all this was done in an hour (from scratch) !!!!

Posted in Technology | Leave a Comment »

My first Blog !

Posted by Shibu on August 3, 2007

Hi !
This is my first blog in the World Wide Blog ! I have been trying to install a server of my own and use some user friendly freeware tool to get this done. After all trials realized that it’s waste of time and energy when there are plenty of free hosting available. So here I am….

Posted in General | 1 Comment »