This discussion is archived
9 Replies Latest reply: Feb 6, 2013 4:02 AM by paulj RSS

XML DB webservice - CLOBs

paulj Explorer
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    @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 Explorer
    Currently Being Moderated
    @odie_63: for testing soapUI 4.5.0 at the moment
  • 7. Re: XML DB webservice - CLOBs
    odie_63 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Thanks a lot for your help, guys!!

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

    BR Paul

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points