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 |
Wiki supplied Functions#
Oracle Built-in Functions#
Conversion Functions#
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 |
LAST_DAY | returns the last day of the month |
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 |
Click to create a new notes page
Screen captures are meant to be indicative of the concept being presented and may not reflect the current screen design.
If you have any comments or questions please email the Wiki Editor
All content © High Line Corporation