PLSQL-Libraries

PL/SQL Libraries of various types.

Packages

• Constants - Constants used by this library
• CLOB_Utils - various utilities for sliceing and dicing CLOBs
• Log_Utils - conditional blocks (and formats) logs. (for STDERR_t
• Generate_Series - Generate a series of Integers, Days, Months, or Weeks

SQL Data Types

• Hash_t - PL/SQL Hash implementation using json_object_t
• Iterator_t - iterates an array of Hash_t generated by a sys_refcursor
• STDERR_t - A DBMS_OUTPUT data type for logging purpose
• STDOUT_t - A 'DBMS_OUTPUT` data type for fancy (code style) printing
• INT_array - VARRAY of Integers for STDOUT_t
• CLOB_array - VARRAY of CLOBs for STDOUT_t

Domains (23c+)

• validators for various string/date/number/intervals formats
• various static value validators
  • number is integer, above 0/1
  • date is day/month/year
  • string is not null and/or trimmed and/or ( upper or lower )

UT Packages

Coming soon

Security

All Packages and Types are Invoker's Rights. All Pacakages and Types have been granted access to PUBLIC.

Public Synonym is (optionally) available by prefixing the object name with MKL_

• MKL_Hash_t
• MKL_STDOUT_t
• etc

Installation

Easy Installation

For Oracle Cloud Free Tier, run this from SQLPlus/SQLDeveloper/SQLcl command prompt as ADMIN. (This one installs the current version)

OCI Cloud Installation

@@https://raw.githubusercontent.com/MikeKutz/PLSQL-Libraries/main/cloud-install.sql

Local 23c Free

@@https://raw.githubusercontent.com/MikeKutz/PLSQL-Libraries/main/install_local_23c.sql

TYPES

Hash_t

Persist hash-like data. (Associative Array in PL/SQL)

TBD

Iterator_t

Iterate over a sys_refcursor (from a function) or a JSON Array (in a Hash_t format)

TBD

STDOUT_t

Cache print() statements similar to dbms_output. But, adds block indention and tab-stops.

TBD

STDERR_t

Logging-like utility based on STDOUT_t.

TBD

PACKAGES

JSON_Path_utils

TBD

Generate_Series

Macro name	Description
of_numbers	Generate a series of n integers starting with start_value
of_days	Generate a series of Days starting with start_value
of_weeks	Generate a series of Weeks starting with the week given by start_value
of_months	Generate a series of Months starting with the week given by start_value

Example Usage:

with data (dt) as (
    select to_date( '5-nov-1955', 'dd-mon-yyyy') from dual union all
    select sysdate from dual
)
select d.dt, b.*
from data d
    cross join lateral  (
        select *
        from generate_series.of_weeks( d.dt, 3 )
    ) b

Results:

DT        WEEK_STAR WEEK_END  WEEK_PERI     WEEK_N   WEEK_N_0   ISO_WEEK   ISO_YEAR ORACLE_QUARTER
--------- --------- --------- --------- ---------- ---------- ---------- ---------- --------------
05-NOV-55 30-OCT-55 05-NOV-55 06-NOV-55          1          0         44       1955              4
05-NOV-55 06-NOV-55 12-NOV-55 13-NOV-55          2          1         45       1955              4
05-NOV-55 13-NOV-55 19-NOV-55 20-NOV-55          3          2         46       1955              4
12-MAY-22 08-MAY-22 14-MAY-22 15-MAY-22          1          0         19       2022              2
12-MAY-22 15-MAY-22 21-MAY-22 22-MAY-22          2          1         20       2022              2
12-MAY-22 22-MAY-22 28-MAY-22 29-MAY-22          3          2         21       2022              2

6 rows selected.