Work with BLOB object in Oracle

Question

I have an SQL statement like this-

select utl_encode.utl_encode.base64_encode(IMAGE1) 
from IPHONE.accidentreports 
where "key" = 66

But when I run it I get this error-

ORA-00904: "UTL_ENCODE"."UTL_ENCODE"."BASE64_ENCODE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 2 Column: 8

I want to convert my BLOB object into BASE64. How could it be done?

utl_encode.utl_encode - Why utl_encode twice? utl_encode is a Package, and unless you have a DB schema named utl_encode this- utl_encode.base64_encode is the correct syntax. — Anjan Biswas
– Anjan Biswas, Commented Oct 9, 2012 at 14:00

Vincent Malgrat · Accepted Answer · 2012-10-09 14:48:33Z

5

Since the UTL_ENCODE.BASE64_ENCODE function works on RAWs, it will work with BLOBs up to 32767 bytes in PL/SQL or 4000 bytes in SQL.

If your images are larger, you'll have to write your own function. Here's an example:

CREATE OR REPLACE FUNCTION base64_encode_blob (p BLOB) RETURN BLOB IS
   l_raw    RAW(24573);
   l_base64 RAW(32767);
   l_result BLOB;
   l_offset NUMBER := 1;
   l_amount NUMBER := 24573;
BEGIN
   DBMS_LOB.createtemporary(l_result, FALSE);
   DBMS_LOB.open(l_result, DBMS_LOB.lob_readwrite);
   LOOP
      DBMS_LOB.read(p, l_amount, l_offset, l_raw);
      l_offset := l_offset + l_amount;
      l_base64 := utl_encode.base64_encode(l_raw);
      DBMS_LOB.writeappend(l_result, utl_raw.length(l_base64), l_base64);
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN l_result;
END;
/

answered Oct 9, 2012 at 14:48

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anjan Biswas · Accepted Answer · 2014-02-19 00:32:01Z

0

First of all UTL_ENCODE.BASE64_ENCODE works on binary representation of a RAW value and the function looks like:

UTL_ENCODE.BASE64_ENCODE (
   r  IN RAW) 
RETURN RAW;

So, considering IMAGE1 is of RAW type:

SELECT UTL_ENCODE.BASE64_ENCODE(CAST(IMAGE1 AS RAW))  --if IMAGE1 is LOB
  FROM IPHONE.accidentreports 
 WHERE "key" = 66;

4 Comments

user1731968 Over a year ago

have tried to do: "select utl_raw.cast_to_raw(image1) from IPHONE.accidentreports where "key" = 66" but have got: ORA-06553: PLS-306: wrong number or types of arguments in call to 'CAST_TO_RAW' 06553. 00000 - "PLS-%s: %s" *Cause: *Action: Error at Line: 5 Column: 8

Anjan Biswas Over a year ago

That is because IMAGE1 is not RAW. The datatype of IMAGE1 has to be RAW not BLOB or CLOB. Updated answer.

user1731968 Over a year ago

IMAGE1 is BLOB, but why BLOB couldn't be converted to base64 or raw? ( isn't PNG file is a raw image , which holds in DB as BLOB object )?

Anjan Biswas Over a year ago

BLOB is Binary Large object. You can CAST an LOB to RAW and then use the BASE64 function on it. If you noticed my updated answer you would see that I CAST the IMAGE1 column while passing it to the function.

Collectives™ on Stack Overflow

Work with BLOB object in Oracle

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related