4 Replies Latest reply: Jan 14, 2011 1:09 PM by mdrake RSS

    Soap Request with Binary

    Kev
      Hi,

      I'm having major problems sending a SOAP envelope which includes a document in binary. I am trying to communicate with a online fax service called interfax.net which provide web services to send faxes.

      I have succesffully sent a small fax (17,500 bytes) by encoding the document blob content in base64 and then casting to varchar. This is then sent to the interax using http. Now here lies the problem. A typical document that i can send using this scenario is around 15-30kb which is tiny. I need to send much larger documents. When i check the length of the blob it is exceeding 32000 characters and therefore i cannot send the attachment.

      Is there another method of sending large binary files i am not aware of or a workaround to the method i have mentioned.

      Any help will be really appreciated

      Thanks
        • 1. Re: Soap Request with Binary
          mdrake
          Please read the positing guidelines and provide db version information, etc when posting..

          Also, I think we need to see the code you are using..
          • 2. Re: Soap Request with Binary
            Kev
            Oracle 11g Standard Edition Database

            I have created the following procedure which uses the encode function provided in an earlier post

            CREATE OR REPLACE PROCEDURE pr_send_fax(pn_transaction_id OUT VARCHAR2) AS

            -- Variables
            lv_wsdl VARCHAR2(100);
            lv_ws_response xmltype;
            lv_envelope CLOB;
            lbl_blob BLOB;
            lcl_clob CLOB;
            -- Exception Variables
            ln_sqlcode NUMBER;
            lv_sqlerrm VARCHAR2(1000);

            CURSOR cur_get_doc IS SELECT d.blob_content
            FROM purchase_req_document d
            WHERE d.req_sysid = 20;
            BEGIN
            -- Get WSDL URL from logical types if not provided as parameter
            lv_wsdl := 'http://ws.interfax.net/dfs.asmx?wsdl';

            OPEn cur_get_doc;
            FETCH cur_get_doc INTO lbl_blob;
            CLOSE cur_get_doc;

            lcl_clob := base64encode(lbl_blob);
            insert into leetest(env) values(lcl_clob);
            -- Create Soap Request
            lv_envelope := '<?xml version="1.0" encoding="utf-8"?>
            <soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
            <soap:Body>
            <Sendfax xmlns="http://www.interfax.cc">
            <Username>Private Info</Username>
            <Password>Private Info</Password>
            <FaxNumber>Private Info</FaxNumber>
            <FileData>'||lcl_clob||'</FileData>
            <FileType>pdf</FileType>
            </Sendfax>
            </soap:Body>
            </soap:Envelope>';


            -- Call Web Service
            lv_ws_response := pkg_web_services.make_request(p_url => lv_wsdl,
            p_action => 'http://www.interfax.cc/Sendfax',
            p_envelope => lv_envelope);

            pn_transaction_id := pkg_web_services.parse_xml_clob(p_xml => lv_ws_response,
            p_xpath => '//SendfaxResponse/SendfaxResult/text()',
            p_ns => 'xmlns="http://www.interfax.cc"');

            COMMIT;

            END pr_send_fax;





            I am trying to use a make request function


            FUNCTION make_request(p_url IN VARCHAR2,
            p_action IN VARCHAR2 DEFAULT NULL,
            p_version IN VARCHAR2 DEFAULT '1.1',
            p_envelope IN CLOB,
            p_username IN VARCHAR2 DEFAULT NULL,
            p_password IN VARCHAR2 DEFAULT NULL,
            p_proxy_override IN VARCHAR2 DEFAULT NULL,
            p_wallet_path IN VARCHAR2 DEFAULT NULL,
            p_wallet_pwd IN VARCHAR2 DEFAULT NULL,
            p_extra_headers IN wwv_flow_global.vc_arr2 DEFAULT empty_vc_arr)
            RETURN xmltype IS
            l_clob CLOB;
            l_http_req utl_http.req;
            l_http_resp utl_http.resp;
            l_amount BINARY_INTEGER := 32000;
            l_offset INTEGER := 1;
            l_buffer VARCHAR2(32000);
            l_db_charset VARCHAR2(100);
            l_env_lenb INTEGER := 0;
            i INTEGER := 0;
            l_headers wwv_flow_global.vc_arr2;
            l_response VARCHAR2(2000);
            BEGIN

            -- determine database characterset, if not AL32UTF8, conversion will be necessary
            SELECT VALUE
            INTO l_db_charset
            FROM nls_database_parameters
            WHERE parameter = 'NLS_CHARACTERSET';

            -- determine length for content-length header
            LOOP
            EXIT WHEN wwv_flow_utilities.clob_to_varchar2(p_envelope, i * 32767) IS NULL;
            IF l_db_charset = 'AL32UTF8' THEN
            l_env_lenb := l_env_lenb +
            lengthb(wwv_flow_utilities.clob_to_varchar2(p_envelope,
            i * 32767));
            ELSE
            l_env_lenb := l_env_lenb +
            utl_raw.length(utl_raw.convert(utl_raw.cast_to_raw(wwv_flow_utilities.clob_to_varchar2(p_envelope,
            i *
            32767)),
            'american_america.al32utf8',
            'american_america.' ||
            l_db_charset));
            END IF;
            i := i + 1;
            END LOOP;

            -- set a proxy if required
            IF apex_application.g_proxy_server IS NOT NULL AND
            p_proxy_override IS NULL THEN
            utl_http.set_proxy(proxy => apex_application.g_proxy_server);
            ELSIF p_proxy_override IS NOT NULL THEN
            utl_http.set_proxy(proxy => p_proxy_override);
            END IF;

            utl_http.set_persistent_conn_support(TRUE);
            utl_http.set_transfer_timeout(600);

            -- set wallet if necessary
            IF instr(lower(p_url), 'https') = 1 THEN
            utl_http.set_wallet(p_wallet_path, p_wallet_pwd);
            END IF;

            -- begin the request
            IF wwv_flow_utilities.db_version LIKE '9.%' THEN
            l_http_req := utl_http.begin_request(p_url, 'POST', 'HTTP/1.0');
            ELSE
            l_http_req := utl_http.begin_request(p_url, 'POST');
            END IF;

            -- set basic authentication if required
            IF p_username IS NOT NULL THEN
            utl_http.set_authentication(r => l_http_req,
            username => p_username,
            password => p_password,
            scheme => 'Basic',
            for_proxy => FALSE);
            END IF;

            -- set standard HTTP headers for a SOAP request
            utl_http.set_header(l_http_req, 'Proxy-Connection', 'Keep-Alive');
            IF p_version = '1.2' THEN
            utl_http.set_header(l_http_req,
            'Content-Type',
            'application/soap+xml; charset=UTF-8; action="' ||
            p_action || '";');
            ELSE
            utl_http.set_header(l_http_req, 'SOAPAction', p_action);
            utl_http.set_header(l_http_req,
            'Content-Type',
            'text/xml; charset=UTF-8');
            END IF;
            utl_http.set_header(l_http_req, 'Content-Length', l_env_lenb);

            -- set additional headers if supplied, these are separated by a colon (:) as name/value pairs
            FOR i IN 1 .. p_extra_headers.COUNT LOOP
            l_headers := apex_util.string_to_table(p_extra_headers(i));
            utl_http.set_header(l_http_req, l_headers(1), l_headers(2));
            END LOOP;

            -- read the envelope, convert to UTF8 if necessary, then write it to the HTTP request
            BEGIN
            LOOP
            dbms_lob.READ(p_envelope, l_amount, l_offset, l_buffer);
            IF l_db_charset = 'AL32UTF8' THEN
            utl_http.write_text(l_http_req, l_buffer);
            ELSE
            utl_http.write_raw(l_http_req,
            utl_raw.convert(utl_raw.cast_to_raw(l_buffer),
            'american_america.al32utf8',
            'american_america.' ||
            l_db_charset));
            END IF;
            l_offset := l_offset + l_amount;
            l_amount := 32000;
            END LOOP;
            EXCEPTION
            WHEN no_data_found THEN
            NULL;
            END;

            -- get the response
            l_http_resp := utl_http.get_response(l_http_req);

            -- put the response in a clob
            dbms_lob.createtemporary(l_clob, FALSE);
            dbms_lob.OPEN(l_clob, dbms_lob.lob_readwrite);
            BEGIN
            LOOP
            utl_http.read_text(l_http_resp, l_buffer);
            dbms_lob.writeappend(l_clob, length(l_buffer), l_buffer);
            END LOOP;
            EXCEPTION
            WHEN OTHERS THEN
            IF SQLCODE <> -29266 THEN
            RAISE;
            END IF;
            END;

            utl_http.end_response(l_http_resp);

            RETURN xmltype.createxml(l_clob);

            EXCEPTION
            WHEN OTHERS THEN
            IF SQLCODE = -31011 THEN
            -- its not xml
            RETURN NULL;
            END IF;
            END make_request;



            The reason for my post is that i'm hitting a varchar limit. The user ascheffer has kindly been helping me through this issue.

            Thanks

            Kevin
            • 3. Re: Soap Request with Binary
              Marco Gralike
              So Kevin, is this one solved or do you need further assistance? If needed I can ask my colleague Anton (Scheffer) to reply here on this forum (in the case he missed it)...?

              Apparently he didn't Web Services - Send Blob Content

              Edited by: Marco Gralike on Jan 14, 2011 7:54 PM
              • 4. Re: Soap Request with Binary
                mdrake
                Given this function
                create or replace function blobToBase64Whitespace(P_BINARY_CONTENT in BLOB)
                return CLOB
                as
                  V_BASE64_ENCODED_CONTENT CLOB;
                  V_BUFFER_SIZE            BINARY_INTEGER := 19200;
                  V_BYTE_COUNT             BINARY_INTEGER;
                  V_RAW_SEGMENT_IN         RAW(19200);
                  V_RAW_SEGMENT_OUT        RAW(26400);
                  V_TEXT_SEGMENT_OUT       VARCHAR2(32767);
                  V_INPUT_OFFSET           BINARY_INTEGER := 1;  
                  V_BYTES_WRITTEN          BINARY_INTEGER := 0;
                begin
                  DBMS_LOB.createTemporary(V_BASE64_ENCODED_CONTENT,TRUE,DBMS_LOB.SESSION);
                  loop
                    V_BYTE_COUNT := V_BUFFER_SIZE;
                    DBMS_LOB.READ(P_BINARY_CONTENT,V_BYTE_COUNT,V_INPUT_OFFSET, V_RAW_SEGMENT_IN);
                    V_INPUT_OFFSET     := V_INPUT_OFFSET + V_BYTE_COUNT;
                    V_RAW_SEGMENT_OUT  := UTL_ENCODE.BASE64_ENCODE(V_RAW_SEGMENT_IN);
                    V_TEXT_SEGMENT_OUT := UTL_RAW.CAST_TO_VARCHAR2(V_RAW_SEGMENT_OUT);
                    V_BYTES_WRITTEN    := V_BYTES_WRITTEN + LENGTH(V_TEXT_SEGMENT_OUT);
                    DBMS_LOB.WRITEAPPEND(V_BASE64_ENCODED_CONTENT,LENGTH(V_TEXT_SEGMENT_OUT),V_TEXT_SEGMENT_OUT);
                    EXIT WHEN V_INPUT_OFFSET > DBMS_LOB.GETLENGTH(P_BINARY_CONTENT);
                  end loop;
                  return V_BASE64_ENCODED_CONTENT;
                end;
                /
                and

                a table with a the document stored in a BLOB, such as this...
                SQL> --
                SQL> desc DOCUMENT_TABLE
                 Name                                                                                                                                       Null?    Type
                 -------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------------------------
                ------
                 DOCUMENT                                                                                                                                            BLOB
                
                SQL>
                I'd generate the XML this way
                --
                set long 10000000 pages 0 lines 256
                column XML format A256
                --
                set echo off
                set termout off trimspool on
                spool soap.xml
                --
                select xmlElement
                       (
                         "soap:Envelope",
                         xmlAttributes
                         (
                           'http://schemas.xmlsoap.org/soap/envelope/' as "xmlns:soap",
                           'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi",
                           'http://www.w3.org/2001/XMLSchema' as "xmlns:xsd"
                         ),
                         xmlElement
                         (
                           "soap:Body",
                           xmlElement
                           (
                             "SendFax",
                             xmlAttributes
                             (
                               'http://schemas.xmlsoap.org/soap/envelope/' as "xmlns"
                             ),
                             xmlElement("Username",'Private Info'),
                             xmlElement("Password",'Private Info'),
                             xmlElement("FaxNumber",'Private Info'),
                             xmlElement("FileData",BLOBTOBASE64WHITESPACE(DOCUMENT)),
                             xmlElement("Filetype",'image/jpeg')
                           )
                         )
                       ) XML
                  from DOCUMENT_TABLE
                 where rownum < 2
                /    
                --
                spool off
                set echo on
                set termout on
                --
                Whiich will generate something like this
                SQL> --
                SQL> select xmlElement
                  2         (
                  3           "soap:Envelope",
                  4           xmlAttributes
                  5           (
                  6             'http://schemas.xmlsoap.org/soap/envelope/' as "xmlns:soap",
                  7             'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi",
                  8             'http://www.w3.org/2001/XMLSchema' as "xmlns:xsd"
                  9           ),
                 10           xmlElement
                 11           (
                 12             "soap:Body",
                 13             xmlElement
                 14             (
                 15               "SendFax",
                 16               xmlAttributes
                 17               (
                 18                 'http://schemas.xmlsoap.org/soap/envelope/' as "xmlns"
                 19               ),
                 20               xmlElement("Username",'Private Info'),
                 21               xmlElement("Password",'Private Info'),
                 22               xmlElement("FaxNumber",'Private Info'),
                 23               xmlElement("FileData",BLOBTOBASE64WHITESPACE(DOCUMENT)),
                 24               xmlElement("Filetype",'image/jpeg')
                 25             )
                 26           )
                 27         ) XML
                 28    from DOCUMENT_TABLE
                 29   where rownum < 2
                 30  /
                <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><SendFax xmlns="http://schemas.xmlsoap.org/soap/envelope/"><Username>Private
                 Info</Username><Password>Private Info</Password><FaxNumber>Private Info</FaxNumber><FileData>/9j/4VhsRXhpZgAASUkqAAgAAAAMAA8BAgAGAAAAngAAABABAgAVAAAApAAAABIB
                AwABAAAAAQAAABoBBQABAAAAxAAAABsBBQABAAAAzAAAACgBAwABAAAAAgAAADIB
                AgAUAAAA1AAAADsBAgABAAAAAAAAABMCAwABAAAAAgAAAJiCAgABAAAAAAAAAGmH
                BAABAAAAaAEAACWIBAABAAAA1CEAANYoAABDYW5vbgBDYW5vbiBFT1MgNUQgTWFy
                ayBJSQAAAAAAAAAAAAAAAEgAAAABAAAASAAAAAEAAAAyMDA5OjEyOjE2IDIwOjQ3
                ....
                6dqepXcjFIhMrm2Q9P8AdYgn1z1rEuPGN1qUPhDxBorW8elaCy2dzpSKmwBevGMY
                J55z3zmopVY1pcr2PUq0ouKxNd3v+BneHvH/AIh1PxvZ2Wm79sl4zxi1Q+YXPpjn
                8K8v8dyXlhq8qzSub1p2nuHkyWyTkjn/AD+VdadOnpFHDTnRlUq4amtban//2Q==</FileData><Filetype>image/jpeg</Filetype></SendFax></soap:Body></soap:Envelope>
                
                
                SQL> --
                SQL> spool off
                SQL> set echo on
                SQL> set termout on
                SQL> --
                SQL>
                Does this help..