oracle-databaseblobclobvarchar2

ORA-22921: length of input buffer is smaller than amount requested


While I was working with a table containing a BLOB column:

SELECT id FROM table WHERE blob_column LIKE '%something%';

...I got the following error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 16713, maximum: 4000)

An answer to this SO question addresses the problem with a conversion function which seemed like it would help me:

CREATE OR REPLACE FUNCTION VC2CLOB_FROM_BLOB(B BLOB)
RETURN CLOB IS
    c CLOB;
    n NUMBER;
BEGIN
    IF (b IS NULL) THEN 
        RETURN NULL;
    END IF;
    IF (LENGTH(b) = 0) THEN
        RETURN EMPTY_CLOB();
    END IF;
    DBMS_LOB.CREATETEMPORARY(c, TRUE);
    n := 1;
    WHILE (n + 32767 <= LENGTH(b)) LOOP
        DBMS_LOB.WRITEAPPEND(c, 32767, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, 32767, n)));
        n := n + 32767;
    END LOOP;
    DBMS_LOB.WRITEAPPEND(c, LENGTH(b) - n + 1, UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(b, LENGTH(b) - n + 1, n)));
    RETURN c;
END;
/

However, trying this out:

SELECT id FROM table WHERE VC2CLOB_FROM_BLOB(blob_column) LIKE '%something%';

...I still got an error:

ORA-22921: length of input buffer is smaller than amount requested ORA-06512: at "SYS.DBMS_LOB", line 1163 ORA-06512: at "DATABASE.VC2CLOB_FROM_BLOB", line 18

What's going on and how to fix this?


Solution

  • You may wish to use DBMS_LOB.CONVERTTOCLOB to do the conversion rather than iterating over substrings.

    Inverting this answer, you can create a blob_to_clob function:

    CREATE OR REPLACE FUNCTION blob_to_clob(
      value            IN BLOB,
      charset_id       IN INTEGER DEFAULT DBMS_LOB.DEFAULT_CSID,
      error_on_warning IN NUMBER  DEFAULT 0
    ) RETURN CLOB
    IS
      result       CLOB;
      dest_offset  INTEGER := 1;
      src_offset   INTEGER := 1;
      lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
      warning      INTEGER;
      warning_msg  VARCHAR2(50);
    BEGIN
    
      IF value IS NULL THEN
        RETURN NULL;
      END IF;
    
      DBMS_LOB.CreateTemporary(
        lob_loc => result,
        cache   => TRUE
      );
    
      DBMS_LOB.CONVERTTOCLOB(
        dest_lob     => result,
        src_blob     => value,
        amount       => LENGTH( value ),
        dest_offset  => dest_offset,
        src_offset   => src_offset,
        blob_csid    => charset_id,
        lang_context => lang_context,
        warning      => warning
      );
      
      IF warning != DBMS_LOB.NO_WARNING THEN
        IF warning = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
          warning_msg := 'Warning: Inconvertible character.';
        ELSE
          warning_msg := 'Warning: (' || warning || ') during BLOB conversion.';
        END IF;
        
        IF error_on_warning = 0 THEN
          DBMS_OUTPUT.PUT_LINE( warning_msg );
        ELSE
          RAISE_APPLICATION_ERROR(
            -20567, -- random value between -20000 and -20999
            warning_msg
          );
        END IF;
      END IF;
    
      RETURN result;
    END blob_to_clob;
    /
    

    fiddle