1.0.0

ut_utils

UT_UTILS

Types

NameCodeDescription
t_event_name
subtype t_event_name           is varchar2(30);
t_executable_type
subtype t_executable_type      is varchar2(30);
t_test_result
subtype t_test_result   is binary_integer range 0 .. 3;
t_rollback_type
subtype t_rollback_type is binary_integer range 0 .. 1;
t_version
type t_version is record(
  major  natural,
  minor  natural,
  bugfix natural,
  build  natural
);
t_clob_tab
type t_clob_tab is table of clob;

Constants

NameCodeDescription
gc_version
gc_version                 constant varchar2(50) := 'v3.1.2.1964-develop';

SURROUND_WITH Function

utPLSQL - Version 3
Copyright 2016 - 2017 utPLSQL Project

Licensed under the Apache License, Version 2.0 (the "License"):
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

  http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.

Syntax

function surround_with(a_value varchar2, a_quote_char varchar2) return varchar2

VALIDATE_ROLLBACK_TYPE Procedure

Procedure: validate_rollback_type

Validates passed value against supported rollback types

Syntax

procedure validate_rollback_type(a_rollback_type number)

TEST_RESULT_TO_CHAR Function

Converts test results into strings

Syntax

function test_result_to_char(a_test_result integer) return varchar2

Parameters

NameDescription
a_test_resultnumeric representation of test result
returna string representation of a test_result.

GEN_SAVEPOINT_NAME Function

Generates a unique name for a savepoint
Uses sys_guid, as timestamp gives only miliseconds on Windows and is not unique
Issue: #506 for details on the implementation approach

Syntax

function gen_savepoint_name return varchar2

STRING_TO_TABLE Function

Splits a given string into table of string by delimiter.
The delimiter gets removed.
If null passed as any of the parameters, empty table is returned.
If no occurence of a_delimiter found in a_text then text is returned as a single row of the table.
If no text between delimiters found then an empty row is returned, example:
string_to_table( 'a,,b', ',' ) gives table ut_varchar2_list( 'a', null, 'b' );

Syntax

function string_to_table(a_string varchar2, a_delimiter varchar2:= chr(10), a_skip_leading_delimiter varchar2 := 'N') return ut_varchar2_list

Parameters

NameDescription
a_stringthe text to be split.
a_delimiterthe delimiter character or string
a_skip_leading_delimiterdetermines if the leading delimiter should be ignored, used by clob_to_table
returntable of varchar2 values

CLOB_TO_TABLE Function

Splits a given string into table of string by delimiter.
Default value of a_max_amount is 8191 because of code can contains multibyte character.
The delimiter gets removed.
If null passed as any of the parameters, empty table is returned.
If split text is longer than a_max_amount it gets split into pieces of a_max_amount.
If no text between delimiters found then an empty row is returned, example:
string_to_table( 'a,,b', ',' ) gives table ut_varchar2_list( 'a', null, 'b' );

Syntax

function clob_to_table(a_clob clob, a_max_amount integer := 8191, a_delimiter varchar2:= chr(10)) return ut_varchar2_list

Parameters

NameDescription
a_clobthe text to be split.
a_delimiterthe delimiter character or string (default chr(10) )
a_max_amountthe maximum length of returned string (default 8191)
returntable of varchar2 values

TIME_DIFF Function

Returns time difference in seconds (with miliseconds) between given timestamps

Syntax

function time_diff(a_start_time timestamp with time zone, a_end_time timestamp with time zone) return number

INDENT_LINES Function

Returns a text indented with spaces except the first line.

Syntax

function indent_lines(a_text varchar2, a_indent_size integer := 4, a_include_first_line boolean := false) return varchar2

GET_UTPLSQL_OBJECTS_LIST Function

Returns a list of object that are part of utPLSQL framework

Syntax

function get_utplsql_objects_list return ut_object_names

APPEND_TO_LIST Procedure

Append a item to the end of ut_varchar2_list

Syntax

procedure append_to_list(a_list in out nocopy ut_varchar2_list, a_item varchar2)

SET_ACTION Procedure

Set session's action and module using dbms_application_info

Syntax

procedure set_action(a_text in varchar2)

SET_CLIENT_INFO Procedure

Set session's client info using dbms_application_info

Syntax

procedure set_client_info(a_text in varchar2)

TO_VERSION Function

Converts version string into version record

Syntax

function to_version(a_version_no varchar2) return t_version

Parameters

NameDescription
a_version_nostring representation of version in format vX.X.X.X where X is a positive integer
returnt_version record with up to four positive numbers containing version

Thrown exceptions

throws 20214 if passed version string is not matching version pattern

SAVE_DBMS_OUTPUT_TO_CACHE Procedure

Saves data from dbms_output buffer into a global temporary table (cache)
used to store dbms_output buffer captured before the run

Syntax

procedure save_dbms_output_to_cache

READ_CACHE_TO_DBMS_OUTPUT Procedure

Reads data from global temporary table (cache) abd puts it back into dbms_output
used to recover dbms_output buffer data after a run is complete

Syntax

procedure read_cache_to_dbms_output

UT_OWNER Function

Function is used to reference to utPLSQL owned objects in dynamic sql statements executed from packages with invoker rights

Syntax

function ut_owner return varchar2

Parameters

NameDescription
returnthe name of the utPSQL schema owner

SCALE_CARDINALITY Function

Used in dynamic sql select statements to maintain balance between
number of hard-parses and optimiser accurancy for cardinality of collections

Syntax

function scale_cardinality(a_cardinality natural) return natural

Parameters

NameDescription
return3, for inputs of: 1-9; 33 for input of 10 - 99; 333 for (100 - 999)

TO_XML_NUMBER_FORMAT Function

Returns number as string. The value is represented as decimal according to XML standard:
https://www.w3.org/TR/xmlschema-2/#decimal

Syntax

function to_xml_number_format(a_value number) return varchar2

TRIM_LIST_ELEMENTS Function

It takes a collection of type ut_varchar2_list and it trims the characters passed as arguments for every element

Syntax

function trim_list_elements(a_list IN ut_varchar2_list, a_regexp_to_trim in varchar2 default '[:space:]') return ut_varchar2_list

FILTER_LIST Function

It takes a collection of type ut_varchar2_list and it only returns the elements which meets the regular expression

Syntax

function filter_list(a_list IN ut_varchar2_list, a_regexp_filter in varchar2) return ut_varchar2_list

REPLACE_MULTILINE_COMMENTS Function

Replaces multi-line comments in given source-code with empty lines

Syntax

function replace_multiline_comments(a_source clob) return clob