oos_util_lob.md

OOS_UTIL_LOB

• Constants
• CLOB2BLOB Function
• BLOB2CLOB Function
• GET_FILE_SIZE Function
• GET_LOB_SIZE Function
• GET_LOB_SIZE Function
• REPLACE_CLOB Function
• WRITE_TO_FILE Procedure
• READ_FROM_FILE Function

Constants

Name	Code	Description
gc_unit_b	gc_unit_b constant varchar2(1) := 'B';
gc_unit_kb	gc_unit_kb constant varchar2(2) := 'KB';
gc_unit_mb	gc_unit_mb constant varchar2(2) := 'MB';
gc_unit_gb	gc_unit_gb constant varchar2(2) := 'GB';
gc_unit_tb	gc_unit_tb constant varchar2(2) := 'TB';
gc_unit_pb	gc_unit_pb constant varchar2(2) := 'PB';
gc_unit_eb	gc_unit_eb constant varchar2(2) := 'EB';
gc_unit_zb	gc_unit_zb constant varchar2(2) := 'ZB';
gc_unit_yb	gc_unit_yb constant varchar2(2) := 'YB';
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)
  return clob

Parameters

Name	Description
p_blob	blob to be converted to clob
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 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

WRITE_TO_FILE Procedure

Write a clob (p_text) into a file (p_filename) located in a database
server file system directory (p_path). p_path is an Oracle directory
object.

Syntax

procedure write_to_file(
  p_text in clob,
  p_path in varchar2,
  p_filename in varchar2)

Parameters

Name	Description
p_text
p_path
p_filename

READ_FROM_FILE Function

Read a content of a file (p_filename) from a database server file system
directory (p_path) and return it as a temporary clob. The caller is
responsible to free the clob (dbms_lob.freetemporary()). p_path is an
Oracle directory object.

The implementation is based on UTL_FILE so the following constraints apply:

A line size can't exceed 32767 bytes.

Because UTL_FILE.get_line ignores line terminator it has to be added
implicitly. Currently the line terminator is hardcoded to char(10)
(unix), so if in the original file the terminator is different then a
conversion will take place.

TODO: consider DBMS_LOB.LOADCLOBFROMFILE instead.

Syntax

function read_from_file(
  p_path in varchar2,
  p_filename in varchar2)
  return clob

Parameters

Name	Description
p_path
p_filename
return	clob