oos_util_lob.md

OOS_UTIL_LOB

• Constants

• CLOB2BLOB Function

• BLOB2CLOB Function

• GET_FILE_SIZE Function

• GET_LOB_SIZE Function

• GET_LOB_SIZE-1 Function

• REPLACE_CLOB Function

Constants

Name	Code	Description
gc_unit_b	gc_unit_b constant varchar2(1) := 'B';	B
gc_unit_kb	gc_unit_kb constant varchar2(2) := 'KB';	KB
gc_unit_mb	gc_unit_mb constant varchar2(2) := 'MB';	MB
gc_unit_gb	gc_unit_gb constant varchar2(2) := 'GB';	GB
gc_unit_tb	gc_unit_tb constant varchar2(2) := 'TB';	TB
gc_unit_pb	gc_unit_pb constant varchar2(2) := 'PB';	PB
gc_unit_eb	gc_unit_eb constant varchar2(2) := 'EB';	EB
gc_unit_zb	gc_unit_zb constant varchar2(2) := 'ZB';	ZB
gc_unit_yb	gc_unit_yb constant varchar2(2) := 'YB';	YB
gc_size_b	gc_size_b constant number := 1024;
gc_size_kb	gc_size_kb constant number := power(1024, 2);
gc_size_mb	gc_size_mb constant number := power(1024, 3);
gc_size_gb	gc_size_gb constant number := power(1024, 4);
gc_size_tb	gc_size_tb constant number := power(1024, 5);
gc_size_pb	gc_size_pb constant number := power(1024, 6);
gc_size_eb	gc_size_eb constant number := power(1024, 7);
gc_size_zb	gc_size_zb constant number := power(1024, 8);
gc_size_yb	gc_size_yb constant number := power(1024, 9);

CLOB2BLOB Function

Convers clob to blob

Syntax

function clob2blob(
  p_clob in clob)
  return blob

Parameters

Name	Description
p_clob	Clob to conver to blob
return	blob

BLOB2CLOB Function

Converts blob to clob

Notes:

• Copied from http://stackoverflow.com/questions/12849025/convert-blob-to-clob

Syntax

function blob2clob(
  p_blob in blob,
  p_blob_csid in integer default dbms_lob.default_csid)
  return clob

Parameters

Name	Description
p_blob	blob to be converted to clob
p_blob_csid	Encoding to use. See https://docs.oracle.com/database/121/NLSPG/ch2charset.htm#NLSPG169 (table 2-4) for different charsets. Can use nls_charset_id(<charset>) to get the clob_csid
return	clob

GET_FILE_SIZE Function

Returns human readable file size

Syntax

function get_file_size(
  p_file_size in number,
  p_units in varchar2 default null)
  return varchar2

Parameters

Name	Description
p_file_size	size of file in bytes
p_units	See gc_size_... consants for options. If not provided, most significant one automatically chosen.
return	Human readable file size

GET_LOB_SIZE Function

See get_file_size

Syntax

function get_lob_size(
  p_lob in clob,
  p_units in varchar2 default null)
  return varchar2

Parameters

Name	Description
p_lob
p_units

GET_LOB_SIZE-1 Function

See get_file_size

Syntax

function get_lob_size(
  p_lob in blob,
  p_units in varchar2 default null)
  return varchar2

Parameters

Name	Description
p_lob
p_units

REPLACE_CLOB Function

Replaces p_search with p_replace

Oracle's replace function handles clobs but runs into 32k issues

Notes:

• Source: http://dbaora.com/ora-22828-input-pattern-or-replacement-parameters-exceed-32k-size-limit/

Syntax

function replace_clob(
  p_str in clob,
  p_search in varchar2,
  p_replace in clob)
  return clob

Parameters

Name	Description
p_str
p_search
p_replace
return	Replaced string