4 Replies Latest reply: Jan 12, 2013 4:27 PM by Mettemusens2 RSS

    BLOB into CDATA in XML - convert to ?

    Mettemusens2
      Hi there

      I'm on 11g

      I have a BLOB containing a gif.

      I would like to send this via en XML file to another site.

      I thought this was was I had to do, but I gueess not ...
      select xmlelement("Image", xmlcdata(UTL_ENCODE.BASE64_ENCODE(bl))) from xx;
      Since I do have an XML on how it's suppsed to look I can see that the svobe is wrong. Here is the code used to generate the BLOB in the first place (from an XML):
      drop table xx;
      create table xx (bl blob , blen number);
      
      declare
       w_xml         XMLTYPE := 
       XMLTYPE('
      <Image name="test" type="gif"><![CDATA[
      R0lGODlhFAAUANUAAP/mIFVACP/iHEA0EP/aEPbOAPrWDOqqAPbKAPK2AOaZAG1Q
      BPK+AP/iGKVtBP/eFPLCAO6yAPrSBFlACP/eGNquAP/mHLKJAKp9BMqNAJllAPrW
      CM6FAPrOALp1APbOBM6ZAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      AAAAAAAAAAAAAAAAACH+HUJ1aWx0IHdpdGggR0lGIE1vdmllIEdlYXIgNC4wACH5
      BAkAACEALAAAAAAUABQAAAbFwJBwSCwajYGk8lgMLC4VBKSCWQSYoQDmYyAQDBIE
      Q3M1TkCGh2AteBgKjMykeTFQBIB8XvBOYMpCCxJqAwN6hRQSDAcLQwF1AoYAhpID
      BAgRDmUBFQR4eqAABAUJGZsInqGgD6QKmxAGn6qimK5CnBIUswB8EAccmxcIsaoC
      owkHmkMLDAUGA58CFKOLCo2OGM0ShQ9fcBEKykQTGQkQCAUFUsgKHnNIGgcRCQkR
      BwcKZFhODhkKChwcWMHiSEkSggiNBAEAOw
      ]]>
      </Image>
      ');
      w_clob clob;
      w_blob blob;
      w_blob_len number;
      procedure save_to_file (p_blob blob, p_filename varchar2, p_dir varchar2 default 'METTE')
      as
          l_file UTL_FILE.FILE_TYPE;
          l_buffer RAW(32767);
          l_amount BINARY_INTEGER := 32767;
          l_pos INTEGER := 1;
      
          l_blob_len INTEGER;
      
      BEGIN
      
          l_blob_len := DBMS_LOB.getlength(p_blob);
          l_pos:= 1;
          -- Open the destination file.
          l_file := UTL_FILE.fopen(p_dir,p_filename,'wb', 32767);
      
          -- Read chunks of the BLOB and write them to the file
          -- until complete.
          WHILE l_pos < l_blob_len LOOP
              DBMS_LOB.read(p_blob, l_amount, l_pos, l_buffer);
              UTL_FILE.put_raw(l_file, l_buffer, TRUE);
              l_pos := l_pos + l_amount;
          END LOOP;
      
          utl_file.fflush(l_file);
          -- Close the file.
          UTL_FILE.fclose(l_file);
      
          EXCEPTION
              WHEN OTHERS THEN
              -- Close the file if something goes wrong.
              IF UTL_FILE.is_open(l_file) THEN
                  UTL_FILE.fclose(l_file);
              END IF;
              RAISE;
      
      end save_to_file;
      
      begin
        SELECT extractvalue(w_xml, '//Image') into w_clob from dual;
        w_blob := to_blob( UTL_ENCODE.BASE64_DECODE( UTL_RAW.CAST_TO_RAW(to_char(w_clob)) ) );
      
        w_blob_len := DBMS_LOB.GETLENGTH(w_blob);
      
        insert into xx values (w_blob, w_blob_len); 
        
        --- skriv filen til OS !
        save_to_file (w_blob,'mette.gif');
        commit;
        end;
      /
      
      select * from xx;
      Can you assist me ?

      Best regards
      Mette
        • 1. Re: BLOB into CDATA in XML - convert to ?
          odie_63
          Hi,

          Binary data such as RAW or BLOB datatypes are supported in SQL/XML functions but the content is implicitly converted to a string of hexadecimal characters.

          This is what happens here since BASE64_ENCODE returns a RAW.

          The solution is convert the RAW output to VARCHAR2 explicitly :
          SQL> set long 5000
          SQL> set lines 300
          SQL> select xmlelement("Image",
            2           xmlattributes('test' as "name", 'gif' as "type")
            3         , xmlcdata(
            4             UTL_RAW.CAST_TO_VARCHAR2(
            5               UTL_ENCODE.BASE64_ENCODE(bl)
            6             )
            7           )
            8         ) as result
            9  from xx;
          
          RESULT
          ------------------------------------------------------------------------------------------------------------------------------------------------------
          
          <Image name="test" type="gif"><![CDATA[R0lGODlhFAAUANUAAP/mIFVACP/iHEA0EP/aEPbOAPrWDOqqAPbKAPK2AOaZAG1Q
          BPK+AP/iGKVtBP/eFPLCAO6yAPrSBFlACP/eGNquAP/mHLKJAKp9BMqNAJllAPrW
          CM6FAPrOALp1APbOBM6ZAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
          AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
          AAAAAAAAAAAAAAAAACH+HUJ1aWx0IHdpdGggR0lGIE1vdmllIEdlYXIgNC4wACH5
          BAkAACEALAAAAAAUABQAAAbFwJBwSCwajYGk8lgMLC4VBKSCWQSYoQDmYyAQDBIE
          Q3M1TkCGh2AteBgKjMykeTFQBIB8XvBOYMpCCxJqAwN6hRQSDAcLQwF1AoYAhpID
          BAgRDmUBFQR4eqAABAUJGZsInqGgD6QKmxAGn6qimK5CnBIUswB8EAccmxcIsaoC
          owkHmkMLDAUGA58CFKOLCo2OGM0ShQ9fcBEKykQTGQkQCAUFUsgKHnNIGgcRCQkR
          BwcKZFhODhkKChwcWMHiSEkSggiNBAEAOw==]]></Image>
          Note that you may hit the SQL size limitation for RAW if you have to handle larger BLOBs.
          You'll then have to switch to PL/SQL, and ultimately to a custom encoding function.
          • 2. Re: BLOB into CDATA in XML - convert to ?
            Mettemusens2
            Hello

            Thanks for the hint - I was close :-)
            I get that I have to code PL/SQL in order to make a CData of more that 2000 bytes.

            Do you have a "skeleton" for that?

            regards
            Mette
            • 3. Re: BLOB into CDATA in XML - convert to ?
              Mettemusens2
              Hello

              Thanks for the hint - I was close :-)
              I get that I have to code PL/SQL in order to make a CData of more that 2000 bytes.

              Do you have a "skeleton" for that?

              This is as far as I got , but I get a conversion error:
              create or replace function b2raw2clob ( b  blob ) return clob
              IS
              pos PLS_INTEGER := 1;
              buffer RAW( 2000 );
              res CLOB;
              stump varchar2(4000);
              lob_len PLS_INTEGER := DBMS_LOB.getLength( b );
              
              BEGIN
              DBMS_LOB.createTemporary( res, TRUE );
              DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
              
              LOOP
              buffer := UTL_RAW.cast_to_raw( DBMS_LOB.SUBSTR( b, 500, pos ) );
              stump := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer));
              DBMS_OUTPUT.PUT_LINE(stump);
              IF UTL_RAW.LENGTH( buffer ) > 0 THEN
                  DBMS_LOB.writeAppend( res,stump, length(stump) );
              END IF;
              
              pos := pos + 1000;
              EXIT WHEN pos > lob_len;
              END LOOP;
              
              RETURN res; -- res is OPEN here
              END b2raw2clob; 
              regards
              Mette

              Edited by: Mettemusens2 on 2013-01-12 14:14
              • 4. Re: BLOB into CDATA in XML - convert to ?
                Mettemusens2
                Well ....

                I think that thos one works
                create or replace function b2raw2clob ( b  blob ) return clob
                IS
                pos PLS_INTEGER := 1;
                buffer RAW( 2000 );
                res CLOB;
                stump varchar2(4000);
                lob_len PLS_INTEGER := DBMS_LOB.getLength( b );
                
                BEGIN
                DBMS_LOB.createTemporary( res, TRUE );
                DBMS_LOB.OPEN( res, DBMS_LOB.LOB_ReadWrite );
                
                LOOP
                
                dbms_output.put_line('inden read');
                buffer :=  DBMS_LOB.SUBSTR( b, 500, pos );
                DBMS_OUTPUT.PUT_LINE(' buffer '|| buffer);
                
                stump := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(buffer));
                DBMS_OUTPUT.PUT_LINE(length(stump)  || '-'|| stump);
                IF LENGTH( stump ) > 0 THEN
                dbms_output.put_line('inden append');
                    res := res || stump;
                END IF;
                
                pos := pos + 500;
                EXIT WHEN pos > lob_len;
                END LOOP;
                
                RETURN res; -- res is OPEN here
                END b2raw2clob;