Updating blob shows PL/SQL success but no content change

Question

Attempting to update pictures in a blob column for multiple rows, I receive successful messages with no error and yet changes have not been rendered. I restarted Oracle service and machine and still nothing propagated. Yes, as shown I am committing and I have tried only one blob to no avail.

• Is it the temporary dest_lob object? The readonly type? Do I have to release the temp blob?
• Is it the appropriate schema not set? I am calling it with schema user.
• Do previous blob content have to be NULL? The Hamlet.jpg attempt (ID = 101) maintains NULL in Picture blob column.
• Is it a caching issue with Oracle? Do I have to refresh something like the tablespace file?
• Is it the multiple BEGIN ... END calls?

Table

 SQL > desc Characters;
 Name                  Null?    Type
 ----------------------------------------------------------------
 ID                    NOT NULL NUMBER(5)
 CHARACTER                      VARCHAR2(255 CHAR)
 DESCRIPTION                    VARCHAR2(1000 CHAR)
 SOURCE                         VARCHAR2(255 CHAR)
 QUOTE                          VARCHAR2(1500 CHAR)
 PICTURE                        BLOB
 LINKIMAGE                      VARCHAR2(255 CHAR)

PL-SQL

CREATE OR REPLACE DIRECTORY MY_DIR AS '/path/to/pictures';
DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'HenryCorwin.jpg');
  dest_blob BLOB;
BEGIN
  SELECT PICTURE into dest_blob FROM CHARACTERS WHERE ID = 44;
  DBMS_LOB.OPEN(src_bfile, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.CREATETEMPORARY( dest_blob, TRUE);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.CLOSE(src_bfile);
  COMMIT;
END;
/
DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'Hamlet.jpg');
  dest_blob BLOB;
BEGIN
  SELECT PICTURE into dest_blob FROM CHARACTERS WHERE ID = 101;
  DBMS_LOB.OPEN(src_bfile, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.CREATETEMPORARY( dest_blob, TRUE);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.CLOSE(src_bfile);
  COMMIT;
END;
/
DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'CGreen.jpg');
  dest_blob BLOB;
BEGIN
  SELECT PICTURE into dest_blob FROM  CHARACTERS WHERE ID = 15;
  DBMS_LOB.OPEN(src_bfile, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.CREATETEMPORARY( dest_blob, TRUE);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.CLOSE(src_bfile);
  COMMIT;
END;
/
DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'SevenOfNine.jpg');
  dest_blob BLOB;
BEGIN
  SELECT PICTURE into dest_blob FROM CHARACTERS WHERE ID = 82;
  DBMS_LOB.OPEN(src_bfile, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.CREATETEMPORARY( dest_blob, TRUE);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.CLOSE(src_bfile);
  COMMIT;
END;
/

sqlplus command line call (of above script)

SQL> @/path/to/script.sql

Console Output

Directory created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

SQL Blob data check

SELECT ID FROM Characters
WHERE DBMS_LOB.GETLENGTH(Picture) = 0 OR 
      DBMS_LOB.GETLENGTH(Picture) IS NULL;

Output

    ID
----------
    15
    44
    82
   101

Environment

• Ubuntu 16.04 LTS, 64-bit
• Intel Celeron, 4-processor, 64-GB HD, 2-GB RAM

• Oracle 11g Express:

  SQL > select * from v$version
  
  Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  PL/SQL Release 11.2.0.2.0 - Production
  CORE    11.2.0.2.0  Production
  TNS for Linux: Version 11.2.0.2.0 - Production
  NLSRTL Version 11.2.0.2.0 - Production
  

Answer 1

With help from this other forum thread, I found my issue. Instead of using the following LOB methods:

DBMS_LOB.OPEN(src_bfile, DBMS_LOB.LOB_READONLY);

Simply replace with the FILE version and remove the CREATETEMPORARY():

DBMS_LOB.FILEOPEN(src_bfile, DBMS_LOB.FILE_READONLY);

And in hindsight this makes somewhat sense since I am reading form a file object, src_bfile, and not an actual blob object.

With the following updated PL/SQL routine, blob columns successfully render.

CREATE OR REPLACE DIRECTORY MY_DIR AS '/path/to/pictures';
DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'HenryCorwin.jpg');
  dest_blob BLOB;
BEGIN
  SELECT PICTURE into dest_blob FROM CHARACTERS WHERE ID = 44 FOR UPDATE;
  DBMS_LOB.FILEOPEN(src_bfile, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.FILECLOSE(src_bfile);
  COMMIT;
END;
/
DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'CGreen.jpg');
  dest_blob BLOB;
BEGIN
  SELECT PICTURE into dest_blob FROM  CHARACTERS WHERE ID = 15 FOR UPDATE;
  DBMS_LOB.FILEOPEN(src_bfile, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.FILECLOSE(src_bfile);
  COMMIT;
END;
/
DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'SevenOfNine.jpg');
  dest_blob BLOB;
BEGIN
  SELECT PICTURE into dest_blob FROM CHARACTERS WHERE ID = 82 FOR UPDATE;
  DBMS_LOB.FILEOPEN(src_bfile, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.CREATETEMPORARY( dest_blob, TRUE);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.FILECLOSE(src_bfile);
  COMMIT;
END;
/

Also, if blob column is NULL like my Hamlet.jpg attempt the above procedure will fail with

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:

ORA-22275 ORA-06512: at "SYS.DBMS_LOB", line 928 ORA-06512: at line 7

To resolve, update with EMPTY_BLOB() prior to updating the blob with the intended object.

UPDATE CHARACTERS SET Picture = EMPTY_BLOB() WHERE ID = 101;
DECLARE
  src_bfile BFILE := BFILENAME('MY_DIR', 'Hamlet.jpg');
  dest_blob BLOB;
BEGIN
  SELECT PICTURE into dest_blob FROM CHARACTERS WHERE ID = 101 FOR UPDATE;
  DBMS_LOB.FILEOPEN(src_bfile, DBMS_LOB.FILE_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                         SRC_LOB  => src_bfile,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
  DBMS_LOB.FILECLOSE(src_bfile);
  COMMIT;
END;
/

Successful messages render and final query returns a sight to sore eyes:

SQL > SELECT ID FROM Characters WHERE DBMS_LOB.GETLENGTH(Picture) = 0 OR DBMS_LOB.GETLENGTH(Picture) IS NULL;

no rows selected

SQL > SQL> SELECT DBMS_LOB.GETLENGTH(Picture) FROM Characters WHERE ID IN (15, 44, 82, 101);

DBMS_LOB.GETLENGTH(PICTURE)
---------------------------
                     365256
                     412300
                     381586
                     404241

Answer 2

I'll suggest you do an dbms_lob.erase, which seems to be necessary:

SELECT PICTURE INTO dest_blob FROM CHARACTERS WHERE ID = 15
   FOR UPDATE;
dest_length := DBMS_LOB.GETLENGTH(dest_blob);
IF dest_length <> 0 THEN
  DBMS_LOB.ERASE(dest_blob,dest_length,1);
END IF;

-- then do as you wrote:
DBMS_LOB.OPEN(src_bfile, DBMS_LOB.LOB_READONLY);
DBMS_LOB.CREATETEMPORARY( dest_blob, TRUE);
DBMS_LOB.LoadFromFile( DEST_LOB => dest_blob,
                       SRC_LOB  => src_bfile,
                       AMOUNT   => DBMS_LOB.GETLENGTH(src_bfile) );
DBMS_LOB.CLOSE(src_bfile);
COMMIT;

Hope this helps (stolen from here)