Name: Calling a Pipeline Pilot Protocol from Oracle
Author: June Snedecor
Version: 0.1
Created: 11/12/2008
Modified: 11/12/2008

Purpose: This example shows how to call a protocol within Oracle.

Requirements: Pipeline Pilot 7.0, Oracle 10.2.0.4 (haven't tested other versions), UTL_HTTP package for 10g, SOAP UI, a good understanding of PL/SQL and Oracle
O/S: Windows and Linux
Limitations: Lots! The procedures included in this example will have to be modified for any other type of protocol you want to call. The GET_ALOGP function logs into the Pipeline Pilot server for each molecule, so it probably shouldn't be used in a production setting.

NOTE: I do NOT recommend this protocol if an alternative exists! Rolling your own SOAP packets and unraveling XML responses manually is not very flexible. If it's possible to use the Java SDK as an intermediary between Pipeline Pilot and Oracle or to call Oracle within Pipeline Pilot, I would do that instead.

As a related note, if anyone can get UTL_DBWS to work with Pipeline Pilot, please update this example. I have tried to get it working, but it doesn't seem to like the type of soap packets that use envelopes.

If you want to use this on your own protocols, you should have SOAP UI or equivalent to create test packets: http://www.soapui.org/

Keyword: oracle, sql, web service, soap
Contents: ALOGP_PROCEDURES_CLEANED.sql
ALogP_Calculator.xml

Installation:

1. Unzip the archive.
2. Save the ALogP_Calculator protocol to the Protocols tab.
3. Right click on the protocol in the Protocols tab and choose "Inspect Web Service"
4. Copy the first part of the WSDL Url (the part before the ?WSDL) into your text editor of choice. It should look something like this:
http://localhost:7014/scitegic/service/async/ALogP_Calculator
5. Log into Oracle in SQLPlus
6. Load the ALOGP_PROCEDURES_CLEANED.sql file:
SQL>@ALOGP_PROCEDURES_CLEANED
7. Test the GET_ALOGP stored procedure:

SELECT GET_ALOGP('cccccc','username','password','http://localhost:7014/scitegic/service/async/ALogP_Calculator') from dual;

8. Commit procedures to the database.