Oracle PLSQL algorithms, useful functions and procedures

This repository contains algorithms, useful functions and procedures based on PLSQL and Java in Oracle Database.

GEO

• Functions
  • DISTANCE BETWEEN - returns the ditance between two coordinates in meters, taking into account the radius of the Earth.

STRING / CLOB

• Functions

  • SPLIT - splits the input string (varchar2 or clob) by pattern and outputs each substring in a separate line. Optimized for CLOB type inputs.
  • CLOB REPLACE - returns new clob with every occurrence of search_string replaced with replacement_string.
  • IS NUMBER - returns 1 if the string consists of only digits, 0 if string is null or string length = 0.
  • GET DATE - returns valid date or NULL, tries to convert a string to a date based on an array of masks.

• Procedures

  • CLOB REPLACE - returns new clob with every occurrence of search_string replaced with replacement_string.

UTILS

• FILE SYSTEM

  • READ DIRECTORY - JAVA procedure reads the list of files in the directory and puts it in a temporary table.

• BACKUP

  • DB SOURCE - a simple backup of dba_source to the dump files and a simple reading of these files on the required date.

SQL

• DELETE
  • DELETE DUPLICATES - removing duplicates from a table without temporary tables.
  • DELETE HISTORY - removing historical data from a table without temporary tables.