9 Replies Latest reply: Feb 6, 2013 6:02 AM by paulj RSS

    XML DB webservice - CLOBs

    paulj
      Hello!

      I'm trying to create a webservice based on a procedure that can take CLOBs as IN-Parameter (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0).

      So far I found out that the XML<=>PLSQL mapping doesn't support CLOBs but ObjectTypes:
      [Oracle® XML DB Developer's Guide|http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb_web_services.htm#CHDGBFID]

      Using ObjectTypes I managed to create a webservice that can send CLOB-Data as response (based on the procedure get_pjtest below), but I can't get CLOB-Data into the DB (see procedure put_pjtest below).

      I basically tried to create a new row using the procedure put_pjtest and the clob-data from the first response (1234567891011...), when I check the CLOB-Column it is only gibberish (strange symbols etc.).

      UPDATE: It works well until ~60000 characters, where is the limit?

      What am I doing wrong? Are there any examples on XML DB webservices used together with CLOBs?

      Thanks a lot
      Paul

      ------------------------------------------------------------------------------------------
      create or replace type pjtestobj as object (
      atext varchar2(100)
      ,aclob clob
      )
      /

      create table pjtests
      (id number(10) not null
      ,pjtest pjtestobj);

      declare
      v_pjtest pjtestobj;
      v_aclob clob;
      begin
      FOR i IN 1 .. 5000 LOOP
      v_aclob := v_aclob || TO_CHAR (i);
      END LOOP;

      v_pjtest := pjtestobj('abc', v_aclob);
      insert into pjtests values (2, v_pjtest);
      commit;
      end;

      create or replace PROCEDURE get_pjtest (
      p_id IN pjtests.id%TYPE,
      p_pjtest OUT pjtestobj) AS
      BEGIN
      select pjtest
      into p_pjtest
      from pjtests
      where id = p_id;
      END;

      create or replace procedure put_pjtest(
      p_id IN pjtests.id%type,
      p_pjtest IN pjtestobj) AS
      BEGIN
      insert into pjtests values(p_id, p_pjtest);
      END;
      --------------------------------------------------------------

      Edited by: paulj on Feb 4, 2013 5:31 PM
        • 1. Re: XML DB webservice - CLOBs
          mdrake
          This works for me
          SQL> --
          SQL> connect sys/oracle as sysdba
          Connected.
          SQL> --
          SQL> set define on
          SQL> set timing on
          SQL> --
          SQL> define USERNAME = WSDLTEST
          SQL> --
          SQL> def PASSWORD = &USERNAME
          SQL> --
          SQL> def WSDLDIR = &1
          SQL> --
          SQL> def USER_TABLESPACE = USERS
          SQL> --
          SQL> def TEMP_TABLESPACE = TEMP
          SQL> --
          SQL> def HOSTNAME = localhost
          SQL> --
          SQL> drop user &USERNAME cascade
            2  /
          old   1: drop user &USERNAME cascade
          new   1: drop user WSDLTEST cascade
          
          User dropped.
          
          Elapsed: 00:00:00.60
          SQL> grant create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &PASSWORD
            2  /
          old   1: grant create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &PASSWORD
          new   1: grant create any directory, drop any directory, connect, resource, alter session, create view to WSDLTEST identified by WSDLTEST
          
          Grant succeeded.
          
          Elapsed: 00:00:00.07
          SQL> alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
            2  /
          old   1: alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
          new   1: alter user WSDLTEST default tablespace USERS temporary tablespace TEMP
          
          User altered.
          
          Elapsed: 00:00:00.01
          SQL> begin
            2    dbms_network_acl_admin.drop_acl('localhost.xml');
            3  end;
            4  /
          begin
          *
          ERROR at line 1:
          ORA-31001: Invalid resource handle or path name "/sys/acls/localhost.xml" 
          ORA-06512: at "XDB.DBMS_XDB", line 355 
          ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN", line 470 
          ORA-06512: at line 2 
          
          
          Elapsed: 00:00:00.00
          SQL> begin
            2  $IF DBMS_DB_VERSION.VER_LE_11_2 $THEN
            3    dbms_network_acl_admin.drop_acl('EnableHttpAccess.xml');
            4  $ELSE
            5    dbms_network_acl_admin.remove_host_ace(
            6        host =>  '&HOSTNAME',
            7        ace  =>  xs$ace_type(privilege_list => xs$name_list('resolve'),
            8                     principal_name => '&USERNAME',
            9                     principal_type => xs_acl.ptype_db));
           10  
           11  $END
           12  end;
           13  /
          old   6:     host =>  '&HOSTNAME',
          new   6:     host =>  'localhost',
          old   8:                        principal_name => '&USERNAME',
          new   8:                        principal_name => 'WSDLTEST',
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:00.01
          SQL> begin
            2  $IF DBMS_DB_VERSION.VER_LE_11_2 $THEN
            3    dbms_network_acl_admin.create_acl('EnableHttpAccess.xml', 'ACL for &HOSTNAME', '&USERNAME', true, 'connect');
            4    dbms_network_acl_admin.assign_acl('EnableHttpAccess.xml', '&HOSTNAME');
            5  $ELSE
            6    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
            7        host => '&HOSTNAME',
            8        ace  =>  xs$ace_type(privilege_list => xs$name_list('connect', 'resolve'),
            9                        principal_name => '&USERNAME',
           10                        principal_type => xs_acl.ptype_db));
           11  $END
           12  end;
           13  /
          old   3:   dbms_network_acl_admin.create_acl('EnableHttpAccess.xml', 'ACL for &HOSTNAME', '&USERNAME', true, 'connect');
          new   3:   dbms_network_acl_admin.create_acl('EnableHttpAccess.xml', 'ACL for localhost', 'WSDLTEST', true, 'connect');
          old   4:   dbms_network_acl_admin.assign_acl('EnableHttpAccess.xml', '&HOSTNAME');
          new   4:   dbms_network_acl_admin.assign_acl('EnableHttpAccess.xml', 'localhost');
          old   7:     host => '&HOSTNAME',
          new   7:     host => 'localhost',
          old   9:                           principal_name => '&USERNAME',
          new   9:                           principal_name => 'WSDLTEST',
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:00.27
          SQL> COMMIT
            2  /
          
          Commit complete.
          
          Elapsed: 00:00:00.40
          SQL> GRANT XDB_WEBSERVICES TO &USERNAME
            2  /
          old   1: GRANT XDB_WEBSERVICES TO &USERNAME
          new   1: GRANT XDB_WEBSERVICES TO WSDLTEST
          
          Grant succeeded.
          
          Elapsed: 00:00:00.01
          SQL> GRANT XDB_WEBSERVICES_OVER_HTTP TO &USERNAME
            2  /
          old   1: GRANT XDB_WEBSERVICES_OVER_HTTP TO &USERNAME
          new   1: GRANT XDB_WEBSERVICES_OVER_HTTP TO WSDLTEST
          
          Grant succeeded.
          
          Elapsed: 00:00:00.00
          SQL> connect &USERNAME/&PASSWORD
          Connected.
          SQL> --
          SQL> VAR TARGET VARCHAR2(4000)
          SQL> --
          SQL> begin
            2            :TARGET := 'WSDLTEST/TEST';
            3  end;
            4  /
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:00.01
          SQL> undef TARGET_NAMESPACE
          SQL> --
          SQL> create type T1 as object (
            2    key VARCHAR2(4),
            3    val CLOB
            4  )
            5  /
          
          Type created.
          
          Elapsed: 00:00:00.03
          SQL> create table MY_TABLE (
            2    Key VARCHAR2(4),
            3    VAL CLOB
            4  )
            5  /
          
          Table created.
          
          Elapsed: 00:00:00.01
          SQL> create or replace procedure TEST (ARG T1)
            2  as
            3  begin
            4            insert into MY_TABLE values (ARG.key, ARG.val);
            5  end;
            6  /
          
          Procedure created.
          
          Elapsed: 00:00:00.05
          SQL> column TARGET_NAMESPACE new_value TARGET_NAMESPACE
          SQL> select 'http://xmlns.oracle.com/orawsv/' || :TARGET TARGET_NAMESPACE
            2    from dual
            3  /
          
          TARGET_NAMESPACE                                                                
          --------------------------------------------------------------------------------
          http://xmlns.oracle.com/orawsv/WSDLTEST/TEST                                    
          
          Elapsed: 00:00:00.00
          SQL> create or replace DIRECTORY WSDLDIR as '&WSDLDIR'
            2  /
          old   1: create or replace DIRECTORY WSDLDIR as '&WSDLDIR'
          new   1: create or replace DIRECTORY WSDLDIR as '/home/oracle/xdb/examples/DBNWS'
          
          Directory created.
          
          Elapsed: 00:00:00.01
          SQL> VAR URL VARCHAR2(1024)
          SQL> --
          SQL> show errors
          No errors.
          SQL> --
          SQL> set long 10000000 pages 0 lines 160
          SQL> --
          SQL> BEGIN
            2    :URL := 'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/' || :TARGET;
            3  end;
            4  /
          old   2:   :URL := 'http://&USERNAME:&PASSWORD@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/' || :TARGET;
          new   2:   :URL := 'http://WSDLTEST:WSDLTEST@localhost:' || dbms_xdb.getHttpPort() || '/orawsv/' || :TARGET;
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:00.01
          SQL> print url
          http://WSDLTEST:WSDLTEST@localhost:8080/orawsv/WSDLTEST/TEST                                                                                                    
          
          SQL> --
          SQL> select httpuritype( :URL || '?wsdl' ).getXML()
            2    from dual
            3  /
          <definitions name="TEST" targetNamespace="http://xmlns.oracle.com/orawsv/WSDLTEST/TEST" xmlns="http://schemas.xmlsoap.org/wsdl/" xmlns:tns="http://xmlns.oracle.
          com/orawsv/WSDLTEST/TEST" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">                                      
            <types>                                                                                                                                                       
              <xsd:schema targetNamespace="http://xmlns.oracle.com/orawsv/WSDLTEST/TEST" elementFormDefault="qualified">                                                  
                <xsd:element name="TESTInput">                                                                                                                            
                  <xsd:complexType>                                                                                                                                       
                    <xsd:sequence>                                                                                                                                        
                      <xsd:element name="ARG-T1-CIN" type="tns:T1Type"/>                                                                                                  
                    </xsd:sequence>                                                                                                                                       
                  </xsd:complexType>                                                                                                                                      
                </xsd:element>                                                                                                                                            
                <xsd:element name="TESTOutput">                                                                                                                           
                  <xsd:complexType>                                                                                                                                       
                    <xsd:sequence>                                                                                                                                        
                      </xsd:sequence>                                                                                                                                     
                  </xsd:complexType>                                                                                                                                      
                </xsd:element>                                                                                                                                            
                <xsd:complexType name="T1Type">                                                                                                                           
                  <xsd:sequence>                                                                                                                                          
                    <xsd:element name="T1">                                                                                                                               
                      <xsd:complexType>                                                                                                                                   
                        <xsd:sequence>                                                                                                                                    
                          <xsd:element name="KEY">                                                                                                                        
                            <xsd:simpleType>                                                                                                                              
                              <xsd:restriction base="xsd:string">                                                                                                         
                                <xsd:maxLength value="4"/>                                                                                                                
                              </xsd:restriction>                                                                                                                          
                            </xsd:simpleType>                                                                                                                             
                          </xsd:element>                                                                                                                                  
                          <xsd:element name="VAL" type="xsd:string"/>                                                                                                     
                        </xsd:sequence>                                                                                                                                   
                      </xsd:complexType>                                                                                                                                  
                    </xsd:element>                                                                                                                                        
                  </xsd:sequence>                                                                                                                                         
                </xsd:complexType>                                                                                                                                        
              </xsd:schema>                                                                                                                                               
            </types>                                                                                                                                                      
            <message name="TESTInputMessage">                                                                                                                             
              <part name="parameters" element="tns:TESTInput"/>                                                                                                           
            </message>                                                                                                                                                    
            <message name="TESTOutputMessage">                                                                                                                            
              <part name="parameters" element="tns:TESTOutput"/>                                                                                                          
            </message>                                                                                                                                                    
            <portType name="TESTPortType">                                                                                                                                
              <operation name="TEST">                                                                                                                                     
                <input message="tns:TESTInputMessage"/>                                                                                                                   
                <output message="tns:TESTOutputMessage"/>                                                                                                                 
              </operation>                                                                                                                                                
            </portType>                                                                                                                                                   
            <binding name="TESTBinding" type="tns:TESTPortType">                                                                                                          
              <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>                                                                           
              <operation name="TEST">                                                                                                                                     
                <soap:operation soapAction="TEST"/>                                                                                                                       
                <input>                                                                                                                                                   
                  <soap:body parts="parameters" use="literal"/>                                                                                                           
                </input>                                                                                                                                                  
                <output>                                                                                                                                                  
                  <soap:body parts="parameters" use="literal"/>                                                                                                           
                </output>                                                                                                                                                 
              </operation>                                                                                                                                                
            </binding>                                                                                                                                                    
            <service name="TESTService">                                                                                                                                  
              <documentation>Oracle Web Service</documentation>                                                                                                           
              <port name="TESTPort" binding="tns:TESTBinding">                                                                                                            
                <soap:address location="http://localhost:8080/orawsv/WSDLTEST/TEST"/>                                                                                     
              </port>                                                                                                                                                     
            </service>                                                                                                                                                    
          </definitions>                                                                                                                                                  
                                                                                                                                                                          
          
          Elapsed: 00:00:00.07
          SQL> select XMLTransform( httpuritype(:URL || '?wsdl' ).getXML(), XMLTYPE(bfilename('WSDLDIR','wsdl2Request.xsl'),nls_charset_id('AL23UTF8')))
            2    from dual
            3  /
          <?xml version="1.0" encoding="US-ASCII"?>                                                                                                                       
          <Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">                                                                                                    
            <Body>                                                                                                                                                        
              <TESTInput xmlns="http://xmlns.oracle.com/orawsv/WSDLTEST/TEST">                                                                                            
                <ARG-T1-CIN/>                                                                                                                                             
              </TESTInput>                                                                                                                                                
            </Body>                                                                                                                                                       
          </Envelope>                                                                                                                                                     
                                                                                                                                                                          
          
          Elapsed: 00:00:00.04
          SQL> VAR RESULT CLOB
          SQL> VAR RESPONSE CLOB
          SQL> VAR REQUEST CLOB
          SQL> --
          SQL> DECLARE
            2    V_SOAP_REQUEST        XMLTYPE;
            3    V_SOAP_REQUEST_TEXT CLOB;
            4    V_REQUEST        UTL_HTTP.REQ;
            5    V_RESPONSE        UTL_HTTP.RESP;
            6    V_BUFFER         VARCHAR2(32000);
            7    V_RESPONSE_TEXT        CLOB;
            8    V_RESPONSE_XML        XMLTYPE;
            9    V_WSDL             XMLTYPE;
           10  BEGIN
           11            DBMS_LOB.CREATETEMPORARY(V_RESPONSE_TEXT, TRUE);
           12  
           13            select XMLTransform( httpuritype(:URL || '?wsdl' ).getXML(), XMLTYPE(bfilename('WSDLDIR','wsdl2Request.xsl'),nls_charset_id('AL23UTF8')))
           14              into V_SOAP_REQUEST
           15              from dual;
           16  
           17  
           18            select XMLQUERY(
           19                  'declare namespace soapenv = "http://schemas.xmlsoap.org/soap/envelope/"; (: :)
           20                   declare namespace tns      = "&TARGET_NAMESPACE"; (: :)
           21             copy $NEWXML := $XML modify (
           22                        for $TARGET in $NEWXML/soapenv:Envelope/soapenv:Body/tns:TESTInput
           23                           return replace node $TARGET with element tns:TESTInput { element tns:ARG-T1-CIN { element tns:T1 {element tns:KEY {2}, element tns:VAL {"AAA"} } } }
           24                         )
           25                return $NEWXML'
           26                passing V_SOAP_REQUEST as "XML"
           27                returning content
           28             )
           29        into V_SOAP_REQUEST
           30        from dual;
           31  
           32    select XMLSERIALIZE(DOCUMENT V_SOAP_REQUEST as CLOB)
           33        into V_SOAP_REQUEST_TEXT
           34        from DUAL;
           35  
           36    :REQUEST := V_SOAP_REQUEST_TEXT;
           37  
           38    begin
           39        V_REQUEST := UTL_HTTP.BEGIN_REQUEST(URL => :URL, METHOD => 'POST');
           40        UTL_HTTP.SET_HEADER(V_REQUEST, 'User-Agent', 'Mozilla/4.0');
           41        V_REQUEST.METHOD := 'POST';
           42        UTL_HTTP.SET_HEADER (R => V_REQUEST, NAME => 'Content-Length', VALUE => DBMS_LOB.GETLENGTH(V_SOAP_REQUEST_TEXT));
           43        UTL_HTTP.WRITE_TEXT (R => V_REQUEST, DATA => V_SOAP_REQUEST_TEXT);
           44        V_RESPONSE := UTL_HTTP.GET_RESPONSE(V_REQUEST);
           45         LOOP
           46          UTL_HTTP.READ_LINE(V_RESPONSE, V_BUFFER, TRUE);
           47          if (LENGTH(V_BUFFER) > 0) then
           48            DBMS_LOB.WRITEAPPEND(V_RESPONSE_TEXT,LENGTH(V_BUFFER),V_BUFFER);
           49          end if;
           50         END LOOP;
           51         UTL_HTTP.END_RESPONSE(V_RESPONSE);
           52    EXCEPTION
           53        WHEN UTL_HTTP.END_OF_BODY THEN
           54          UTL_HTTP.END_RESPONSE(V_RESPONSE);
           55    END;
           56  
           57    :RESPONSE := V_RESPONSE_TEXT;
           58  
           59    V_RESPONSE_XML := XMLTYPE(V_RESPONSE_TEXT);
           60  
           61    select XMLQUERY
           62             (
           63            'declare namespace SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"; (::)
           64             declare namespace SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/"; (::)
           65             declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance"; (::)
           66             declare namespace xsd="http://www.w3.org/2001/XMLSchema"; (::)
           67             declare namespace m="&TARGET_NAMESPACE"; (::)
           68             $resp/SOAP-ENV:Envelope/SOAP-ENV:Body/m:TEST_CLOBOutput//m:RETURN/text()'
           69             passing V_RESPONSE_XML as "resp" returning content
           70             )
           71        into V_WSDL
           72        from DUAL;
           73  
           74    select V_WSDL.getClobVal()
           75        into :RESULT
           76        from dual;
           77  
           78            DBMS_LOB.FREETEMPORARY(V_RESPONSE_TEXT);
           79  
           80  END;
           81  /
          old  20:                declare namespace tns     = "&TARGET_NAMESPACE"; (: :)
          new  20:                declare namespace tns     = "http://xmlns.oracle.com/orawsv/WSDLTEST/TEST"; (: :)
          old  67:             declare namespace m="&TARGET_NAMESPACE"; (::)
          new  67:             declare namespace m="http://xmlns.oracle.com/orawsv/WSDLTEST/TEST"; (::)
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:00.07
          SQL> select XMLPARSE(DOCUMENT :REQUEST WELLFORMED)
            2    from dual
            3  /
          <?xml version="1.0" encoding="US-ASCII"?>                                                                                                                       
          <Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">                                                                                                    
            <Body>                                                                                                                                                        
              <tns:TESTInput xmlns:tns="http://xmlns.oracle.com/orawsv/WSDLTEST/TEST">                                                                                    
                <tns:ARG-T1-CIN>                                                                                                                                          
                  <tns:T1>                                                                                                                                                
                    <tns:KEY>2</tns:KEY>                                                                                                                                  
                    <tns:VAL>AAA</tns:VAL>                                                                                                                                
                  </tns:T1>                                                                                                                                               
                </tns:ARG-T1-CIN>                                                                                                                                         
              </tns:TESTInput>                                                                                                                                            
            </Body>                                                                                                                                                       
          </Envelope>                                                                                                                                                     
                                                                                                                                                                          
          
          Elapsed: 00:00:00.00
          SQL> select XMLPARSE(DOCUMENT :RESPONSE WELLFORMED)
            2    from dual
            3  /
          <?xml version="1.0"?>                                                                                                                                           
          <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">                                                                                          
            <soap:Body>                                                                                                                                                   
              <TESTOutput xmlns="http://xmlns.oracle.com/orawsv/WSDLTEST/TEST">    </TESTOutput>                                                                          
            </soap:Body>                                                                                                                                                  
          </soap:Envelope>                                                                                                                                                
                                                                                                                                                                          
          
          Elapsed: 00:00:00.01
          SQL> select XMLPARSE(DOCUMENT :RESULT WELLFORMED)
            2    from dual
            3  /
                                                                                                                                                                          
          
          Elapsed: 00:00:00.00
          SQL> select *
            2    from MY_TABLE
            3  /
          2    AAA                                                                                                                                                        
          
          Elapsed: 00:00:00.00
          SQL> exit
          • 2. Re: XML DB webservice - CLOBs
            paulj
            Thank you mdrake for your super detailed answer!

            It works for me too now, but only until somewhere between 60000 and 70000 characters for the clob.

            Do you know of any limits/restrictions regarding the length of a clob and/or the size of the soap message?

            BR Paul
            • 3. Re: XML DB webservice - CLOBs
              mdrake
              Between 60000 and 70000 sounds awlfully like 64K to me..

              I suspect we have not enabled handling text nodes over 64K somewhere in the DBNWS code..
              • 4. Re: XML DB webservice - CLOBs
                odie_63
                paulj wrote:
                It works for me too now, but only until somewhere between 60000 and 70000 characters for the clob.
                What are you using to call the webservice and send the CLOB?
                • 5. Re: XML DB webservice - CLOBs
                  paulj
                  @mdrake: I was thinking the same after doing some googling around, but I also stumbled upon the following document

                  [http://docs.oracle.com/cd/B28359_01/appdev.111/b28369/whatsnew.htm]

                  saying

                  "The previous 64K limit on text nodes and attribute values has been lifted."

                  couldn't find out if the same is true for webservices though...
                  • 6. Re: XML DB webservice - CLOBs
                    paulj
                    @odie_63: for testing soapUI 4.5.0 at the moment
                    • 7. Re: XML DB webservice - CLOBs
                      odie_63
                      I confirm the boundary is at 64k : 65534 works, 65535 fails (corrupted content).

                      After a few timeout errors, I managed to get it work for content over 64k by using an XMLType wrapper instead of a UDT :
                      create or replace procedure saveLobContent (p_wrapper in xmltype) is
                      begin
                      
                        insert into tmp_xml values (p_wrapper);
                      
                      end;
                      SQL> DECLARE
                        2    req_text   clob :=
                        3  '<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
                        4    <soap:Body>
                        5      <SAVELOBCONTENTInput xmlns="http://xmlns.oracle.com/orawsv/DEV/SAVELOBCONTENT">
                        6        <P_WRAPPER-XMLTYPE-IN>
                        7          <LobContent>' || rpad(to_clob('X'),80000,'X') || '</LobContent>
                        8        </P_WRAPPER-XMLTYPE-IN>
                        9      </SAVELOBCONTENTInput>
                       10    </soap:Body>
                       11  </soap:Envelope>';
                       12  
                       13    req        utl_http.req;
                       14    res        utl_http.resp;
                       15    buf        varchar2(32767);
                       16    reqlen     pls_integer;
                       17    amt        pls_integer := 32767;
                       18    offs       pls_integer := 1;
                       19  
                       20  BEGIN
                       21  
                       22    req := utl_http.begin_request('http://localhost:8080/orawsv/DEV/SAVELOBCONTENT', 'POST', 'HTTP/1.1');
                       23    utl_http.set_authentication(req, 'DEV', 'dev');
                       24    utl_http.set_header(req, 'Transfer-Encoding', 'chunked');
                       25    utl_http.set_header(req, 'Content-Type', 'text/xml');
                       26  
                       27    reqlen := dbms_lob.getlength(req_text);
                       28    utl_http.set_header(req, 'Content-Length', reqlen);
                       29  
                       30    while (offs < reqlen) loop
                       31      dbms_lob.read(req_text, amt, offs, buf);
                       32      utl_http.write_text(req, buf);
                       33      offs := offs + amt;
                       34    end loop;
                       35  
                       36    res := utl_http.get_response(req);
                       37    utl_http.read_text(res, buf);
                       38    dbms_output.put_line(buf);
                       39  
                       40    utl_http.end_response(res);
                       41  
                       42  END;
                       43  /
                       
                      <?xml version="1.0" ?>
                      <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
                        <soap:Body>
                          <SAVELOBCONTENTOutput xmlns="http://xmlns.oracle.com/orawsv/DEV/SAVELOBCONTENT">
                          </SAVELOBCONTENTOutput>
                        </soap:Body>
                      </soap:Envelope>
                       
                      PL/SQL procedure successfully completed
                       
                      SQL> 
                      SQL> select length(x.lobcontent)
                        2  from tmp_xml
                        3     , xmltable('/LobContent' passing object_value
                        4         columns lobcontent clob path '.'
                        5       ) x ;
                       
                      LENGTH(X.LOBCONTENT)
                      --------------------
                                     80000
                       
                      • 8. Re: XML DB webservice - CLOBs
                        mdrake
                        Filed bug 16286466. Contact oracle support to open an tar if you need to fix. But be-warned the fix maybe not support for more than 64K.
                        • 9. Re: XML DB webservice - CLOBs
                          paulj
                          Thanks a lot for your help, guys!!

                          odie_63's workaround using an XMLType wrapper solves my issue.

                          BR Paul