Base64 encoding and decoding in oracle

Question

How can I do Base64 encode decode a value in Oracle

User · Answer

do url raw cast to raw   support in oracle 6

User · Answer

All the previous posts are correct  There s more than one way to skin a cat  Here is another way to do the same thing   just replace  what ever you want to convert  with your string and run it in Oracle       set serveroutput on      DECLARE     v str VARCHAR2 1000       BEGIN     --Create encoded value     v str    utl encode text encode       what ever you want to convert   WE8ISO8859P1   UTL ENCODE BASE64       dbms output put line v str       --Decode the value       v str    utl encode text decode      v str  WE8ISO8859P1   UTL ENCODE BASE64       dbms output put line v str       END          source

User · Answer

I ve implemented this to send Cyrillic e-mails through my MS Exchange server   function to base64 t in varchar2  return varchar2 is  begin     return utl raw cast to varchar2 utl encode base64 encode utl raw cast to raw t     end to base64    Try it   upd  after a minor adjustment I came up with this  so it works both ways now   function from base64 t in varchar2  return varchar2 is begin   return utl raw cast to varchar2 utl encode base64 decode utl raw cast to raw t     end from base64    You can check it   SQL gt  set serveroutput on SQL gt   SQL gt  declare   2    function to base64 t in varchar2  return varchar2 is   3    begin   4      return utl raw cast to varchar2 utl encode base64 encode utl raw cast to raw t       5    end to base64    6     7    function from base64 t in varchar2  return varchar2 is   8    begin   9      return utl raw cast to varchar2 utl encode base64 decode utl raw cast to raw     t      10    end from base64   11    12  begin  13    dbms output put line from base64 to base64  asdf       14  end   15     asdf  PL SQL procedure successfully completed   upd2  Ok  here s a sample conversion that works for CLOB I just came up with  Try to work it out for your blobs      declare    clobOriginal     clob    clobInBase64     clob    substring        varchar2 2000     n                pls integer    0    substring length pls integer    2000     function to base64 t in varchar2  return varchar2 is   begin     return utl raw cast to varchar2 utl encode base64 encode utl raw cast to raw t       end to base64     function from base64 t in varchar2  return varchar2 is   begin     return utl raw cast to varchar2 utl encode base64 decode utl raw cast to raw t       end from base64   begin    select clobField into clobOriginal from clobTable where id   1     while true loop        we substract pieces of substring length       substring    dbms lob substr clobOriginal                                   least substring length  substring length   n   1 - length clobOriginal                                     substring length   n   1           if no substring is found  - then we ve reached the end of blob        if substring is null then       exit      end if           convert them to base64 encoding and stack it in new clob vadriable       clobInBase64    clobInBase64    to base64 substring                 n    n   1       end loop     n    0    clobOriginal    null       then we do the very same thing backwards - decode base64     while true loop       substring    dbms lob substr clobInBase64                                   least substring length  substring length   n   1 - length clobInBase64                                     substring length   n   1         if substring is null then       exit      end if        clobOriginal    clobOriginal    from base64 substring         n    n   1      end loop            and insert the data in our sample table - to ensure it s the same     insert into clobTable  id  anotherClobField  values  1  clobOriginal    end

User · Answer

