For purposes of interfacing, writing scripts or end user reporting, there are a number of data base functions that can be used - both from Oracle and those created as part of the application. As well, customers could create their own custom data base function for this purpose.

The functions listed below are those found to be useful in the creation of interfaces, conversion, script writing or reporting. It is not an exhaustive list, and can certainly be extended by a customer adding their own functions.

Calling Functions in SQL Loader

Calling Functions in IDIF (Derivation Expression) – export interfaces

Personality supplied Functions#

Oracle Built-in Functions#

ORACLE CASE WHEN Functionality

Complex If-Then-Else logic

Conversion Functions#

TO_CHAR	Converting a numeric or date field to a character field (often with formatting)
TO_DATE(Oracle_Function)	Converting a character field to a date (often with formatting)
TO_NUMBER	Converting a character field to a number(often with formatting)
DATE FORMAT MODELS (Oracle) for TO_CHAR and TO_DATE	Format mask options
NUMBER FORMAT MODELS (Oracle) for TO_CHAR	Format mask options

Text Functions#

CONCAT	Concatenation of two strings - usually easier to use two pipes (eg. ||)
SUBSTR	Extracting a substring out of a larger text string
INITCAP , UPPER and LOWER	Reformatting a text string's capital letters
INSTR	Determining if one string is inside another
LENGTH	Determining the length of the string
LPAD and RPAD	Left- and Right-padding character fields with another value
TRIM	Trimming off characters (usually blanks)
LTRIM and RTRIM	Trimming off characters (usually blanks) from teh left or right side of the string
REPLACE	Replace one value in a string with another

Numeric Functions#

ABS	Absolute Value (removing the - on negative values)
CEIL	Rounding a number up to the next integer
EXTRACT	Extracts a year, month or day value from a date
FLOOR	Rounding a number down to the previous integer
MOD and REMAINDER	Modulus division (determining the remainder) or just returning the remainder
ROUND	Rounding a number up/down and to a specific number of decimal places
TRUNC	Truncating the decimal part of a number
SIGN	Determining the +/- sign of a number

Date Functions#

SYSDATE	Getting the current date/time
LAST_DAY	Determining the last day of a month
ADD_MONTHS	Adding a number of months to a date
NEXT_DAY	Returns the first weekday that is greater than a specified date
MONTHS_BETWEEN	Determining the number of months between dates
TRUNC (Date usage)	Round a date to a nearest unit (usually to midnight)

Miscellaneous Functions#

DECODE	Translating a finite set of values from one set to another
GREATEST and LEAST	Determining the greatest (maximum) and least (minimum) value between sets of values
MAX and MIN	Returns the maximum and minimum values from an expression
NVL	testing for and resolving NULL values
NVL2	testing for a resolving NULL and NOT NULL values
SUM	Returns the summed value of an expression
COALESCE	testing for and resolving multiple NULL values
SQLCODE	returns Oracle error message number from the most recently raised exception
SQLERRM	returns Oracle error message text from the most recently raised exception
USER	returns the Oracle database username of your session
UID	returns the Oracle Session UserID number for your session
SYS_CONTEXT-USERENV	Return various values related to your current database session

---

Notes #

Click to create a new notes page