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
- SAP Managed Tags:
10 Comments
