Saturday 2 April 2011

Salesforce API call from Oracle APEX

This post is about integrating Salesforce force.com with Oracle using the Salesforce API.

I will try to show you how you can write a PL/SQL procedure to quickly send an SQL INSERT you do in an Oracle table to a Salesforce custom object as well. That is, how you can INSERT to a Salesforce object simultaneously when you insert to an Oracle table. I will use Oracle APEX, an Oracle RAD (Rapid Application Development) tool, as the Oracle database. 

You can achieve this by using the Oracle APEX package APEX_WEB_SERVICE.MAKE_REQUEST and send SOAP envelopes to the Salesforce WSDL, without the HTTPS option, as I couldn't get that part working with my FREE Salesforce Developer account and my hosted Oracle Apex instance.

The idea is simple, you will create the PL/SQL procedure in Oracle, run it and insert data into an Oracle table and a Salesforce custom object simultaneously via SOAP envelope. The SOAP elements will be sent to Salesforce from the PL/SQL block using the APEX_WEB_SERVICE.MAKE_REQUEST package. The data flow direction is from Oracle Apex to Salesforce. To do this, you will create two SOAP envelopes. 

1. One to get authenticated and login to Salesforce, once you login you can get the Salesforce sesionID.

2. And two to send the INSERT (Create in Salesforce parleur) data DML. 

Here is the code you run in Oracle, you don't have to do anything in Salesforce, all you need in Salesforce is to have a custom object to insert the data into, your credentials and obviously your XML envelopes should be written accordingly based on the Salesforce WSDL which you will generate. I have the following code as a PL/SQL anonymous block in an Oracle Apex page process and does the job for me. Just to save you time, you need an Oracle Apex instance with enabled external network calls, unfortunately apex.oracle.com will not let you do that, that was when I checked it last time, you can always get a low cost fully external network enabled Oracle Apex instance from hosting companies, the one I use is Enciva.com


Enough said, here is the code make sure you pass in your Salesforce credentials with the security token.

DECLARE
l_envelope CLOB;
l_envelope_create CLOB;
l_xml XMLTYPE;
l_xml_create XMLTYPE;
v_session_id varchar2(1000);
BEGIN
wwv_flow_api.set_security_group_id;
--
--
-- ENVELOPE LOGIN
l_envelope :='<?xml version="1.0" encoding="utf-8"?>
   <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://
www.w3.org/2001/XMLSchema">
      <soap:Body>
         <login xmlns="urn:enterprise.soap.sforce.com">
            <username>YOUR@SALESFORCEUSERNAME</username>
            <password>YOURPASSWORDANDSECURITYTOKEN</password>
         </login>
      </soap:Body>
   </soap:Envelope>';
 
-- make request
l_xml := apex_web_service.make_request(
p_url => 'http://na7.salesforce.com/services/Soap/c/20',
p_action => 'http://na7.salesforce.com/services/Soap/c/20/login',
p_envelope => l_envelope
);
--
-- ENVELOPE CREATE
-- get session id
-- You will need the salesforce session id , I know this is an ugly way of getting it, write a better one then, I just wanted to get it working

v_session_id := replace(replace(
substr(substr(l_xml.getClobVal(),0,instr(l_xml.getClobVal(),'</sessionId>')),instr(substr(l_xml.getClobVal(),0,instr(l_xml.getClobVal(),'</sessionId>')),'<sessionId>'))
,'<sessionId>',''),'<','');
-- debug
dbms_output.put_line('session_id is: '||v_session_id);
 
