skurthInnovation und Technologie

Calling a Web service from a Java Stored Procedure

This article is about how to invoke a web service with the help of a Java Stored Procedure in Oracle. To communicate with the web service, we use the Simple Object Access Protocol (SOAP).

But before we write our Java class, we have to check the version of the installed Oracle RDBMS. The condition for the further steps is that our version is 11.2.0.4 or higher. We can prove that by executing the following SQL statement:
2014-03-20-java-web-service-stored-procedure-01

It’s important to check this because lower versions have several problems to invoke a web service via SOAP or to compile Java classes in the database. In these versions are some necessary Java Archives (JARs) missing. So we have to load the required JARs manually into the database if we want to use a lower version. The new and needed feature in Oracle 11.2.0.4 is the upgrade of the system classes to JDK 6.

After we make sure our Oracle version is correct we can start to implement the Java class. In this article we invoke a simple “Hello World” web service. When we finished the implementation, we have to load the Java class into the database.

For example via the console:
2014-03-20-java-web-service-stored-procedure-02

Then we must write a function in the database that invokes the method getResponse of the Java class. The required input variables for the web service call are the desired output variables, the URL of the web service and the XML document, which contains the SOAP Request. If we don’t know how the XML document has to look like, we can use several programs (e.g. SoapUI) to display the structure of these.

At the end we can execute the written function, which creates automatically a PL/SQL function. This PL/SQL function returns the result.

If we execute the code for the first time, it’s possible that we get an error message from Oracle because we haven’t the correct Java permissions. In this case, Oracle shows us the missing permissions and we must grant these to the executing user.

Java class

import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.Reader;
import java.nio.charset.Charset;
import java.sql.Clob;
import java.sql.SQLException;

import javax.xml.soap.MessageFactory;
import javax.xml.soap.MimeHeaders;
import javax.xml.soap.SOAPConnection;
import javax.xml.soap.SOAPConnectionFactory;
import javax.xml.soap.SOAPException;
import javax.xml.soap.SOAPMessage;

public class WebServiceCall {

  /**
   * Coverts a String to a SOAPMessage
   * @param xml
   * @return
   * @throws SOAPException
   * @throws IOException
   */
  private static SOAPMessage getSoapMessageFromString(String xml) throws SOAPException, IOException {
    MessageFactory factory = MessageFactory.newInstance();
    SOAPMessage message = factory.createMessage(new MimeHeaders(), new ByteArrayInputStream(xml.getBytes(Charset.forName("UTF-8"))));
    return message;
  }

  /**
   * Sends a SOAP-Request and receives a SOAP-Response
   * @param outputParameter - Name(s) of the outputvariables
   * @param url - Url of the wsdl
   * @param xml - XML-Document
   * @return
   * @throws Exception
   */
  public static String getResponse(String outputParameterText, String url, Clob clobXML) throws Exception{	        	
    String xml = clobToString(clobXML);	
    String xmlResponse = null;
    String result = "";
    String[] outputParameter = null;
    int outputLength = 0;
  
    if(outputParameterText.isEmpty() == false){
      outputParameter = stringToArray(outputParameterText);			            	
      outputLength = outputParameter.length;
    }
					
    try {
      // Create SOAP Connection
      SOAPConnectionFactory soapConnectionFactory = SOAPConnectionFactory.newInstance();
      SOAPConnection soapConnection = soapConnectionFactory.createConnection();

      //Send SOAP Message to SOAP Server
      SOAPMessage soapResponse = soapConnection.call(getSoapMessageFromString(xml), url);

      if(outputLength != 0){
        //Convert SOAPMessage back to String
        ByteArrayOutputStream baos = null;

        try {
          baos = new ByteArrayOutputStream();
          soapResponse.writeTo(baos); 
          xmlResponse = baos.toString();
        } 
        catch (Exception e) {		                     
          xmlResponse = "Exception during SOAP to String: " + e.toString();
        }
		                        
        // Check if an error exists
        if(xmlResponse.contains("faultcode")){
          String[] beforeFaultcode = xmlResponse.split("");
          String[] afterFaultcode = beforeFaultcode[1].split("");
          result = result + "faultcode: " + afterFaultcode[0] + "; n";
        }
        if(xmlResponse.contains("faultstring")){
          String[] beforeFaultstring = xmlResponse.split("");
          String[] afterFaultstring = beforeFaultstring[1].split("");
          result = result + "faultstring: " + afterFaultstring[0] + "; n";
        }

        // If an error exists
        if(result.isEmpty() == false){
          soapConnection.close();
        }
        // If no error exists
        else{
          // Extract chosen output variables
          for(int i = 0; i < outputLength; i++){
            String[] vorErgebnis = xmlResponse.split("<"+outputParameter[i]+">");
            String[] nachErgebnis = vorErgebnis[1].split("");
            result = result + outputParameter[i] + ": " + nachErgebnis[0] + "; n";
          }
          soapConnection.close();
        }
      }
      else{
        soapConnection.close();
      }                        
    }
    catch (Exception e)
    {
      xmlResponse = "Exception during the SOAP-Connection: " + e.toString();
    }

    // Print the Java error
    if (result.equals("")){
      return xmlResponse;
    }
    // Print the result
    else{		        		
      return result;
    }
  }
	            
  /**
   * Transforms a String to an Array. The separation of an element with ";;"
   * @param transform
   * @return
   */
  public static String[] stringToArray(String transform){
    String[] toArray = transform.split(";;");;
    return toArray;
  }
	        
  /**
   * Converts a CLOB to a String
   * @param data
   * @return
   */
  public static String clobToString(Clob data){
    final StringBuilder sb = new StringBuilder();
    String result = "";	            
    try{
      final Reader         reader = data.getCharacterStream();
      final BufferedReader br     = new BufferedReader(reader);

      int b;
      while(-1 != (b = br.read())){
        sb.append((char)b);
      }
      br.close();
    }
    catch (SQLException e){
      e.printStackTrace();
    }
    catch (IOException e){
      e.printStackTrace();
    }
    return sb.toString();
  }
}

SOAP request

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://ServiceHello/ServiceHelloInterface">
  <soapenv:Header/>
  <soapenv:Body>
    <ser:sayHello>
      <name>World</name>
    </ser:sayHello>
  </soapenv:Body>
</soapenv:Envelope>

Function

create or replace FUNCTION XMLWSCALL (
  outputParameter varchar2, 
  url varchar2, 
  xml CLOB
) 
RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'WebServiceCall.getResponse(java.lang.String, java.lang.String, java.sql.Clob) return java.lang.String';

PL/SQL function

DECLARE
  OUTPUTPARAMETER VARCHAR2(200);
  URL VARCHAR2(200);
  XML CLOB;
  v_Return VARCHAR2(4000);
BEGIN
  OUTPUTPARAMETER := 'output1';
  URL := 'http://localhost:9080/ServiceHelloWeb/sca/ServiceHello?wsdl';
  XML := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ser="http://ServiceHello/ServiceHelloInterface"> 
            <soapenv:Header/> 
            <soapenv:Body> 
   	          <ser:sayHello>
   	 	          <namevWorld</name>
   	 	        </ser:sayHello>
            </soapenv:Bodyv 
          </soapenv:Envelope>';

  v_Return := XMLWSCALL(
    OUTPUTPARAMETER => OUTPUTPARAMETER,
    URL => URL,
    XML => XML
  );

DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);

  :v_Return := v_Return;
--rollback; 
END;

Schlagwörter: ,