Solution with utl encode base64 encode and utl encode base64 decode have one limitation  they work only with strings up to 32 767 characters bytes   In case you have to convert bigger strings you will face several obstacles    For BASE64 ENCODE the function has to read 3 Bytes and transform them  In case of Multi-Byte characters  e g              stored at UTF-8  aka AL32UTF8  3 Character are not necessarily also 3 Bytes  In order to read always 3 Bytes you have to convert your CLOB into BLOB first  The same problem applies for BASE64 DECODE  The function has to read 4 Bytes and transform them into 3 Bytes  Those 3 Bytes are not necessarily also 3 Characters Typically a BASE64-String has NEW LINE  CR and or LF  character each 64 characters  Such new-line characters have to be ignored while decoding    Taking all this into consideration the full featured solution could be this one   CREATE OR REPLACE FUNCTION DecodeBASE64 InBase64Char IN OUT NOCOPY CLOB  RETURN CLOB IS      blob loc BLOB      clob trim CLOB      res CLOB       lang context INTEGER    DBMS LOB DEFAULT LANG CTX      dest offset INTEGER    1      src offset INTEGER    1      read offset INTEGER    1      warning INTEGER      ClobLen INTEGER    DBMS LOB GETLENGTH InBase64Char        amount INTEGER    1440  -- must be a whole multiple of 4     buffer RAW 1440       stringBuffer VARCHAR2 1440       -- BASE64 characters are always simple ASCII  Thus you get never any Mulit-Byte character and having the same size as  amount  is sufficient  BEGIN      IF InBase64Char IS NULL OR NVL ClobLen  0    0 THEN          RETURN NULL      ELSIF ClobLen lt   32000 THEN         RETURN UTL RAW CAST TO VARCHAR2 UTL ENCODE BASE64 DECODE UTL RAW CAST TO RAW InBase64Char         END IF              -- UTL ENCODE BASE64 DECODE is limited to 32k  process in chunks if bigger          -- Remove all NEW LINE from base64 string     ClobLen    DBMS LOB GETLENGTH InBase64Char       DBMS LOB CREATETEMPORARY clob trim  TRUE       LOOP         EXIT WHEN read offset  gt  ClobLen          stringBuffer    REPLACE REPLACE DBMS LOB SUBSTR InBase64Char  amount  read offset   CHR 13   NULL   CHR 10   NULL           DBMS LOB WRITEAPPEND clob trim  LENGTH stringBuffer   stringBuffer           read offset    read offset   amount      END LOOP       read offset    1      ClobLen    DBMS LOB GETLENGTH clob trim       DBMS LOB CREATETEMPORARY blob loc  TRUE       LOOP         EXIT WHEN read offset  gt  ClobLen          buffer    UTL ENCODE BASE64 DECODE UTL RAW CAST TO RAW DBMS LOB SUBSTR clob trim  amount  read offset             DBMS LOB WRITEAPPEND blob loc  DBMS LOB GETLENGTH buffer   buffer           read offset    read offset   amount      END LOOP       DBMS LOB CREATETEMPORARY res  TRUE       DBMS LOB CONVERTTOCLOB res  blob loc  DBMS LOB LOBMAXSIZE  dest offset  src offset   DBMS LOB DEFAULT CSID  lang context  warning        DBMS LOB FREETEMPORARY blob loc       DBMS LOB FREETEMPORARY clob trim       RETURN res       END DecodeBASE64      CREATE OR REPLACE FUNCTION EncodeBASE64 InClearChar IN OUT NOCOPY CLOB  RETURN CLOB IS      dest lob BLOB        lang context INTEGER    DBMS LOB DEFAULT LANG CTX      dest offset INTEGER    1      src offset INTEGER    1      read offset INTEGER    1      warning INTEGER      ClobLen INTEGER    DBMS LOB GETLENGTH InClearChar        amount INTEGER    1440  -- must be a whole multiple of 3     -- size of a whole multiple of 48 is beneficial to get NEW LINE after each 64 characters      buffer RAW 1440       res CLOB    EMPTY CLOB     BEGIN      IF InClearChar IS NULL OR NVL ClobLen  0    0 THEN          RETURN NULL      ELSIF ClobLen  lt   24000 THEN         RETURN UTL RAW CAST TO VARCHAR2 UTL ENCODE BASE64 ENCODE UTL RAW CAST TO RAW InClearChar         END IF      -- UTL ENCODE BASE64 ENCODE is limited to 32k  3 4   process in chunks if bigger          DBMS LOB CREATETEMPORARY dest lob  TRUE       DBMS LOB CONVERTTOBLOB dest lob  InClearChar  DBMS LOB LOBMAXSIZE  dest offset  src offset  DBMS LOB DEFAULT CSID  lang context  warning       LOOP         EXIT WHEN read offset  gt   dest offset          DBMS LOB READ dest lob  amount  read offset  buffer           res    res    UTL RAW CAST TO VARCHAR2 UTL ENCODE BASE64 ENCODE buffer                   read offset    read offset   amount      END LOOP      DBMS LOB FREETEMPORARY dest lob       RETURN res   END EncodeBASE64

[oracle] Base64 encoding and decoding in oracle

Examples related to oracle

Examples related to base64