Generating artifacts for invoking a stored procedure in MSSQL server and IBM DB2

1. Set the operating system classpath to contain the following java library  files found in jdeveloper installation
1. ocrs12.jar
2. ojdbc14.jar
3. ojdbc14dms.jar
4. orai18n.jar
5. sqljdbc.jar
6. DBAdapter.jar
7. bpm-infra.jar
8. orabpel.jar
9. xmlparserv2.jar
10. xschema.jar
11. toplink.jar

    eg : set classpath=%classpath%;d:\jdevstudio10134\jdbc\lib\ocrs12.jar; d:\jdevstudio10134\jdbc\lib\ojdbc14.jar; d:\jdevstudio10134\jdbc\lib\ojdbc14dms.jar; d:\jdevstudio10134\jdbc\lib\orai18n.jar; d:\jdevstudio10134\jdbc\lib\sqljdbc.jar;

Note: If any of the jar is missing in the jdeveloper/SOA suite installation, the jar can be downloaded from http://www.findjar.com/

2. Prepare the properties file that will be consumed by the java tool

The properties file should have

ProductName=<Database product>

DriverClassName=<Driver to be used>

ConnectionString=<JDBC Connection String>

Username=<username here>

Password=<password here>

DatabaseName=<MSSQL database>

SchemaName=dbo

ProcedureName=<name of the procedure here>

ServiceName=<Service name. This will be the name of the wsdl generated and referenced>

DatabaseConnection=<OC4Jra.xml connection string that the artificat will use>

Eg : dbname.Properties

ProductName=Microsoft SQL Server

DriverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

ConnectionString=jdbc:sqlserver://xx.xx.xx.xx:xxxx;databaseName=dbname

Username=dbusername

Password=XXXXX

DatabaseName=dbname

SchemaName=dbo

ProcedureName=spiCSIncidentTaskFromSOA

ServiceName=srv_CSTask

DatabaseConnection=eis/DB/dbname

3. Use the below command to generate artifacts using the above properties file

C:\Jdev10.1.3.4\integration\lib>java oracle.tip.adapter.db.sp.artifacts.GenerateArtifacts dbname.Properties

3. The tool will generate two WSDL file and  an XSD
4. Import the Project WSDL into the BPEL Project
5. Create a partner link for the imported wsdl
6. Create a Invoke activity and map it to the new partner link