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

Oracle SOA 12C - Database Adapter (Part 2)

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

 In this post we will discuss  about calling a simple plsql.

Calling  plsql procedure

  When we need to call a simple plsql, its almost similar to the fist post. Only change will be we need to configure the plsql procedure call while configuring the database adapter. Also one more thing if we are configuring plsql adapter for the procedure or function call, we will not be able to reuse a adapter. 

 Which mean for each and every call to procedure we need to create separate database adapters. Hence your design should be minimum calls. we need to think and need complete the activity in one or two procedure calls. In a procedure again you can call multiple procedure. But SOA to plsql traffic we need to minimize. 

 If you are adding the new parameters to the plsql procedure then no need to re-create it just to next-next and dbadatper will be updated with the new parameters. 

In this post expectation is that you have completed the post 1, 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) Browse the schema and procedure you want to execute. It will show you input and output parameters

    d) Now click ok.

  e)   Now keep he default and say next.

3. No add the wire between the db adapter and BPEL so that we can call from the BPEL.

4. Now use invoke activity,to call the plsql procedure and the create two variable one for inputs and then output.

5. 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.

Once done then check the database for the new employee.

Download Link.

   Please use the download link

Exercise :-

Add one more procedure to remove the employee and then remove if salary is more then 5000 post addition.