This work is licensed under a Creative Commons Attribution 4.0 International License.

PL/SQL Commons

Contains common utility and logging methods.

INSTALLATION

You can put the objects under any schema you like, but you can create a utility schema ,if you do not have already, and put all the objects under that schema.

• Create a schema named util with:

    create user util identified by <password>;
  
    grant connect, resource to util;

• Change the current schema to util

    alter session set current_schema = util;

• Grant privileges

    GRANT SELECT ON dba_constraints TO util;
    
    GRANT SELECT ON dba_indexes TO util;

• Run the contents of logs.ddl.sql

• Run the contents of logtype.pks.sql and logtype.pkb.sql in order.

• Run the contents of pl.pks.sql and pl.pkb.sql in order.

• Optionally create a public synonym for pl with;

    create public synonym pl for util.pl;  
  
    grant execute on util.logtype to public;
  
    grant execute on util.pl to public;

API

• is_number

  function is_number(piv_str varchar2) return boolean

  Checks if string is classified as a Number or not.
  
  Arguments: 
     [piv_str='']    (varchar2): The string to check.
  Returns
     (boolean): Returns true if string is numeric.

• split

  function split(
    piv_str varchar2, 
    piv_split varchar2 default ',', 
    pin_limit number default null
  ) return dbms_sql.varchar2_table

    Splits string by separator.
  
    Arguments: 
       [piv_str='']    (varchar2): The string to split.
       [piv_split=','] (varchar2): The separator pattern to split by.
       [pin_limit]     (number): The length to truncate results to.
    Returns
       (varchar2_table): Returns the string segments.

• date_string

  function date_string(pid_date date) return varchar2

  Returns a date as string containing to_date function. 
  Useful when used with 'execute immediate'
  
  Arguments: 
     [pid_date] (date): The date object.
  Returns:
     (varchar2): the date function string
     example: 'to_date(''20120101 22:12:00'',''yyyymmdd hh24:mi:ss'')' 

• truncate_table

  procedure truncate_table(piv_owner varchar2, piv_table varchar2)

  Truncates the given table
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table

• drop_table

  procedure drop_table(piv_owner varchar2, piv_table varchar2, pib_ignore_err boolean default true);
  

  Drops the given table
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [pib_ignore_err=true] (boolean): when set to false raises error

• table_exists

  function table_exists(piv_owner varchar2, piv_table varchar2) return boolean;

  Checks whether the given table exists or not 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
  Returns:
     (boolean): true if table exists

• gather_table_stats

  procedure gather_table_stats(
    piv_owner varchar2, 
    piv_table varchar2, 
    piv_part_name varchar2 default null) 

  Gather table/partition statistics 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [piv_part_name] (varchar2): Name of the partition defaults to null

• manage_constraints

  procedure manage_constraints(
    piv_owner varchar2, 
    piv_table varchar2, 
    piv_order varchar2 default 'enable') 

  Enable/Disable constraints for the given table. 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [piv_order] (varchar2): DISABLE|ENABLE

• enable_constraints

  procedure enable_constraints(
    piv_owner varchar2, 
    piv_table varchar2) 

  Enable constraints for the given table. 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table

• disable_constraints

  procedure disable_constraints(
    piv_owner varchar2, 
    piv_table varchar2) 

  Disable constraints for the given table. 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table

• manage_indexes

  procedure manage_indexes(
    piv_owner varchar2, 
    piv_table varchar2, 
    piv_order varchar2 default 'enable') 

  Unusable/Rebuild indexes for the given table. 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [piv_order] (varchar2): DISABLE|ENABLE
      DISABLE makes the indexes unusable
      ENABLE rebuilds the indexes

• enable_indexes

  procedure enable_indexes(
    piv_owner varchar2, 
    piv_table varchar2) 

  Rebuild indexes for the given table. 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table

• disable_indexes

  procedure disable_indexes(
    piv_owner varchar2, 
    piv_table varchar2) 

  Make indexes unusable for the given table. 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table

• add_partitions

  procedure add_partitions(piv_owner varchar2, piv_table varchar2, pid_date date)

  Adds partitions to the given table up to the date given by the 'pid_date' parameter. 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [pid_date] (date): the date up to partitions will be added 

• add_partition

  procedure add_partition (piv_owner varchar2, piv_table varchar2,pid_date date)

  Adds a single partition to the given table with the date given by the 'pid_date' parameter. 
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [pid_date] (date): the date partition will be created for 

