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.

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.

Using Database Adapter SOA 12C

Database Adapter 

We are starting one of most important topic of SOA, database adapter. Its very unlikely that a project in fusion middleware have not used, database adapter.

In SOA we know everything is presented a webservice. Hence to create a process if we need to do some interaction with database then we have two options

1. Create a webservice in any technology  and then expose it.

2. Directly interact with database using the database adapter.


As the name suggest this adapter provide an interface between the database and present it our process in webservice form.  Once this adapter created then there is almost no difference calling a werservice from the BPEL process or calling a database adapter.

  But there are some steps involve in weblogic also. We are breaking this post into two steps

1. Creating a create, insert, delete operations. (Post 1)

2. Weblogic configurations  (Post 1)

3. Calling a plsql procedure from database adapter  (Post 2)

4. Calling a plsql procedure with types from database adapter   (post 2)


In this post we will create a employee and then will update the employee and read it.


1. First create a schema, which have following attributes

  a. emloyeeName
  b. userName
  c.  firstName
  d.  LastName

 or take schema from the project link, given below.
   



2. Once done create a new application and project, and add the new schema, can copy the employeeSchema from the project added in this blog.




3. Now create a new BPEL and choose request and response schema, from the xsd which is been copied from the project.

4, Now create a new connection and give the credentials according to your database.

5.  Now create a new database adapter and name it employee schema.

b) Now choose the db connection which we created earlier.

c) Now choose all the options which you want this db adapter to perform.

d) Now choose table, in this example, we are using employee schema.

g) In this i have created an employeeId parameter to select the employeeId for select.

See the images given below.



6) Now we can call this db adapter as a simple service.  Use xlst or assignment to assign the values to the service (db adapter).

7. Now we are ready to start for the testing of our object. Deploy and provide the inputs.

Weblogic Configurations. 

    Database Connection Creation 
    1. Create a new connection in the web-logic server use the below screenshots for more clarity.

2. Now have the connection, almost same as given below. JNDI name should jdbc/HRDB

3. Now add the information about the host and also the username and also password.

4. Now test the connection and add the target as the Default server.

    DB Adapter Connection Creation 

      1. Now go the weblogic console again and click on deployments, now you can see database adapter as of the application.  To use any connection our connection should be available under database adapter.

     
   

  2. Once DB adapter application open click on configuration and then select outbound connection pools. Once here click on new. 

3.Now provide the JNDI name as eis/db/HRDB.  This jndi should be the same name, which was used in the database adapter. Actually this connection database adapter will search in the application and this connection pool will allow connection to the actual database.

4. Now enter XADataSource name, as we want adapter to execute and should not take part in the actual transaction.  Once done save.

5. Now update the database adapter, select the database adapter and the update.

6. Now it will show the deployment plan and then finish. In SOA 12c, we can use it post deployment.  In older version we need to first restart before using it.

Running part 1.

   Now deploy the application and once deployed you can provide the payload stored with the application.

Once its done click on launch the flow trace and you can see the below level of flow trace.

Database Running.

 Now check the database, this row is already there.

 

Download 

 Please use the link to download the exercise.

Exercise 

1.  Add an operations to delete the employees.

2.  Add an BPEL to merge the employees.


Let us know your feedback. We are continues with next post for the Plsql procedure.