Count number of lines in BLOB datatype

Question

I have a table XYZ which has a column BLOB in it. I need to count the number of lines inside the BLOB. Can anyone help me out?

Answer 1

Count the number of line-feed (CHR(10)) characters in the BLOB and then add 1 if the last character is not a line-feed:

SELECT LENGTH(value)
       - LENGTH(REPLACE(TO_CLOB(value, 0), CHR(10)))
       + CASE WHEN CAST(SUBSTR(TO_CLOB(value, 0), -1) AS VARCHAR2(1)) = CHR(10) THEN 0 ELSE 1 END
         AS num_lines
FROM   table_name;

Which, for the sample data:

CREATE TABLE table_name (value BLOB);

INSERT INTO table_name (value)
VALUES (UTL_RAW.CAST_TO_RAW('abc'||CHR(10)||'def'||CHR(10)||'ghi'));
INSERT INTO table_name (value)
VALUES (UTL_RAW.CAST_TO_RAW('abc'||CHR(10)||'def'||CHR(10)));

Outputs:

NUM_LINES
3
2

---

In Oracle 11g, you may need a user-defined function to convert the BLOB to a CLOB:

CREATE 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
  );

  IF DBMS_LOB.GETLENGTH(value) = 0 THEN
    RETURN result;
  END IF;

  DBMS_LOB.CONVERTTOCLOB(
    dest_lob     => result,
    src_blob     => value,
    amount       => dbms_lob.lobmaxsize,
    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 CLOB 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;
/

Then:

SELECT LENGTH(value)
       - LENGTH(REPLACE(blob_to_clob(value), CHR(10)))
       + CASE WHEN CAST(SUBSTR(blob_to_clob(value), -1) AS VARCHAR2(1)) = CHR(10) THEN 0 ELSE 1 END
         AS num_lines
FROM   table_name;

db<>fiddle here