Utils

Set of packages for working with strings, arrays, dates, number.
  • Functions
  • Packages
    • lib_array
    • lib_db_object
    • lib_number
    • lib_string
    • lib_table
    • lib_user

Functions

-- Aggregation function to join text.
-- Usage example:
--   SELECT order_code, RTRIM(aggr_string_join(product_name || ', '), ', ') FROM order_detail GROUP BY order_code;
FUNCTION aggr_string_join(psText IN VARCHAR2) RETURN VARCHAR2;

Packages

lib_array

Methods for working with arrays and to produce arrays.

-- Split text by separators. Pipelined function for use in SQL code.
-- Usage example:
-- SELECT column_value FROM TABLE(lib_array.split_piped('One,Two,Three', ','))
-- SELECT column_value FROM TABLE(lib_array.split_piped('One Two Three', '\s'))
FUNCTION split_piped (psText IN VARCHAR2, psSeparator IN VARCHAR2, psModifier IN VARCHAR2 := '') RETURN tStringArray4K PIPELINED;

-- Split string by separators. For use in PL/SQL code.
FUNCTION split ( psText IN VARCHAR2, psSeparator IN VARCHAR2, psModifier IN VARCHAR2 := '' ) RETURN tStringArray32K;

-- Split string by separators and returns both sub-strings and separators.
PROCEDURE split (psText IN VARCHAR2, psSeparator IN VARCHAR2, parrTokens OUT tStringArray32K, parrSeparators OUT tStringArray32K);

-- Get all substrings from text which are matched to regular expression
FUNCTION substr_piped ( psText IN VARCHAR2, psMatch IN VARCHAR2, psModifier IN VARCHAR2 := '' ) RETURN tStringArray4K PIPELINED;

lib_db_object

-- Parse full object name into parts.
-- Example:
-- scott.products@db01.host.com
--	Owner: SCOTT
--	Name: PRODUCTS
--	DB Link: DB01.HOST.COM
-- If owner is NULL - then psDefaultUser parameter will be used
--
-- PARAMETERS:
--	psFullNames - list of full objects name, separated by comma. Full name should have format OWNER.NAME[@DBLINK]
--	psDefaultUser - user name to use if full name do not contains owner. If null - then takes session user.
FUNCTION ParseObjectName (psFullName IN VARCHAR2, psDefaultUser IN VARCHAR2 := NULL) RETURN trecObjectName;

lib_number

Methods for working with numbers

---------------------------------------------------------------------------------------------------
-- Convert given number in radix 10 to a number in radix N.
-- max(N) = 36; If need more - add more unique symbols to the constant DEFAULT_ALPHABET
---------------------------------------------------------------------------------------------------
FUNCTION convert_decToRadix ( pnNumber IN INTEGER, pnRadix IN PLS_INTEGER, psAlphabet IN VARCHAR2 := DEFAULT_ALPHABET)
RETURN VARCHAR2;

lib_string

Methods for working with strings and to produce strings.

-- Join sql's result into string.
-- Take all rows from one-column's cursor and make one string, where each part is separated.
-- Usage example:
-- SELECT lib_string.join(CURSOR(SELECT name FROM product WHERE area = 1)) FROM dual;
FUNCTION join ( pCursor SYS_REFCURSOR, psSeparator VARCHAR2 := ', ', pnMaxLength PLS_INTEGER := 4000) RETURN VARCHAR2;

-- Join sql's result into string.
-- Take all rows from one-column's select clause and make one string, where each part is separated.
-- Usage example:
-- SELECT lib_string.join('SELECT name FROM product WHERE area = :b1', ', ', 4000, ANYDATA.ConvertNumber(1)) FROM dual;
FUNCTION join ( psSQL VARCHAR2, psSeparator VARCHAR2 := ', ', pnMaxLength PLS_INTEGER := 4000, var1 ANYDATA := NULL, ..., var10 ANYDATA := NULL )
RETURN VARCHAR2;

-- Returns count of psExpr in psText. psExpr is regular expression.
FUNCTION substr_count ( psText IN VARCHAR2, psExpr IN VARCHAR2, psModifier IN VARCHAR2 := '' ) RETURN PLS_INTEGER;

lib_table

-- Get number of rows in table/view/mview
FUNCTION get_rowCount(psTableName IN VARCHAR2) RETURN PLS_INTEGER;

-- Check if table is empty
FUNCTION is_tableEmpty(psTableName IN VARCHAR2) RETURN BOOLEAN;

lib_user


Last edited Jan 23, 2011 at 12:23 AM by alekseiv, version 4

Comments

No comments yet.