Oracle SOA 12C - Database Adapter calling complex plsql (Part 3)

Oracle SOA 12C - Database Adapter (Part 3)

This is a continuation post for the post 1, and post 2 where we call the database adapter with just one table configuration. 

 In this post we will discuss  about calling a complex with type. 

Calling  plsql procedure with Complex Types

  Some time we need to call the plsql procedure with complex parameter like records, records you can think of collections in java where entire record or records can be sent to database, its a great way you can send entire collection of employees for creation.

 The problem with BPEL is it cant work with the complex record parameters directly.  Hence when you try to create a DB adapter then it will create a wrapper PLSQL package.  This package will talk to plsql and  BPEL. In short it will be a communication channel which will make the complex type readable for the BPEL.

Our experience says that you should use table as a record type

, else if there is any change in the table structure, soa will throw some unnecessary errors, which is hard to debug.   

In this post expectation is that you have completed the post 1 and post 2, if not please go through that, we are not going to cover database configurations and weblogic configurations. 

first create a plsql procedure to create an employee. This procedure should take all parameters. If you are not very families with plsql then copy the code from the example and compile. 

Now once the code is compiled use the following steps to complete

1. Create a new SOA project and add a BPEL to this composite. Use Employee.xsd (given in application) for inputs and output for BPEL

2.  Once this BEPL process is ready then create the database adapter with the help of the below steps and then added it to BPEL process. 

         

    a) Name the Adapter

   b) Choose the connection.

c. Select for the plsql procedure and then click on next.

 d. Choose schema and then click on browse button.

 e. Now select the procedure as given below and click ok.

 f. Now give a name to the wrapper plsql procedure, it will create the wrapper file with the same name. No accept default and click next for all the other steps.

g. Click Next.

h. Click Next, 

i. Click on finish. Once finish you can, 2 new sql file will be created to create a new package, which will be used an adapter. These files need to be compiled on each and every database, where this procedure is suppose to run on each and every database where this process is going to be deployed.

BPEL and Composite changes.

 1) Once done with the DB Adapter creation, create a wire between the db adapter and BPEL, it will look like as given below.

  

 

    2) Browse the BPEL and create a  Invoke activity to call the database adapter and then a transformation feed the data. See the project for more details.

    

    

    e)  Now do the transformation, Once its done then add a Transformation to assign the values to the Database plsql procedure.

 

Now application is ready for the deployment, deploy the application and then test it with the payload given in the project by us.

Running the application:-

 Once application is deployed then now use the payload and press Run. Once its done then  press the launch flow trace. It will provide you entire trace of the activities got executed.

2. Now see the response message, this process is design to return you new employee Id, and success message.

3.  You can ensure using sql sheet to check if row is inserted or not.

Download Link.

   Please use the download link

Exercise :-

Add one more procedure to which accepts employee records and provide if this employee exists or not in status and you need to either create an employee based on the results or not.

Tips:

=> If sometime your application give unnecessary errors, run the process one more time or restart the server.

Happy to take any question in this.