199 lines
7.0 KiB
SQL
199 lines
7.0 KiB
SQL
CREATE OR REPLACE PACKAGE string_api AS
|
|
-- --------------------------------------------------------------------------
|
|
-- Name : https://oracle-base.com/dba/miscellaneous/string_api.sql
|
|
-- Author : Tim Hall
|
|
-- Description : A package to hold string utilities.
|
|
-- Requirements :
|
|
-- Amendments :
|
|
-- When Who What
|
|
-- =========== ======== =================================================
|
|
-- 02-DEC-2004 Tim Hall Initial Creation
|
|
-- 19-JAN-2017 Tim Hall Add get_uri_paramter_value function.
|
|
-- --------------------------------------------------------------------------
|
|
|
|
-- 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_old (p_clob IN CLOB);
|
|
|
|
PROCEDURE print_clob_htp (p_clob IN CLOB);
|
|
PROCEDURE print_clob_htp_old (p_clob IN CLOB);
|
|
|
|
FUNCTION get_uri_paramter_value (p_uri IN VARCHAR2,
|
|
p_param_name IN VARCHAR2)
|
|
RETURN VARCHAR2;
|
|
|
|
END string_api;
|
|
/
|
|
SHOW ERRORS
|
|
|
|
|
|
CREATE OR REPLACE PACKAGE BODY string_api AS
|
|
-- --------------------------------------------------------------------------
|
|
-- Name : https://oracle-base.com/dba/miscellaneous/string_api.sql
|
|
-- Author : Tim Hall
|
|
-- Description : A package to hold string utilities.
|
|
-- Requirements :
|
|
-- Amendments :
|
|
-- When Who What
|
|
-- =========== ======== =================================================
|
|
-- 02-DEC-2004 Tim Hall Initial Creation
|
|
-- 31-AUG-2017 Tim Hall SUBSTR parameters switched.
|
|
-- 19-JAN-2017 Tim Hall Add get_uri_paramter_value function.
|
|
-- 20-NOV-2018 Tim Hall Reduce the chunk sizes to allow for multibyte character sets.
|
|
-- --------------------------------------------------------------------------
|
|
|
|
-- Variables to support the URI functionality.
|
|
TYPE t_uri_array IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767);
|
|
g_last_uri VARCHAR2(32767) := 'initialized';
|
|
g_uri_tab t_uri_array;
|
|
|
|
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
FUNCTION split_text (p_text IN CLOB,
|
|
p_delimeter IN VARCHAR2 DEFAULT ',')
|
|
RETURN t_split_array IS
|
|
-- ----------------------------------------------------------------------------
|
|
-- Could be replaced by APEX_UTIL.STRING_TO_TABLE.
|
|
-- ----------------------------------------------------------------------------
|
|
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 > LENGTH(p_clob);
|
|
DBMS_OUTPUT.put_line(SUBSTR(p_clob, l_offset, l_chunk));
|
|
l_offset := l_offset + l_chunk;
|
|
END LOOP;
|
|
END print_clob;
|
|
-- ----------------------------------------------------------------------------
|
|
|
|
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
PROCEDURE print_clob_old (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_offset, l_chunk));
|
|
l_offset := l_offset + l_chunk;
|
|
END LOOP;
|
|
END print_clob_old;
|
|
-- ----------------------------------------------------------------------------
|
|
|
|
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
PROCEDURE print_clob_htp (p_clob IN CLOB) IS
|
|
-- ----------------------------------------------------------------------------
|
|
l_offset NUMBER := 1;
|
|
l_chunk NUMBER := 3000;
|
|
BEGIN
|
|
LOOP
|
|
EXIT WHEN l_offset > LENGTH(p_clob);
|
|
HTP.prn(SUBSTR(p_clob, l_offset, l_chunk));
|
|
l_offset := l_offset + l_chunk;
|
|
END LOOP;
|
|
END print_clob_htp;
|
|
-- ----------------------------------------------------------------------------
|
|
|
|
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
PROCEDURE print_clob_htp_old (p_clob IN CLOB) IS
|
|
-- ----------------------------------------------------------------------------
|
|
l_offset NUMBER := 1;
|
|
l_chunk NUMBER := 3000;
|
|
BEGIN
|
|
LOOP
|
|
EXIT WHEN l_offset > DBMS_LOB.getlength(p_clob);
|
|
HTP.prn(DBMS_LOB.substr(p_clob, l_offset, l_chunk));
|
|
l_offset := l_offset + l_chunk;
|
|
END LOOP;
|
|
END print_clob_htp_old;
|
|
-- ----------------------------------------------------------------------------
|
|
|
|
|
|
|
|
-- ----------------------------------------------------------------------------
|
|
FUNCTION get_uri_paramter_value (p_uri IN VARCHAR2,
|
|
p_param_name IN VARCHAR2)
|
|
RETURN VARCHAR2 IS
|
|
-- ----------------------------------------------------------------------------
|
|
-- Example:
|
|
-- l_uri := 'https://localhost:8080/my_page.php?param1=value1¶m2=value2¶m3=value3';
|
|
-- l_value := string_api.get_uri_paramter_value(l_uri, 'param1')
|
|
-- ----------------------------------------------------------------------------
|
|
l_uri VARCHAR2(32767);
|
|
l_array t_split_array := t_split_array();
|
|
l_idx NUMBER;
|
|
BEGIN
|
|
IF p_uri IS NULL OR p_param_name IS NULL THEN
|
|
RAISE_APPLICATION_ERROR(-20000, 'p_uri and p_param_name must be specified.');
|
|
END IF;
|
|
|
|
IF p_uri != g_last_uri THEN
|
|
-- First time we've seen this URI, so build the key-value table.
|
|
g_uri_tab.DELETE;
|
|
g_last_uri := p_uri;
|
|
l_uri := TRANSLATE(g_last_uri, '&?', '^^');
|
|
l_array := split_text(l_uri, '^');
|
|
FOR i IN 1 .. l_array.COUNT LOOP
|
|
l_idx := INSTR(l_array(i), '=');
|
|
IF l_idx != 0 THEN
|
|
g_uri_tab(SUBSTR(l_array(i), 1, l_idx - 1)) := SUBSTR(l_array(i), l_idx + 1);
|
|
--DBMS_OUTPUT.put_line('param_name=' || SUBSTR(l_array(i), 1, l_idx - 1) ||
|
|
-- ' | param_value=' || SUBSTR(l_array(i), l_idx + 1));
|
|
END IF;
|
|
END LOOP;
|
|
END IF;
|
|
|
|
RETURN g_uri_tab(p_param_name);
|
|
EXCEPTION
|
|
WHEN NO_DATA_FOUND THEN
|
|
RETURN NULL;
|
|
END get_uri_paramter_value;
|
|
-- ----------------------------------------------------------------------------
|
|
|
|
END string_api;
|
|
/
|
|
SHOW ERRORS
|