cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling PI Webservice from Oracle Procedure

Former Member

on ‎06-17-2014 4:02 AM

0 Kudos

I am trying to call a PI web service from  Oracle Procedure. I am able to test it from SOAP UI Client. But when calling from Oracle, exception is thrown.

Procedure -:

/* Formatted on 2014/06/16 20:38 (Formatter Plus v4.8.8) */

CREATE OR REPLACE PROCEDURE cdba.p_init_ws_transaction

AS

  v_soap_request_text  CLOB;

  v_request            UTL_HTTP.req;

  v_response            UTL_HTTP.resp;

  v_buffer              VARCHAR2 (32767);

  v_response_xml        CLOB;

  l_soap_env            XMLTYPE;

  l_soap_body          XMLTYPE;

  ls_ematn              VARCHAR2 (50);

  ls_test              VARCHAR2 (1000);

  s_namespace          VARCHAR2 (1000);

  l_namespace          VARCHAR2 (50);

  l_raw_data            RAW (512);

  l_clob_response      CLOB;

  l_buffer_size        NUMBER (10)      := 512;

  l_substring_msg      VARCHAR2 (512);

--  l_napaka_opis        VARCHAR2 (1000);

--  l_napaka_koda        VARCHAR2 (100);

--  l_tmp                VARCHAR2 (100);

BEGIN

--  --nastavimo certifikat in geslo

--  UTL_HTTP.set_wallet (g_lokacija_certifikata, g_geslo);

  --nastavimo soap request:

  v_soap_request_text :=

      ' <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:amp="http://tsl/mm/100064/AMPLDetails">

  <soapenv:Header/>

  <soapenv:Body>

      <amp:MT_AMPLDetails_Request>

              <row>

  <BMATN>0008C0053</BMATN>

  </row>

      </amp:MT_AMPLDetails_Request>

  </soapenv:Body>

</soapenv:Envelope>';

  SELECT XMLTYPE (v_soap_request_text).EXTRACT ('//row/BMATN/text()',

                                                s_namespace || l_namespace

                                                ).getstringval ()

    INTO ls_test

    FROM DUAL;

  DBMS_OUTPUT.put_line (ls_test);

  v_request :=

      UTL_HTTP.begin_request

        (url        => 'http://176.0.11.76:50000/XISOAPAdapter/MessageServlet?senderParty=&senderService=SI_OS_AMPLDetails&r...',

          method      => 'POST'

        );

  --UTL_HTTP.set_header (v_request, 'User-Agent', 'Mozilla/4.0');

  --v_request.method := 'POST';

  UTL_HTTP.set_body_charset (v_request, 'UTF8');

  UTL_HTTP.set_header (r          => v_request,

                        NAME      => 'Content-Length',

                        VALUE      => DBMS_LOB.getlength (v_soap_request_text)

                      );

  UTL_HTTP.set_header (r          => v_request,

                        NAME      => 'SOAPAction',

                        VALUE      => 'http://sap.com/xi/WebService/soap1.1'

                      );

  UTL_HTTP.set_authentication (v_request, '157769', 'tatasap@89');

  UTL_HTTP.write_text (r => v_request, DATA => v_soap_request_text);

  <<request_loop>>

  FOR i IN 0 .. CEIL (LENGTH (v_soap_request_text) / l_buffer_size) - 1

  LOOP

      l_substring_msg :=

          SUBSTR (v_soap_request_text, i * l_buffer_size + 1, l_buffer_size);

      DBMS_OUTPUT.put_line (l_substring_msg);

      BEGIN

        l_raw_data := UTL_RAW.cast_to_raw (l_substring_msg);

        UTL_HTTP.write_raw (r => v_request, DATA => l_raw_data);

      EXCEPTION

        WHEN NO_DATA_FOUND

        THEN

            EXIT request_loop;

      END;

  END LOOP request_loop;

  v_response := UTL_HTTP.get_response (v_request);

  BEGIN

      --convert the response to a CLOB

      <<response_loop>>

      LOOP

        DBMS_OUTPUT.put_line ('1');

        UTL_HTTP.read_raw (v_response, l_raw_data, l_buffer_size);

        l_clob_response :=

                    l_clob_response || UTL_RAW.cast_to_varchar2 (l_raw_data);

        DBMS_OUTPUT.put_line (UTL_RAW.cast_to_varchar2 (l_raw_data));

      END LOOP response_loop;

  EXCEPTION

      WHEN UTL_HTTP.end_of_body

      THEN

        UTL_HTTP.end_response (v_response);

  END;

  DBMS_OUTPUT.put_line ('done');

  DBMS_OUTPUT.put_line ('done');

  s_namespace :=

      'xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema/" ';

  l_namespace := 'xmlns="http://tempuri.org/"';

