PI to Oracle d/b

[former_member190543]

Hi guys,

I have a requirement to pass the customer details from a webservice to Oracle database by calling a procedure on Oracle database. First part is simple, but I am not sure about the second part i.e., PI to Oracle d/b.

The requirement is that PI should make a call to the decryptor to get the user name and password in a key in the first call and then make a second call to the procedure/function in a package in Oracle database to pass these details aloong with other customer details.

Is it possible? If so could you tell me how please?

Many thanks in advance.

Ramesh.

[Shabarish_Nair · Accepted solution]

either you can put the first and second call together into a SP and make a single call from PI.

Else, you will have to use  a BPM with two sync send steps i.e for the first DB call, followed by the second.

[former_member190543 · Accepted solution]

Thanks Shabarish, Simone and Bhaskar!

I got a bit more clarity on the requirement. I have to make a call to this toolkit on Oracle which sends me the key (encrypted user name and password) which I have to decrypt in PI (using  "The DBMS_OBFUSCATION_TOOLKIT package") and send the username and password and send it along with other data)

I never heard of anyone importing/installing the toolkit in PI before. Is there something like this that we can do or that anyone has ever done? Or am I just over-complicating the things?

Thanks,

Ramesh.

[Shabarish_Nair · Accepted solution]

It would seem this is an overly complicated one Try to explore more around a single call to the DB via a stored procedure.

[baskar_gopalakrishnan2 · Accepted solution]

If you can't make both the calls as single using Stored Procedure, then the below is an another option.

Use Java mapping to make a database connection using connection class. After making connection, you might have to import those toolkit calls in the program to use that toolkit to retrieve the user/password credentials. There is no need to install toolkit inside pi. This is one option.

William Li's blog for creating UDF for the version 7.0 is available. You might do try using the code. I think Java mapping would be more flexibe. You don' t need install anything third party tool kit inside the PI. Everything you package it inside the jar along with your main jdbc connection class.

[Former Member · Accepted solution]

As per your requirements, I agree with Baskar for Java Mapping & Database Lookup approach to retrieve user/password credentials, before performing the SP.

[former_member190543]

Thanks everyone for your help..

Unfortunately the Oracle database is of a very old version and the drivers does not support JDK version greater than 1.3. So we scrapped the plan and looking into different options. JMS/FTP/Webservice/ABAP program

Regards,

Ramesh.

[baskar_gopalakrishnan2]

>The requirement is that PI should make a call to the decryptor to get the user name and password in a key in the first call

You might want to handle through  jdbc lookup the above part in the mapping and the second part can be handled using jdbc receiver.

[Former Member]

Yes, I am in agreement with Shabarish. Imho the best approach is to develope a Stored Procedure on Oracle System, and make a single call from Sap PI.