l_envelope_create :='<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <soapenv:Header> 
         <ns1:SessionHeader soapenv:mustUnderstand="0" xmlns:ns1="urn:enterprise.soap.sforce.com">  
            <ns2:sessionId xmlns:ns2="urn:enterprise.soap.sforce.com">'||v_session_id||'</ns2:sessionId> 
         </ns1:SessionHeader>
      </soapenv:Header>
      <soapenv:Body> 
         <create xmlns="urn:enterprise.soap.sforce.com">  
            <sObjects xsi:type="ns3:linkstream__c" xmlns:ns3="urn:sobject.enterprise.soap.sforce.com">
               <ns3:wbid__c>'||:P3_WBID ||'</ns3:wbid__c>   
               <ns3:Link__c>'||:P3_LINK ||'</ns3:Link__c>   
               <ns3:Description__c>'||:P3_DESCRIPTION ||'</ns3:Description__c>   
               <ns3:Display_link__c>'||UPPER(:P3_DISPLAY_LINK) ||'</ns3:Display_link__c>   
            </sObjects>  
         </create>
      </soapenv:Body>
   </soapenv:Envelope> ';

dbms_output.put_line(l_envelope_create);

l_xml_create := apex_web_service.make_request(
p_url => 'http://na7.salesforce.com/services/Soap/c/20',
p_action => 'http://na7.salesforce.com/services/Soap/c/20/create',
p_envelope => l_envelope_create
);
dbms_output.put_line(substr(l_xml_create.getClobVal(),1,25500));

END;


As you can see the code executes in one PL/SQL block. You can call the procedure via an Insert Trigger on the Oracle table, or as a 'Page processing' process in Oracle Apex. It ads a link to my force.com sites custom object from my Oracle Apex app. I display this mock force.com sites on the left column in this blog, under 'My force.com apps' section.

Needless to say, there are maybe dozens of easier ways of doing the same thing between Oracle and Salesforce with tools such as CastIron, Talend, Informatica or Apatar. Some are even free. But hey, this is using no tool but just HTTP!




9 comments:

Unknown said...

Hello,

This is a great post. How can w do the integration in other way around like fron Salesforce --> Oracle database tables

Kubilay said...

Hi Venu

Sorry for the late reply. Yes, you can send data from salesforce to Oracle as well.

Oracle in the 11g version can act as a web-server provider as well. That is you can have a WSDL set up in Oracle and then you can access this WSDL as a consumer from any other system. In relation to my Salesforce post above, the configuration I am suggesting here will be reversed, Oracle will be provider of a web-service (WSDL) this time and not Salesforoce. Read the documentation here: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb_web_services.htm#ADXDB5676 on how to setup Oracle Native Web Services.

Your question set me a challenge :-).

I have done Web Service integration with Oracle where Oracle was the web-service provider before, but I interfaced to the Oracle WSDL from PVCS, a version control system, and not Salesforce. When I have some time, I would like to give it a go. So watch this space!

Unknown said...

Hi,
I am a newbie in salesforce and want to integrate BOSS with Salesforce one way i.e data transfer from BOSS----> Salesforce what should I do. What else setups do i need.

Kubilay said...

Hi Attaullah

Sorry for my ignorance, what is BOSS? Is it a database, another application? If it is another application and has an API layer, you can do end-to-end integration using SOAP and REST. If it is another database, and you need to integrate with cloud middleware, Informatica Cloud is a good tool! The choice, really depends on the source and target and their available toolsets.

Regards

Kubilay

God is my Ally! said...

Thanks for the Post. We are using Relational Junction to get the data from Salesforce to the Oracle Database. How can we integrate from the Salesforce Oracle Tables and our Application Tables? What would be the unique ID for joining/mapping the data.

Thanks!
Resh

Kubilay said...

Hi God is my Ally!

Sorry for the late reply.
You have to use primary keys from both source and target. For Salesforce use an unique external id, you can ad one to an object restrospectively with the 'autonumber' from oracle you have to use the primary key. Uniqueness is key for integration, hope it helps.

Regards

Kubilay

Unknown said...

hello,
is it possible to execute pl/sql script from salesforce.com apex?
if yes, then please guide me.

Anonymous said...

Hi @Kubilay.

I have to integrate Oracle with SFDC where Oracle should WSDL Provider.
Do you have any document on this . ANy info would be a great help

Kubilay said...

Hi Anonymous

Yes, you can expose PL/SQL as a webservice and consume it from Salesforce. Look at this. http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_web_services.htm#CHDFGIBD

You basically need to create an Oracle PL/SQL procedure and call it from Salesforce.