Page 153
Table 7.3. Date functions.
| Function Name | Function Description |
| ADD_MONTHS | Adds one month to the date specified. It does not add 30 or 31 days, but simply adds one to the month. If the resulting month has fewer days, it will return the last day of that month instead. |
| LAST_DAY | Returns the last day of the given month. A very useful function, especially for programming in accounting departments. |
| MONTHS_BETWEEN | Computes the months between two dates. Returns an integer if both dates are the last days of the month; otherwise, it returns the fractional portion of a 31-day month. |
| NEW_TIME | Returns the time/day value from a time zone specified by the user. |
| NEXT_DAY | Returns the date of the first day of the week specified in a string after the beginning date. |
| ROUND | Gives you full flexibility to round to the nearest date parameter of your choice, such as month, year, century, and so on. |
| SYSDATE | Simply returns the system date and time in type DATE format. |
| TRUNC | Truncates up to the specified date parameter, such as day, month, and so on. |
Table 7.4. Conversion functions.
| Function Name | Function Description |
| CHARTOROWID | Converts CHAR or VARCHAR2 from the external format provided by Oracle to its internal binary format. |
| CONVERT | Converts from one character set to another character set. |
| HEXTORAW | Converts hex string values to internal raw values. |
| RAWTOHEX | Converts internal raw values to an external hex string. |
| ROWIDTOCHAR | Converts the ROW ID into its external 18-character string representation. |
| TO_CHAR | Converts DATES, MLSLABELS, and NUMBERS to a VARCHAR2 string. |
| TO_DATE | Converts a CHAR or VARCHAR2 string into a DATE value. |
| TO_LABEL | Converts a CHAR or VARCHAR2 string into an MLSLABEL. |
continues
Page 154
Table 7.4. continued
| Function Name | Function Description |
| TO_MULTI_BYTE | Converts any single-byte string of characters into a multibyte string. |
| TO_NUMBER | Converts a CHAR or VARCHAR2 string into a NUMBER value. |
| TO_SINGLE_BYTE | Converts any multibyte string of characters into a single-byte string. |
Table 7.5. Grouping functions.
| Function Name | Function Description |
| AVG | Average of a column of values |
| COUNT | Total count of rows returned in a query |
| GLC | Greatest lower bound of an MLSLABEL |
| LUB | Least upper bound of an MLSLABEL |
| MAX | Returns the largest value of a row in a column from a query |
| MIN | Returns the smallest value of a row in a column from a query |
| STDDEV | Returns the standard deviation of a selected column in a query |
| SUM | Returns the SUM of a selected column in a query |
| VARIANCE | Returns the statistical VARIANCE of a selected column in a query |
Table 7.6. Miscellaneous functions.
| Function Name | Function Description |
| BFILENAME | Similar to C language. Returns a pointer, which is referred to as a locator in Oracle, to the associated physical LOB binary file where the file is stored. |
| DECODE | Acts like nested IF...THEN...ELSE statement from a list of values. |
| DUMP | Provides a dump of values in a string VARCHAR2 to see the representation in many different formats. |
| EMPTY_BLOB | Used to initialize a BLOB variable or column that will contain no data. |
| EMPTY_CLOB | Used to initialize a CLOB variable or column that will contain no data. |
Page 155
| Function Name | Function Description |
| GREATEST | Takes a list of values or expressions and returns the largest evaluated value. |
| GREATEST_LB | Takes a list of MLSLABELS and returns the greatest lower bound. |
| LEAST | Takes a list of values or expressions and returns the smallest evaluated value. |
| LEAST_LB | Takes a list of MLSLABELS and returns the least lower bound. |
| NLS_CHARSET_ID | Returns the NLS character set ID number associated with the NLS character set name. |
| NLS_CHARSET_NAME | Returns the NLS character set name associated with the ID passed to the function. |
| NVL | Selects the first non-null value from a list of values or expressions. |
| SQLCODE | Returns an error code based upon the current error. |
| SQLERRM | Returns the error message associated with the Oracle error code. |
| UID | Returns the USER ID assigned to the user in Oracle. |
| USER | Returns the name of the current user in a VARCHAR2 string. |
| USERENV | Returns information about your current working environment. |
| VSIZE | Returns the number of bytes in some value. |
The best way to understand functions in Oracle is to test all of them out with your own data. However, the easiest way is to use SQL*Plus and the SQL command SELECT FUNCTION(arguments,column_headings) from DUAL to test these functions. The examples in today's lesson use this method.
| NOTE |
What is DUAL and why would you select from DUAL? The DUAL table is simply a standard Oracle table, which is used as a dummy table to evaluate a condition to true. When you are testing your functions while selecting from DUAL, the table allows you to return one result. Use DUAL only when data itself is irrelevant. |
Page 156
Some of the most important and widely used functions are the conversion functions. These functions allow you to convert from one datatype to another datatype. This section discusses two major types of conversions: going from a CHAR or VARCHAR2 datatype to either a NUMBER or a DATE, or converting from a DATE or a NUMBER datatype to a VARCHAR2.
The TO_DATE function converts a character string (CHAR or VARCHAR2) as denoted by the apostrophe (`) surrounding the character string to an actual DATE value.
The Syntax for the TO_DATE Function
TO_DATE(character string, format, NLS_DATE_LANGUAGE)
The format parameter is optional, and if it is not used, the default DATE format will apply, which is DD-MMM-YY. The format must be the representation of the character date you are supplying to convert.
| TIP |
Always make sure that you specify the proper format for the date you are supplying; otherwise, you will get to know the Oracle error messages very well. For instance, if you forgot to use the format option and passed `061167', Oracle would return an error message stating that the month is invalid. Considering that the default format is DD-MMM-YY, it's easy to see that the day would be 06, but the month would not be proper at 116. |
The TO_DATE function has some limitations:
Refer to Table 7.7 for the available format masks you can pass when using the TO_DATE function.