Skip to Content

As most of the people reading this blog probably know the standard sender JDBC adapter does not work well with Oracle stored procedures (the receiver works well that’s why we concentrate only on the sender). By saying “does not work well” I mean it does not work at all but there are some workrounds (for example with functions) which can enable you to use something more then a simple select statement. (check OSS note  Note 941317 – XI / PI JDBC Adapter: Known Problems / Incompatibilities point “6. Oracle Database 10g 10.2.x JDBC Driver” for details”) 

Question 1

What if you work with an application based on the Oracle which has plenty of stored procedures ? 

Answer 1

Then basically you have two approaches: 

a) change all of the stored procedures to select statements which might be very difficult and require lots of resources – I’d compare it to using a file adapter with ERP connectivity when IDocs are being used in ERP for data&process intetegration 

b) do something in PI that will allow using stored procedures 

 

Let’s compare both approches 

Question 2 

So why would you ever want to change the Oracle’s stored procedures and not develop your own JDBC adapter 

Answer 2 

I can see only two explanations to this question 

a) if you only have a few (1-4 stored procedures that you need to change) 

b) you don’t know how long does it take to develop an adapter like this 

 

As you can imagine I’m going to concentrate only on the second one and will try to explain how you can develop a sender JDBC adapter for Oracle stored procedures in 5 days (so hopefully in less time which would take to convert a few big stored procedures to something else which could be executed with the standard JDBC adapter). 

 

Day 1 

Get the code for the sample file adapter (OSS note  Note 1004000 – SAP NetWeaver PI Adapter/Module Development: API Changes). As the standard sample adapter is of type file this is exactly the same thing as we need as file adapter and JDBC sender adapter are both polling adapters. Once you get the code you need to get the respective jar files,  compile the code, create RAR and SDA files, upload the adapter metadata file to ESR and test it. Setting up the NDWS environment is actually one of the most difficult and most time consumig (apart from testing) thing that you wil be doing during your adapter development – that’s why we may spend a whole day doing that. 

 

Day 2 and 3 

Now it’s time to prepare the code which will connect to the Oracle stored procedure and replace the old methods in class SPIManagedConnectionFactory. Have no fear you don’t need to reinvent the wheel…. How many times do you think java guys needed to do the same – call an Oracle SP ? So you can find lots of good pieces of code on the internet – add some error handling you’re done. You can still work on the same adapter metadata as used in file adapter – we don’t need to change it yey (just use those standard input fields for your new values). 

 

Day 4 and 5 

Now we need to do some small changes in a few other classes like XIConfiguration, SPIManagedConnection – but this should take a day and half max and the next half of a day you can spend on changing the adapter metadata with new names and nice descriptions and we can start doing more sophisticated testing. 

 

And we’re done ! (we only need to develop the sender JDBC adapter not the receiver…) 


Obviously there are a few more things to be done like:

– monitoring (so you can check the status of the communication channel) 

– all sorts of testings (like different types of stored procedures, stress testing, etc.) 

– you can implement very nice functionalities like node level locking (so only one server node will execute the SP at the same time) 

– etc. 

 

But the fact is that the adapter itself was developed in 5 days and just needs more polishing.

 

3 Top things to remember if you want to develop an JDBC sender adapter for Oracle stored procedures in 5 days:

 

1. you need to have access to adapter deployment (SDM, JSPM or having a PCK would be ideal) 

2. you need to have Oracle DB installed – so you can test quickly 

3. you need to have some java knowledge (if you don’t know how to write a UDF maybe you will need a little bit more then 5 days)  🙂

 

UPDATE – adapter resources 

Some more resources on the adapter development:

Adapter and Module Development

How to develop a JAVA Based adapter in PI

To Create Or Not To Create An SAP XI Adapter

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

    1. Michal Krawczyk Post author
      Shabz,

      no 🙂 but there are some useful info-blogs – have a look at the UPDATED – resource section of the blog 🙂

      Thanks for the comment,

      Regards,
      Michal Krawczyk

      (0) 
  1. Arkesh Sharma
    Hi Michal,

    Nice blog but I am not aware of Adapter Configuration and Development.
    Any blogs related to the above topic will be really helpful for me.

    (0) 
  2. James Wood
    Nice blog. Another potential option I might throw out in addition to the ones described is to use the SAP NetWeaver Scheduler for Java. Here, it’s pretty easy to create a simple MDB to pull the data on a periodic basis and dump it on a local JMS queue, file share, etc. Naturally, the custom adapter approach is the way to go if you have a lot of these. But if you only have a few one-off scenarios, this could be one way to go – especially if you’re not familiar with the JCA.
    (0) 
    1. Michal Krawczyk Post author
      hi James,

      if you can use NW scheduler then you can write an adapter – that idea was supposed to be the point of the blog too 🙂

      Regards,
      Michal

      (0) 
  3. Araman Amru
    We’ve set up our Oracle JDBC receiver adapter for the integration of SAP to Primavera (using Primavera API) with a guide from Riyaz’s blog : http://www.riyaz.net/blog/jdbc-adapter-configuration/technology/1200/ and this guide : http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/f04ce027-934d-2a10-5a8f-fa0b1ed4d88f?QuickLink=index&overridelayout=true
    It was a one way communication where SAP act as the sender and Primavera as the receiver.
    And I thought, if we’ve successful with the receiver.. it should be the same with the sender..

    Thanks Michal!!

    (0) 

Leave a Reply