Home

Description

This rule flags any invocation of DBMS_LOB.LOADFROMFILE or the desupported LOB buffering API (for example, calls that enable buffering or trap the BUFFERING_ENABLED exception). Starting in Oracle 21c these features are desupported: the generic procedure LOADFROMFILE has been split into the type-specific routines LoadBlobFromFile and LoadClobFromFile, and LOB buffering has been removed in favour of prefetch and piece-wise I/O.

Key Benefits

• Upgrade Safety: Prevents compile-time failures after migrating to Oracle 21c, 23ai, and later releases.
• Type Clarity: Separate procedures for BLOB and CLOB operations make code easier to read and audit.
• Performance: Modern SecureFiles I/O with prefetch and chunked writes typically outperforms the retired buffering API.
• Reduced Risk: Removes dependence on legacy code paths that are no longer patched or supported by Oracle.

Non-compliant Code Example

DECLARE
  src_bfile  BFILE := BFILENAME('MEDIA_DIR','video.mp4');
  dst_blob   BLOB;
BEGIN
  DBMS_LOB.createtemporary(dst_blob, TRUE);

  DBMS_LOB.LOADFROMFILE(dst_blob, src_bfile, DBMS_LOB.getLength(src_bfile)); --Non compliant code (LOADFROMFILE subprogram is desupported)

EXCEPTION
    WHEN DBMS_LOB.BUFFERING_ENABLED THEN --Non compliant code (LOB buffering is desupported)
    DBMS_OUTPUT.put_line('Cannot write: buffering is ON, flush first');
END;
/

Compliant Code Example

DECLARE
  src_bfile  BFILE := BFILENAME('MEDIA_DIR','video.mp4');
  dst_blob   BLOB;
BEGIN
  DBMS_LOB.createtemporary(dst_blob, TRUE);

  /* type-specific routine replaces LOADFROMFILE */
  DBMS_LOB.LoadBlobFromFile(
      dest_lob     => dst_blob,
      src_bfile    => src_bfile,
      amount       => DBMS_LOB.getLength(src_bfile)); --Compliant code

  /* no buffering API exists, so no BUFFERING_ENABLED exception */

END;
/