Home » SQL & PL/SQL » SQL & PL/SQL » Base64 to Blob (Oracle, 12.2.0.1.0, Linux)
Base64 to Blob [message #686808] Thu, 12 January 2023 08:05 Go to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
Ok, I'm hoping someone can help me with this. I had this working but lost everything. I think, but not sure, that I found some code from Solomon that did the Base64 to Blob conversion but I'm not sure. Anyway, before I lost everything it worked like a charm. You could upload any file you wanted and it just worked. Just don't remember what I need or even the steps needed to make it work.

Here's what I'm trying to accomplish. I have a web page, uses Fetch, that uploads files (Images, Word, PowerPoint, Text file...etc) into an Oracle table. The Fetch calls a Package/Procedure. The column is a Blob. The fetch sends the file upload as Base64, and I assume, as a Clob.

I'm using the following routine to convert that Base64 file upload to a blob but it's nothing you can read if viewed within Toad.

Anyone know what I'm doing wrong?


procedure FileUpload (PIDM           in varchar2,
                      UserID         in varchar2,
                      SequenceNumber in varchar2,
                      FileAction     in varchar2,
                      FileMime       in varchar2 default null,
                      FileName       in varchar2 default null,
                      FileUpload     in clob     default null) is

  DataBlob     blob;

  begin
     .
     .
     .
     DataBlob := Base64ToBlob(FileUpload);

     insert into file_upload
            (file_blob)
       
            values
              (DataBlob);
     
     commit;

  end;


function Base64ToBlob (Base64Data in clob) return blob is
    
  DataAmount number := 7700;
  DataOffset number := 1;
  Temp       varchar2(32767);
  DataRaw    raw(32767);
  DataBlob   blob;
  
  
  begin
    begin
      dbms_lob.createtemporary(DataBlob, false, dbms_lob.call);
      loop
        dbms_lob.read(Base64Data, DataAmount, DataOffset, Temp);
        DataOffset := DataOffset + DataAmount;
        DataRaw := utl_encode.base64_decode(utl_raw.cast_to_raw(Temp));
        dbms_lob.append(DataBlob, to_blob(DataRaw));
      end loop;
    
    exception
      when no_data_found 
        then
          null;
    
    end;

    return DataBlob;
      
  end; 


[Updated on: Thu, 12 January 2023 08:11]

Report message to a moderator

Re: Base64 to Blob [message #686810 is a reply to message #686808] Thu, 12 January 2023 08:22 Go to previous messageGo to next message
Duane
Messages: 508
Registered: December 2002
Senior Member
Uploaded Text File

Base64 Value: Tk8gREFUQSBmb3IgMDEvMDQvMjAyMw0K

Converted Value: NO DATA for 01/04/2023

Blob Column Value: u«Zc×±·úej)m«ë““ÈUH›Üˆ KÌ
ÌŒ ŒÃB
Re: Base64 to Blob [message #686815 is a reply to message #686810] Thu, 12 January 2023 15:20 Go to previous message
Duane
Messages: 508
Registered: December 2002
Senior Member
I believe I have this working again. It would appear the file upload is prefixed with ".....base64," based on what is being uploaded. I had to script that off before doing the Base64 to Blob conversion.
Previous Topic: Row Level trigger
Next Topic: SQL very slow
Goto Forum:
  


Current Time: Thu Feb 09 02:38:08 CST 2023