CREATE OR REPLACE PACKAGE string_api AS
-- --------------------------------------------------------------------------
-- Name : http://www.oracle-base.com/dba/miscellaneous/string_api.sql
-- Author : Tim Hall
-- Description : A package to hold string utilities.
-- Requirements :
-- Ammedments :
-- When Who What
-- =========== ======== =================================================
-- 02-DEC-2004 Tim Hall Initial Creation
-- --------------------------------------------------------------------------
-- Public types
TYPE t_split_array IS TABLE OF VARCHAR2(4000);
FUNCTION split_text (p_text IN CLOB,
p_delimeter IN VARCHAR2 DEFAULT ',')
RETURN t_split_array;
PROCEDURE print_clob (p_clob IN CLOB);
PROCEDURE print_clob_htp (p_clob IN CLOB);
END string_api;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE BODY string_api AS
-- --------------------------------------------------------------------------
-- Name : http://www.oracle-base.com/dba/miscellaneous/string_api.sql
-- Author : Tim Hall
-- Description : A package to hold string utilities.
-- Requirements :
-- Ammedments :
-- When Who What
-- =========== ======== =================================================
-- 02-DEC-2004 Tim Hall Initial Creation
-- --------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
FUNCTION split_text (p_text IN CLOB,
p_delimeter IN VARCHAR2 DEFAULT ',')
RETURN t_split_array IS
-- ----------------------------------------------------------------------------
l_array t_split_array := t_split_array();
l_text CLOB := p_text;
l_idx NUMBER;
BEGIN
l_array.delete;
IF l_text IS NULL THEN
RAISE_APPLICATION_ERROR(-20000, 'P_TEXT parameter cannot be NULL');
END IF;
WHILE l_text IS NOT NULL LOOP
l_idx := INSTR(l_text, p_delimeter);
l_array.extend;
IF l_idx > 0 THEN
l_array(l_array.last) := SUBSTR(l_text, 1, l_idx - 1);
l_text := SUBSTR(l_text, l_idx + 1);
ELSE
l_array(l_array.last) := l_text;
l_text := NULL;
END IF;
END LOOP;
RETURN l_array;
END split_text;
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
PROCEDURE print_clob (p_clob IN CLOB) IS
-- ----------------------------------------------------------------------------
l_offset NUMBER := 1;
l_chunk NUMBER := 255;
BEGIN
LOOP
EXIT WHEN l_offset > DBMS_LOB.getlength(p_clob);
DBMS_OUTPUT.put_line(DBMS_LOB.substr(p_clob, l_chunk, l_offset));
l_offset := l_offset + l_chunk;
END LOOP;
END print_clob;
-- ----------------------------------------------------------------------------
-- ----------------------------------------------------------------------------
PROCEDURE print_clob_htp (p_clob IN CLOB) IS
-- ----------------------------------------------------------------------------
l_offset NUMBER := 1;
l_chunk NUMBER := 32767;
BEGIN
LOOP
EXIT WHEN l_offset > DBMS_LOB.getlength(p_clob);
HTP.prn(DBMS_LOB.substr(p_clob, l_chunk, l_offset));
l_offset := l_offset + l_chunk;
END LOOP;
END print_clob_htp;
-- ----------------------------------------------------------------------------
END string_api;
/
SHOW ERRORS