--  SELECT XMLTYPE (l_clob_response).getstringval ()

--    INTO ls_test

--    FROM DUAL;

  SELECT XMLTYPE (l_clob_response).EXTRACT ('//record/EMATN/text()',

                                            s_namespace || l_namespace

                                            ).getstringval ()

    INTO ls_test

    FROM DUAL;

  DBMS_OUTPUT.put_line (ls_test);

EXCEPTION

  WHEN OTHERS

  THEN

      DBMS_OUTPUT.put_line ('exception block');

      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);

      UTL_HTTP.end_response (v_response);

      DBMS_OUTPUT.put_line (SQLERRM);

--      p_napaka := SQLERRM;

--      p_url_megapos := NULL;

END;                                              -- end p_init_ws_transaction

/

        

Output -:

0008C0053

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:amp="http://tsl/mm/100064/AMPLDetails">

  <soapenv:Header/>

  <soapenv:Body>

      <amp:MT_AMPLDetails_Request>

              <row>

  <BMATN>0008C0053</BMATN>

  </row>

      </amp:MT_AMPLDetails_Request>

  </soapenv:Body>

</soapenv:Envelope>

1

<?xml version='1.0'?>

<!-- see the documentation -->

<SOAP:Envelope xmlns:SOAP='http://schemas.xmlsoap.org/soap/envelope/'>

  <SOAP:Body>

    <SOAP:Fault>

      <faultcode>SOAP:Server</faultcode>

      <faultstring>Server Error</faultstring>

      <detail>

        <s:SystemError xmlns:s='http://sap.com/xi/WebService/xi2.0'>

          <context>XIAdapter</context>

          <code>ADAPTER.JAVA_EXCEPTION</code>

          <text><![CDATA[

java.lang.NullPointerException: while trying to invoke the method java.lang

1

.String.indexOf(int) of a null object loaded from local variable 'ctype'

  at com.sap.aii.af.sdk.xi.net.MIMEInputSource.decodeContentType(MIMEInputSource.java:456)

  at com.sap.aii.af.sdk.xi.net.MIMEInputSource.readBody(MIMEInputSource.java:351)

  at com.sap.aii.af.sdk.xi.net.MIMEServletInputSource.parse(MIMEServletInputSource.java:58)

  at com.sap.aii.adapter.soap.web.MessageServlet.doPost(MessageServlet.java:343)

  at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)

  at javax.servlet.http.HttpServl

1

et.service(HttpServlet.java:847)

  at com.sap.engine.services.servlets_jsp.server.Invokable.invoke(Invokable.java:152)

  at com.sap.engine.services.servlets_jsp.server.Invokable.invoke(Invokable.java:38)

  at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.runServlet(HttpHandlerImpl.java:457)

  at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.handleRequest(HttpHandlerImpl.java:210)

  at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:441)

  at

1

com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:430)

  at com.sap.engine.services.servlets_jsp.filters.DSRWebContainerFilter.process(DSRWebContainerFilter.java:38)

  at com.sap.engine.services.httpserver.chain.AbstractChain.process(AbstractChain.java:78)

  at com.sap.engine.services.servlets_jsp.filters.ServletSelector.process(ServletSelector.java:81)

  at com.sap.engine.services.httpserver.chain.AbstractChain.process(AbstractChain.java:78)

  at com.sap.engine.services

1

.servlets_jsp.filters.ApplicationSelector.process(ApplicationSelector.java:276)

  at com.sap.engine.services.httpserver.chain.AbstractChain.process(AbstractChain.java:78)

  at com.sap.engine.services.httpserver.filters.WebContainerInvoker.process(WebContainerInvoker.java:81)

  at com.sap.engine.services.httpserver.chain.HostFilter.process(HostFilter.java:9)

  at com.sap.engine.services.httpserver.chain.AbstractChain.process(AbstractChain.java:78)

  at com.sap.engine.services.httpserver.filters.ResponseLogWriter.

1

process(ResponseLogWriter.java:60)

  at com.sap.engine.services.httpserver.chain.HostFilter.process(HostFilter.java:9)

  at com.sap.engine.services.httpserver.chain.AbstractChain.process(AbstractChain.java:78)

  at com.sap.engine.services.httpserver.filters.DefineHostFilter.process(DefineHostFilter.java:27)

  at com.sap.engine.services.httpserver.chain.ServerFilter.process(ServerFilter.java:12)

  at com.sap.engine.services.httpserver.chain.AbstractChain.process(AbstractChain.java:78)

  at com.sap.engine.services.

