Encode Decode base64 in PLSQL

Decode base64 in PLSQL:

decode base64 in plsql.txt

Download TXT • 3KB

DECLARE 
   CURSOR get_base64_clob IS 
      SELECT neutral_file
      FROM   c_smart_document_tab
      WHERE  sequence_no = '42';
   
   base64_clob_ CLOB;
   directory_   VARCHAR2(100);
   filename_    VARCHAR2(1000);

   lob_loc_       BLOB;
   buffer_        RAW(32767);
   buffer_size_   BINARY_INTEGER;
   amount_        BINARY_INTEGER;
   offset_        NUMBER(38) := 1;
   chunksize_     INTEGER;
   out_file_      UTL_FILE.FILE_TYPE;

   FUNCTION Decode_Base64 (
      p_clob_in IN CLOB ) RETURN BLOB
   IS
      blob_             BLOB;
      result_           BLOB;
      offset_           INTEGER;
      buffer_size_      BINARY_INTEGER := 48;
      buffer_varchar_   VARCHAR2(48);
      buffer_raw_       RAW(48);
   BEGIN
      IF (p_clob_in IS NULL) THEN
         RETURN NULL;
      END IF;
      
      DBMS_LOB.CREATETEMPORARY(blob_, TRUE);
      
      offset_ := 1;
      
      FOR i IN 1 .. CEIL(DBMS_LOB.GETLENGTH(p_clob_in) / buffer_size_) LOOP
         DBMS_LOB.READ(p_clob_in, buffer_size_, offset_, buffer_varchar_);
         
         buffer_raw_ := UTL_RAW.CAST_TO_RAW(buffer_varchar_);
         buffer_raw_ := UTL_ENCODE.BASE64_DECODE(buffer_raw_);
         
         DBMS_LOB.WRITEAPPEND(blob_, UTL_RAW.LENGTH(buffer_raw_), buffer_raw_);
         offset_ := offset_ + buffer_size_;
      END LOOP;
      
      result_ := blob_;
      
      DBMS_LOB.FREETEMPORARY(blob_);
      
      RETURN result_;
   END Decode_Base64;
BEGIN
   OPEN  get_base64_clob;
   FETCH get_base64_clob INTO base64_clob_;
   CLOSE get_base64_clob;
      
   directory_ := 'OUT_DIR';
   filename_  := 'rmax.docx';
      
   lob_loc_   := Decode_Base64(base64_clob_);
   chunksize_ := DBMS_LOB.GETCHUNKSIZE(lob_loc_);

   IF (chunksize_ < 32767) THEN
      buffer_size_ := chunksize_;
   ELSE
      buffer_size_ := 32767;
   END IF;

   amount_ := buffer_size_;
       
   DBMS_LOB.OPEN(lob_loc_, DBMS_LOB.LOB_READONLY);
    
   out_file_ := UTL_FILE.FOPEN(location   => directory_, 
                              filename     => filename_, 
                              open_mode    => 'wb',
                              max_linesize => 32767);

   WHILE (amount_ >= buffer_size_) LOOP
      DBMS_LOB.READ(lob_loc => lob_loc_,
                    amount  => amount_,
                    offset  => offset_,
                    buffer  => buffer_);

      offset_ := offset_ + amount_;

      UTL_FILE.PUT_RAW(file      => out_file_,
                       buffer    => buffer_,
                       autoflush => TRUE);

      UTL_FILE.FFLUSH(file => out_file_);
   END LOOP;

   UTL_FILE.FFLUSH(file => out_file_);
   UTL_FILE.FCLOSE(out_file_);
   DBMS_LOB.CLOSE(lob_loc_);
EXCEPTION
   WHEN OTHERS THEN
      UTL_FILE.FCLOSE(out_file_);
      DBMS_LOB.CLOSE(lob_loc_);
      RAISE;
END;

Related Links:

Coding and decoding base64 in PL/SQL | Erik Wramner