Oracle DBMS_LOB Functions
-- The max value for SUBSTR is 2000 in SQL Developer
-- (max capacity is 32767)
SELECT  t.*,
  DBMS_LOB.getlength(t.file_content) blob_length_bytes,
  DBMS_LOB.getchunksize(t.file_content) chunk_size,
  DBMS_LOB.instr(t.file_content,UTL_RAW.CAST_TO_RAW(''),1,1) instr_eof,
  UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(t.file_content, 2000, 1))
FROM cms_web_content t;

-- Compare two BLOB fields
SELECT
 h.file_id,
 DBMS_LOB.getlength(h.file_content) h_size,
 DBMS_LOB.getlength(t.file_content) t_size,
 DBMS_LOB.compare(h.file_content, t.file_content) blob_compare,
 DECODE(SIGN(DBMS_LOB.compare(h.file_content, t.file_content)),
  -1,'WIP',0,'COMPLETE','UPDATE') blob_status
FROM cms_web_content t,
     cms_web_content_arch h
WHERE t.file_id = h.file_id;

Oracle DBMS_LOB Getlength(CLOB)
WITH q_tab AS (SELECT LPAD('x',64000, 'x') t_data FROM dual)
SELECT 
 LENGTH(t_data) varchar2_,
 DBMS_LOB.GETLENGTH(TO_CLOB(t_data)||
  t_data||t_data||t_data||t_data||t_data||
  t_data||t_data||t_data||t_data||t_data||
  t_data||t_data||t_data||t_data||t_data||
  t_data||t_data||t_data||t_data||t_data||
  t_data||t_data||t_data||t_data||t_data) clob_4000_x_26
FROM q_tab;

Oracle DBMS_LOB GetLength(CLOB)
Oracle DBMS_LOB Getlength(NCLOB)
WITH q_tab AS
(SELECT LPAD('x',64000, 'x') t_data FROM dual)
SELECT
  LENGTH(t_data) varchar2_,
  DBMS_LOB.GETLENGTH(TO_NCLOB(t_data)) nclob_
FROM q_tab;

Oracle DBMS_LOB GetLength(NCLOB)


Oracle registered trademark of Oracle Corporation.

Last Revised On: September 01, 2014

  1252