8 Replies Latest reply on Oct 25, 2018 7:35 PM by Vijey Chinnaraj Anthony

    i need to convert field blob to xs:base64binary with pl*sql

    2927248

      Hello.
      I would need to convert a field of type BLOB to an XML format field base64binary using the PL * SQL

      Has anyone faced the problem?

      Tks
      Marco

        • 1. Re: i need to convert field blob to xs:base64binary with pl*sql
          odie_63

          2927248 wrote:


          I would need to convert a field of type BLOB to an XML format field base64binary using the PL * SQL

          Has anyone faced the problem?

          Sure.

          Here's a PL/SQL function to do that.

          It takes an input BLOB argument and returns a CLOB encoded in Base64.

           

            function base64encode(p_blob in blob)
            return clob
            is
              CRLF   constant varchar2(2) := chr(13)||chr(10);
              l_clob   clob;
              l_amount integer := 23826; 
              l_offset integer := 1;
              l_raw    raw(32767);
              l_buf    varchar2(32767);
              l_len    integer := dbms_lob.getlength(p_blob);
            begin
              
              dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
              
              while l_offset <= l_len loop      
                dbms_lob.read(p_blob, l_amount, l_offset, l_raw);
                l_offset := l_offset + l_amount;
                l_buf := utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_raw));
                l_buf := replace(l_buf, CRLF);
                dbms_lob.writeappend(l_clob, length(l_buf), l_buf);
              end loop;
              
              return l_clob;
              
            end;
          

           

          If you want more help, please post a test case (sample data and expected output), and your db version.

          Thanks.

          • 2. Re: i need to convert field blob to xs:base64binary with pl*sql
            2927248

            Hy,

             

            Thank you !!

             

            Kindly you would also have the opposite procedure from XML format field base64binary to BLOB ?

            • 3. Re: i need to convert field blob to xs:base64binary with pl*sql
              odie_63

              2927248 wrote:

               

              Kindly you would also have the opposite procedure from XML format field base64binary to BLOB ?

              Here are both functions grouped in a package :

               

              create or replace package xutl_encode is
              
                function base64encode(p_blob in blob) return clob;
                function base64decode(p_clob clob) return blob;
              
              end xutl_encode;
              /
              
              create or replace package body xutl_encode is
              
                CRLF  constant varchar2(2) := chr(13)||chr(10);
                
                function base64encode(p_blob in blob)
                return clob
                is
                  l_clob   clob;
                  l_amount integer := 23826; 
                  l_offset integer := 1;
                  l_raw    raw(32767);
                  l_buf    varchar2(32767);
                  l_len    integer := dbms_lob.getlength(p_blob);
                begin
                  
                  dbms_lob.createtemporary(l_clob, true, dbms_lob.call);
                  
                  while l_offset <= l_len loop      
                    dbms_lob.read(p_blob, l_amount, l_offset, l_raw);
                    l_offset := l_offset + l_amount;
                    l_buf := utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_raw));
                    l_buf := replace(l_buf, CRLF);
                    dbms_lob.writeappend(l_clob, length(l_buf), l_buf);
                  end loop;
                  
                  return l_clob;
                  
                end;
                
                function base64decode(p_clob clob)
                return blob
                is
                  l_blob      blob;    
                  l_amount    integer;
                  l_offset    integer := 1;
                  l_raw       raw(32767);
                  l_buf       varchar2(32767);
                  l_len       integer := dbms_lob.getlength(p_clob);
                  l_chunk_len binary_integer;
                  l_residue   varchar2(3);  
                begin
              
                  dbms_lob.createtemporary(l_blob, true, dbms_lob.call);
                  
                  while l_offset <= l_len LOOP
                    
                    l_amount := 32764 - nvl(length(l_residue), 0);   
                    dbms_lob.read(p_clob, l_amount, l_offset, l_buf);
                    l_offset := l_offset + l_amount;           
                    l_buf := l_residue || replace(l_buf, CRLF); -- remove line breaks and prepend residue
                    l_chunk_len := trunc(length(l_buf)/4)*4; -- nearest length divisible by 4
                    l_residue := substr(l_buf, l_chunk_len+1);
                    l_buf := substr(l_buf, 1, l_chunk_len);
                    
                    l_raw := utl_encode.base64_decode(utl_raw.cast_to_raw(l_buf));
                    dbms_lob.writeappend (l_blob, utl_raw.length(l_raw), l_raw);
                    
                  end loop;
              
                  return l_blob;
                  
                end;
              
              end xutl_encode;
              /
              

               

              And sample usage :

               

              1) Generating XML with base64-encoded data :

               

              SQL> declare
                2    l_xml  xmltype;
                3  begin
                4  
                5    select xmlelement("root"
                6           , xmlelement("item"
                7             , xutl_encode.base64encode(to_blob(utl_raw.cast_to_raw('Hello!')))
                8             )
                9           )
               10    into l_xml
               11    from dual;
               12  
               13    dbms_output.put_line(l_xml.getclobval(1,2));
               14  
               15  end;
               16  /
              
              <root>
                <item>SGVsbG8h</item>
              </root>
              
              
              PL/SQL procedure successfully completed.
              

               

               

              2) Reading XML containing base64-encoded data :

               

              SQL> declare
                2    l_xml   xmltype := xmltype('<root><item>SGVsbG8h</item></root>');
                3    l_blob  blob;
                4  begin
                5  
                6    select xutl_encode.base64decode(x.item)
                7    into l_blob
                8    from xmltable(
                9           '/root'
               10           passing l_xml
               11           columns item  clob path 'item'
               12         ) x;
               13  
               14    dbms_output.put_line(utl_raw.cast_to_varchar2(dbms_lob.substr(l_blob)));
               15  
               16  end;
               17  /
              
              Hello!
              
              PL/SQL procedure successfully completed.
              
              
              • 4. Re: i need to convert field blob to xs:base64binary with pl*sql
                Vijey Chinnaraj Anthony

                Hi All

                 

                I have the similar issue, I am trying to convert BLOB field into base64binary, this BLOB field stores employee photos, and I need to import these photos into Kronos system which only supports base64binary format.

                 

                this is my SQL in PeopleSoft:

                 

                SELECT EMPLID, EMPLOYEE_PHOTO FROM PS_EMPL_PHOTO;

                 

                I found following blog from Kronos community, but it does not work either.

                 

                Image Content needs to be converted to base64binary

                 

                select

                CAST(N'' AS xml).value('xs:base64Binary(sql:column("this_field_is_the_image_as_column_varbinary"))', 'varchar(max)')

                 

                -- you might need to convert the column_binary first to varbinary.

                 

                I appreciate your help!

                • 5. Re: i need to convert field blob to xs:base64binary with pl*sql
                  2927248

                  Hi Anthony

                   

                  if your input data is a blob, no conversion is needed.
                  Just use the function described below, it returns a clob field that you will then go to normalize in xml format

                  function base64encode (p_blob in blob)
                     return clob
                     is
                       CRLF constant varchar2 (2): = chr (13) || chr (10);
                       l_clob clob;
                       l_amount integer: = 23826;
                       l_offset integer: = 1;
                       l_raw raw (32767);
                       l_buf varchar2 (32767);
                       l_len integer: = dbms_lob.getlength (p_blob);
                     begin
                     
                       dbms_lob.createtemporary (l_clob, true, dbms_lob.call);
                     
                       while l_offset <= l_len loop
                         dbms_lob.read (p_blob, l_amount, l_offset, l_raw);
                         l_offset: = l_offset + l_amount;
                         l_buf: = utl_raw.cast_to_varchar2 (utl_encode.base64_encode (l_raw));
                         l_buf: = replace (l_buf, CRLF);
                         dbms_lob.writeappend (l_clob, length (l_buf), l_buf);
                       end loop;
                     
                       return l_clob;
                     
                     end;

                  • 6. Re: i need to convert field blob to xs:base64binary with pl*sql
                    Vijey Chinnaraj Anthony

                    Thanks for your prompt response, also, I think this function returns in CLOB format, but Kronos documentation it says, it only supports base64binary format.

                    Please advise..

                    • 7. Re: i need to convert field blob to xs:base64binary with pl*sql
                      cormaco

                      Thanks for your prompt response, also, I think this function returns in CLOB format, but Kronos documentation it says, it only supports base64binary format.

                      Base64 encoded data consists only of ASCII characters, it it perfectly possible to store them in a CLOB.

                      https://en.wikipedia.org/wiki/Base64

                      • 8. Re: i need to convert field blob to xs:base64binary with pl*sql
                        Vijey Chinnaraj Anthony

                        I created an XML file with CLOB in PeopleSoft, then I was able to load the pictures into Kronos successfully. I appreciate all the support.