• truncate_partition

  procedure truncate_partition(piv_owner varchar2, piv_table varchar2, piv_partition varchar2)
  

  Truncates the given partition.
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [piv_partition] (varchar2): name of the partition 

• drop_partition

  procedure drop_partition(piv_owner varchar2, piv_table varchar2, piv_partition varchar2)
  

  Drops the given partition.
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [piv_partition] (varchar2): name of the partition 

• drop_partition_lt

  procedure drop_partition_lt (piv_owner varchar2, piv_table varchar2, pid_date date); 

  Drops partitions less than the given date.
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [pid_date] (varchar2): date boundary 

• drop_partition_lte

  procedure drop_partition_lte(piv_owner varchar2, piv_table varchar2, pid_date date)

  Drops partitions less than or equal to the given date.
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [pid_date] (varchar2): date boundary 

• drop_partition_gt

  procedure drop_partition_gt (piv_owner varchar2, piv_table varchar2, pid_date date)

  Drops partitions greater than the given date.
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [pid_date] (varchar2): date boundary 

• drop_partition_gte

  procedure drop_partition_gte (piv_owner varchar2, piv_table varchar2, pid_date date)

  Drops partitions greater than or equal to the given date.
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [pid_date] (varchar2): date boundary 

• window_partitions

  procedure window_partitions(
    piv_owner varchar2, 
    piv_table varchar2, 
    pid_date date, 
    pin_window_size number)

  Manages partitions for the given table by fitting the partitions to the given date with pid_date parameter
  and given number by pin_number_size parameter. Basically it adds partitions until pid_date and drops partitions
  older than pin_window_size * (year|month|day)
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [pid_date] (varchar2): date boundary 
     [pin_window_size] (number): number of partitions to keep

• exchange_partition

    procedure exchange_partition(
      piv_owner     varchar2, 
      piv_table_1   varchar2, 
      piv_part_name varchar2,
      piv_table_2   varchar2,
      pib_validate  boolean default false
    );

  Exchanges partition of table_1 with the table_2
  
  Arguments: 
     [piv_owner] (varchar2): Schema of the table
     [piv_table] (varchar2): Name of the table
     [piv_part_name] (varchar2): partitions to be exchanged 
     [piv_table_2] (varchar2): table to replace partition
     [pib_validate=false] (boolean): validate partition after exchange

• enable_parallel_dml

  procedure enable_parallel_dml

  Enable parallel dml for the current session.     

• disable_parallel_dml

  procedure disable_parallel_dml

  Disable parallel dml for the current session.     

• async_exec

    procedure async_exec(piv_sql varchar2)

  Execute given statement asynchronously.
  
  Arguments: 
     [piv_sql] (varchar2): Statement to execute 

• set_param

    procedure set_param(i_name varchar2, i_value)

  Set parameter on `params` table
  
  Arguments: 
    [i_name] (varchar2): parameter name 
    [i_value] (varchar2): parameter value

• find_param

    procedure find_param(i_name varchar2)

  Find given parameter
  
  Arguments: 
    [i_name] (varchar2): parameter name 
  Returns
    (varchar2): Returns parameter value

• param_exists

    procedure param_exists(i_name varchar2)

    Check whether given parameter exists.
  
    Arguments: 
      [i_name] (varchar2): parameter name 
    Returns
      (boolean): true if param exists false otherwise

• send_mail

    procedure send_mail(
      i_to      varchar2,
      i_subject varchar2,
      i_body    varchar2,
      i_cc      varchar2  default null
      i_from    varchar2  default null
    )

    Send mail to given recipients. Set mail server settings on `params` before
    using this method!

• print_locks

  procedure print_locks

  Print locked objects.

• println

  procedure println(piv_message varchar2);

  Print to dbms out. Shortcut for dbms_output.put_line
  
  Arguments: 
     [piv_message] (varchar2): Message to print

• printl

  procedure printl(piv_message varchar2);

  Print to dbms out. Shortcut for dbms_output.put_line
  
  Arguments: 
     [piv_message] (varchar2): Message to print

• p

  procedure p(piv_message varchar2);

  Print to dbms out. Shortcut for dbms_output.put_line
  
  Arguments: 
     [piv_message] (varchar2): Message to print

• print

  procedure print(piv_message varchar2);

  Print to dbms out. Shortcut for dbms_output.put
  
  Arguments: 
     [piv_message] (varchar2): Message to print