1

httpserver.filters.MonitoringFilter.process(MonitoringFilter.java:29)

  at com.sap.engine.services.httpserver.chain.ServerFilter.process(ServerFilter.java:12)

  at com.sap.engine.services.httpserver.chain.AbstractChain.process(AbstractChain.java:78)

  at com.sap.engine.services.httpserver.filters.SessionSizeFilter.process(SessionSizeFilter.java:26)

  at com.sap.engine.services.httpserver.chain.ServerFilter.process(ServerFilter.java:12)

  at com.sap.engine.services.httpserver.chain.AbstractChain.process(AbstractC

1

hain.java:78)

  at com.sap.engine.services.httpserver.filters.MemoryStatisticFilter.process(MemoryStatisticFilter.java:57)

  at com.sap.engine.services.httpserver.chain.ServerFilter.process(ServerFilter.java:12)

  at com.sap.engine.services.httpserver.chain.AbstractChain.process(AbstractChain.java:78)

  at com.sap.engine.services.httpserver.filters.DSRHttpFilter.process(DSRHttpFilter.java:43)

  at com.sap.engine.services.httpserver.chain.ServerFilter.process(ServerFilter.java:12)

  at com.sap.engine.services.http

1

server.chain.AbstractChain.process(AbstractChain.java:78)

  at com.sap.engine.services.httpserver.server.Processor.chainedRequest(Processor.java:475)

  at com.sap.engine.services.httpserver.server.Processor$FCAProcessorThread.process(Processor.java:269)

  at com.sap.engine.services.httpserver.server.rcm.RequestProcessorThread.run(RequestProcessorThread.java:56)

  at com.sap.engine.core.thread.execution.Executable.run(Executable.java:122)

  at com.sap.engine.core.thread.execution.Executable.run(Executable.java:10

1

1)

  at com.sap.engine.core.thread.execution.CentralExecutor$SingleThread.run(CentralExecutor.java:328)

          ]]></text>

        </s:SystemError>

      </detail>

    </SOAP:Fault>

  </SOAP:Body>

</SOAP:Envelope>

1

done

done

Please help.

P_INIT_WS_TRANSACTION.txt.zip
Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (3)

Answers (3)

gerald_kilgus
Explorer
‎03-09-2015 4:46 PM
0 Kudos

Hi,

could it be that you use HTTP GET to send the message. The SOAP adapter only supports POST.

Cheers

Gerald

Show replies
Show replies
manigram
Active Participant
‎06-17-2014 1:11 PM
0 Kudos

Hi,

As it's returning fault error, so there is problem with your data (input values).

Error Line : String.indexOf(int) of a null object loaded from local variable 'ctype'

For example you declared variable as integer and you are sending values like long int or string or date value. So check the input data.

Regards,

Manigandan

Show replies
Show replies
Former Member
‎06-17-2014 5:23 AM
0 Kudos

Hi Chandra,

Issue may be with user Authentication. Crosscheck the "sap-user" and "sap-password" once again.

If UserId & Password is ok, try to append the "sap-user" & "sap-password" to SOAP URL.

URL Format:

http://<server:5xxxx>/XISOAPAdapter/MessageServlet?channel=:<bs>:<soap sender channel>&sap-user=<username>&sap-password=<password>

Regards,

Krupa

Show replies
Show replies
Former Member
‎06-17-2014 11:44 AM
0 Kudos

Hello

Thanks for the reply.

URL that I  am using now is -:

http://176.0.11.76:50000/XISOAPAdapter/MessageServlet?senderParty=&senderService=Service_AMPLDetails...

But the output comes as -:

Message Servlet is in Status OK

Status information:

Servlet com.sap.aii.adapter.soap.web.MessageServlet (Version $Id: //tc/xpi.adapters/NW730EXT_07_REL/src/_soap_application_web_module/webm/api/com/sap/aii/adapter/soap/web/MessageServlet.java#1 $) bound to /MessageServlet

Classname ModuleProcessor: null

Lookupname for localModuleProcessorLookupName: localejbs/ModuleProcessorBean

Lookupname for remoteModuleProcessorLookupName: null

ModuleProcessorClass not instantiated

ModuleProcessorLocal is Instance of com.sun.proxy.$Proxy610

ModuleProcessorRemote not instantiated



Also to clarify, sap-user & sap-password is of PI user or ECC user???

Former Member
‎06-17-2014 12:47 PM
0 Kudos

Hi Chandra,

"Message Servlet is in Status OK" means it is hitting the server.

Use PI user.

Regards,

Krupa

Ask a Question