OOS_UTIL_LOB

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:

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:

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