Tips for Working with Stored Procedures in OSB 12c

During a recent engagement, I faced a few issues while creating a database adapter for a stored procedure in OSB 12.2.1.2. I wanted to share the detailed steps to create the database adapter, the issues I encountered and the resolution to those issues, in the hope that his may help other people, if they come across similar issues.

As SOA Suite 12c uses JDeveloper as the development environment for both SOA and OSB, it is worth noting that it is now possible to create the database adapter in the OSB project itself. The unified development environment provides a consistent approach for both SOA and OSB, unlike the previous approach, where we needed to import the JCA and corresponding files in Eclipse.

This article will cover:

  • Steps to create 12c Database Adapter for Stored Procedure
  • Issues faced
  • Resolution to the issues
Steps to Create Database Adapter

Step 1: Open the Database Adapter Configuration Wizard and name your DB Adapter and specify the location. The Default location is the Resources folder in the OSB project for wrapper, JCA, WSDL, etc, with the exception of the business service, which defaults to the Service Directory location.

Step 2: Create Database Connection (could be on premise or cloud ie Oracle Cloud or AWS) and select it.

Step 3: Select Stored Procedure option.

Step 4: You can leave the schema as Default or choose to select it. The schema name will automatically be included in a few files that are generated as part of the DB Adapter creation process. In subsequent steps, we will need to make sure that we revise or modify the schema name - this is important to note because during development we might use a local or temporary database, which we need to change later on, for deployment purposes. Also there could be instances where schema name has environment specific keywords in it, which we need to change as we progress our code from different environments.

As shown below, select the stored procedure.

Step 5: This is the important step. There are two fields where we need to take care - the wrapper procedure name and the checkbox to overwrite.

The procedure name needs to be chosen carefully - it cannot include any "." (fullstops/periods). I discovered this by trial and error when I used "." in the stored procedure name - it complained about it, however, it did not provide guidance about the right error. Instead it will create sql create/drop wrapper package files but no JCA , XSD or WSDL and it will error out saying 'Failed to create adapter'.

The workaround I used on our project was to use an underscore ( _ ) in the procedure name - for example "osb_procedurename", and this worked fine.

Also, if you are recreating or updating the adapter, be sure to check the overwrite option for existing wrapper package.

Step 6: Click Next and Next. DB Adapter creation is finished and it will create the following files under the resource folder:

Step 7: By default Oracle will generate all the files in the Resources folder. However, if required, based on your project guidelines, you may like to change the location of artifacts to respective folders by using the move option (by right clicking on the artifact and choosing move option), as it will update the reference of dependent artifacts, automatically. In the screenshot below, you can see an example of the guidelines that we followed in our project, for separating JCA, WSDL, XSD files into their respective folders.

Additional (optional) Step 8: This is an important step as this is where we will modify or remove the schema name, per your development environment requirements. That is, when we created the adapter in Step 4, the auto-generated files had the schema name attached, by default. However, it is common that schema names differ between environments in the development lifecycle - such as DEVMDS, SITMDS, PRODMDS, respectively. Hence, we can't simply deploy our code to the Test environment by using the defaulted development schema, as it will generate a "Schema Not Found" error. There could also be other situations where the schema name is different, and therefore it is good practice to remove the schema name.

To do this, you will need to edit the following four files that have schema name attached to database resources, such as the procedure name.

  • Open the schema file and remove the schema name

  • Open the JCA file and remove the schema name

  • Open the SQL file and remove the schema name

  • Open the Business Service and remove the schema name property

Additional (optional) Step 9: At the time of development, we ran into a couple of additional issues, where we had to make some minor updates to the automatically generated scripts.

  • In the auto-generated SQL file, needed to remove the "REPLACE" for the TYPE as OBJECT as shown below for TYPE "TIF_ ..":

  • In some cases you can see the following error while running the adapter that is ORA-06531:Reference to uninitialized collection. Then you need to change the text as follows in the auto-generated SQL file.

Source Text: (generated text in the file)
IF aSqlItem.COUNT>0 THEN

Target Text: (manually change the text file )
IF aSqlItem IS NOT NULL and aSqlItem.COUNT>0 THEN

See the example below:

I hope you find these tips handy if you are working with stored procedures in OSB 12.2.1.2.

Thanks,

Tony Webster

Sonal Sharma

Having more than 8+ years hands on experience on OFM (10g , 11g and 12c) covering OSB, SOA, ADF and ICS . Having wide domain experience in Telecom, Utility, Credit Union, Metals and Mining, Medical.