A format model is a character literal that describes the format of DATE or NUMBER data stored in a character string. You can use a format model as an argument of the TO_CHAR or TO_DATE function:
Note that a format model does not change the internal representation of the value in the database.
This section describes how to use:
You can use a format model to specify the format for Oracle to use to return values from the database to you.
The following statement selects the commission values of the employees in Department 30 and uses the TO_CHAR function to convert these commissions into character values with the format specified by the number format model '$9,990.99':
SELECT ename employee, TO_CHAR(comm, '$9,990.99') commission FROM emp WHERE deptno = 30; EMPLOYEE COMMISSION ---------- ---------- ALLEN $300.00 WARD $500.00 MARTIN $1,400.00 BLAKE TURNER $0.00 JAMES
Because of this format model, Oracle returns commissions with leading dollar signs, commas every three digits, and two decimal places. Note that TO_CHAR returns null for all employees with null in the COMM column.
The following statement selects the date on which each employee from department 20 was hired and uses the TO_CHAR function to convert these dates to character strings with the format specified by the date format model 'fmMonth DD, YYYY':
SELECT ename, TO_CHAR(Hiredate,'fmMonth DD, YYYY') hiredate
FROM emp
WHERE deptno = 20;
ENAME HIREDATE
---------- ------------------
SMITH December 17, 1980
JONES April 2, 1981
SCOTT April 19, 1987
ADAMS May 23, 1987
FORD December 3, 1981
LEWIS October 23, 1997
With this format model, Oracle returns the hire dates with the month spelled out (as specified by "fm" and discussed in "Format Model Modifiers"), two digits for the day, and the century included in the year.
You can use format models to specify the format of a value that you are converting from one datatype to another datatype required for a column. When you insert or update a column value, the datatype of the value that you specify must correspond to the column's datatype. For example, a value that you insert into a DATE column must be a value of the DATE datatype or a character string in the default date format (Oracle implicitly converts character strings in the default date format to the DATE datatype). If the value is in another format, you must use the TO_DATE function to convert the value to the DATE datatype. You must also use a format model to specify the format of the character string.
The following statement updates BAKER's hire date using the TO_DATE function with the format mask 'YYYY MM DD' to convert the character string '1992 05 20' to a DATE value:
UPDATE emp
SET hiredate = TO_DATE('1992 05 20','YYYY MM DD')
WHERE ename = 'BLAKE';
You can use number format models
All number format models cause the number to be rounded to the specified number of significant digits. If a value has more significant digits to the left of the decimal place than are specified in the format, pound signs (#) replace the value. If a positive value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value. Likewise, if a negative value is extremely small and cannot be represented by the specified format, then the negative infinity sign replaces the value (-~).
A number format model is composed of one or more number format elements. Table 3-12 lists the elements of a number format model. Examples are shown in Table 3-13.
Table 3-13 shows the results of the following query for different values of number and 'fmt':
SELECT TO_CHAR(number, 'fmt') FROM DUAL
The MI and PR format elements can appear only in the last position of a number format model. The S format element can appear only in the first or last position of a number format model.
The characters returned by some of these format elements are specified by initialization parameters. Table 3-14 lists these elements and parameters.
You can specify the characters returned by these format elements implicitly using the initialization parameter NLS_TERRITORY. For information on these parameters, see Oracle8 Reference.
You can change the characters returned by these format elements for your session with the ALTER SESSION command. You can also change the default date format for your session with the ALTER SESSION command. For information, see ALTER SESSION.
You can use date format models
The default date format is specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY. For information on these parameters, see Oracle8 Referenceee.
You can change the default date format for your session with the ALTER SESSION command. For information, see ALTER SESSION.
The total length of a date format model cannot exceed 22 characters.
A date format model is composed of one or more date format elements as listed in Table 3-15. For input format models, format items cannot appear twice, and format items that represent similar information cannot be combined. For example, you cannot use 'SYYYY' and 'BC' in the same format string. Only some of the date format elements can be used in the TO_DATE function as noted in Table 3-15.
Table 3-15 Date Format ElementsOracle returns an error if an alphanumeric character is found in the date string where punctuation character is found in the format string. For example:
TO_CHAR (TO_DATE('0297','MM/YY'), 'MM/YY')
returns an error.
The functionality of some date format elements depends on the country and language in which you are using Oracle. For example, these date format elements return spelled values:
The language in which these values are returned is specified either explicitly with the initialization parameter NLS_DATE_LANGUAGE or implicitly with the initialization parameter NLS_LANGUAGE. The values returned by the YEAR and SYEAR date format elements are always in English.
The date format element D returns the number of the day of the week (1-7). The day of the week that is numbered 1 is specified implicitly by the initialization parameter NLS_TERRITORY.
For information on these initialization parameters, see Oracle8 Reference.
Oracle calculates the values returned by the date format elements IYYY, IYY, IY, I, and IW according to the ISO standard. For information on the differences between these values and those returned by the date format elements YYYY, YYY, YY, Y, and WW, see the discussion of national language support in Oracle8 Reference.
The RR date format element is similar to the YY date format element, but it provides additional flexibility for storing date values in other centuries. The RR date format element allows you to store 21st century dates in the 20th century by specifying only the last two digits of the year. It will also allow you to store 20th century dates in the 21st century in the same way if necessary.
If you use the TO_DATE function with the YY date format element, the date value returned is always in the current century. If you use the RR date format element instead, the century of the return value varies according to the specified two-digit year and the last two digits of the current year. Table 3-16 summarizes the behavior of the RR date format element.
The following example demonstrates the behavior of the RR date format element.
Assume these queries are issued between 1950 and 1999:
SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "Year"
FROM DUAL;
Year
----
1995
SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year";
FROM DUAL;
Year
----
2017
Assume these queries are issued between 2000 and 2049:
SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "Year";
FROM DUAL;
Year
----
1995
SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year";
FROM DUAL;
Year
----
2017
Note that the queries return the same values regardless of whether they are issued before or after the year 2000. The RR date format element allows you to write SQL statements that will return the same values after the turn of the century.
Table 3-17 lists suffixes that can be added to date format elements:
| Suffix | Meaning | Example Element | Example Value |
|---|---|---|---|
|
TH |
Ordinal Number |
DDTH |
4TH |
|
SP |
Spelled Number |
DDSP |
FOUR |
|
SPTH or THSP |
Spelled, ordinal number |
DDSPTH |
FOURTH |
When you add one of these suffixes to a date format element, the return value is always in English.
Capitalization in a spelled-out word, abbreviation, or Roman numeral follows capitalization in the corresponding format element. For example, the date format model 'DAY' produces capitalized words like 'MONDAY'; 'Day' produces 'Monday'; and 'day' produces 'monday'.
You can also include these characters in a date format model:
These characters appear in the return value in the same location as they appear in the format model.
You can use the FM and FX modifiers in format models for the TO_CHAR function to control blank padding and exact format checking.
A modifier can appear in a format model more than once. In such a case, each subsequent occurrence toggles the effects of the modifier. Its effects are enabled for the portion of the model following its first occurrence, and then disabled for the portion following its second, and then reenabled for the portion following its third, and so on.
"Fill mode". This modifier suppresses blank padding in the return value of the TO_CHAR function:
"Format exact". This modifier specifies exact matching for the character argument and date format model of a TO_DATE function:
When FX is enabled, you can disable this check for leading zeroes by using the FM modifier as well.
If any portion of the character argument violates any of these conditions, Oracle returns an error message.
The following statement uses a date format model to return a character expression:
SELECT TO_CHAR(SYSDATE, 'fmDDTH')||' of '||TO_CHAR
(SYSDATE, 'Month')||', '||TO_CHAR(SYSDATE, 'YYYY') "Ides"
FROM DUAL;
Ides
------------------
3RD of April, 1995
Note that the statement above also uses the FM modifier. If FM is omitted, the month is blank-padded to nine characters:
SELECT TO_CHAR(SYSDATE, 'DDTH')||' of '|| TO_CHAR(Month, YYYY') "Ides" FROM DUAL; Ides ----------------------- 03RD of April , 1995
The following statement places a single quotation mark in the return value by using a date format model that includes two consecutive single quotation marks:
SELECT TO_CHAR(SYSDATE, 'fmDay')||'''s Special') "Menu"
FROM DUAL;
Menu
-----------------
Tuesday's Special
Two consecutive single quotation marks can be used for the same purpose within a character literal in a format model.
Table 3-18 shows whether the following statement meets the matching conditions for different values of char and 'fmt' using FX:
UPDATE table SET date_column = TO_DATE(char, 'fmt');
The following additional formatting rules apply when converting string values to date values:
| Original Format Element | Additional Format Elements to Try in Place of the Original |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
A decoded expression uses the special DECODE syntax:
To evaluate this expression, Oracle compares expr to each search value one by one. If expr is equal to a search, Oracle returns the corresponding result. If no match is found, Oracle returns default, or, if default is omitted, returns null. If expr and search contain character data, Oracle compares them using nonpadded comparison semantics. For information on these semantics, see the section"Datatype Comparison Rules".
The search, result, and default values can be derived from expressions. Oracle evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle never evaluates a search if a previous search is equal to expr.
Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2. For information on datatype conversion, see "Data Conversion".
In a DECODE expression, Oracle considers two nulls to be equivalent. If expr is null, Oracle returns the result of the first search that is also null.
The maximum number of components in the DECODE expression, including expr, searches, results, and default is 255.
This expression decodes the value DEPTNO. If DEPTNO is 10, the expression evaluates to 'ACCOUNTING'; if DEPTNO is 20, it evaluates to 'RESEARCH'; etc. If DEPTNO is not 10, 20, 30, or 40, the expression returns 'NONE'.
DECODE (deptno,10, 'ACCOUNTING',
20, 'RESEARCH',
30, 'SALES',
40, 'OPERATION',
'NONE')
Form XIA Form XI expression specifies attribute reference and method invocation.
expr_form_XI::=
The column parameter can be an object or REF column.
Examples in this section use the following user-defined types and tables:
CREATE OR REPLACE TYPE employee_t AS OBJECT
(empid NUMBER,
name CHAR(31),
birthdate DATE,
MEMBER FUNCTION age RETURN NUMBER,
PRAGMA RESTRICT REFERENCES(age, RNPS, WNPS, WNDS)
);
CREATE OR REPLACE TYPE BODY employee_t AS
MEMBER FUNCTION age RETURN NUMBER IS
var NUMBER;
BEGIN
var := months_between(ROUND(SYSDATE, 'YEAR'),
ROUND(birthdate, 'YEAR'))/12;
RETURN(var);
END;
END; /
CREATE TABLE department (dno NUMBER, manager EMPLOYEE_T);
The following examples update and select from the object columns and method defined above.
UPDATE department d SET d.manager.empid = 100; SELECT d.manager.name, d.manager.age() FROM department d;
Form VIA Form VI expression specifies a call to a type constructor.
If type_name is an object type, then the type argument list must be an ordered list of arguments, where the first argument is a value whose type matches the first attribute of the object type, the second argument is a value whose type matches the second attribute of the object type, and so on. The total number of arguments to the constructor must match the total number of attributes of the object type; the maximum number of arguments is 999.
If type_name is a VARRAY or nested table type, then the argument list can contain zero or more arguments. Zero arguments imply construction of an empty collection. Otherwise, each argument corresponds to an element value whose type is the element type of the collection type.
Whether type_name is an object type, a VARRAY, or a nested table type, the maximum number of arguments it can contain is 999.
CREATE TYPE address_t AS OBJECT
(no NUMBER, street CHAR(31), city CHAR(21), state CHAR(3), zip NUMBER);
CREATE TYPE address_book_t AS TABLE OF address_t;
DECLARE
/* Object Type variable initialized via Object Type Constructor */
myaddr address_t = address_t(500, 'Oracle Parkway', 'Redwood Shores',
'CA', 94065);
/* nested table variable initialized to an empty table via a
constructor*/
alladdr address_book_t = address_book_t();
BEGIN
/* below is an example of a nested table constructor with two elements
specified, where each element is specified as an object type
constructor. */
insert into employee values (666999, address_book_t(address_t(500,
'Oracle Parkway', 'Redwood Shores', 'CA', 94065), address_t(400,
'Mission Street', 'Fremont', 'CA', 94555)));
END;
Form VIIA Form VII expression converts one collection-typed value into another collection-typed value.
CAST allows you to convert collection-typed values of one type into another collection type. You can cast an unnamed collection (such as the result set of a subquery) or a named collection (such as a VARRAY or a nested table) into a type-compatible named collection. The type_name must be the name of a collection type and the operand must evaluate to a collection value.
To cast a named collection type into another named collection type, the elements of both collections must be of the same type.
If the result set of subquery can evaluate to multiple rows, you must specify the MULTISET keyword. The rows resulting from the subquery form the elements of the collection value into which they are cast. Without the MULTISET keyword, the subquery is treated as a scalar subquery, which is not supported in the CAST expression. In other words, scalar subqueries as arguments of the CAST operator are not valid in Oracle8.
The CAST examples that follow use the following user-defined types and tables:
CREATE TYPE address_t AS OBJECT
(no NUMBER, street CHAR(31), city CHAR(21), state CHAR(2));
CREATE TYPE address_book_t AS TABLE OF address_t;
CREATE TYPE address_array_t AS VARRAY(3) OF address_t;
CREATE TABLE emp_address (empno NUMBER, no NUMBER, street CHAR(31),
city CHAR(21), state CHAR(2));
CREATE TABLE employees (empno NUMBER, name CHAR(31));
CREATE TABLE dept (dno NUMBER, addresses address_array_t);
CAST a subquery:
SELECT e.empno, e.name, CAST(MULTISET(SELECT ea.no, ea.street,
ea.city, ea.state
FROM emp_address ea
WHERE ea.empno = e.empno)
AS address_book_t)
FROM employees e;
CAST converts a VARRAY type column into a nested table. The table values are generated by a flattened subquery. See "Using Flattened Subqueries".
SELECT *
FROM THE(SELECT CAST(d.addresses AS address_book_t)
FROM dept d
WHERE d.dno = 111) a
WHERE a.city = 'Redwood Shores';
The following example casts a MULTISET expression with an ORDER BY clause:
CREATE TABLE projects (empid NUMBER, projname VARCHAR2(10)); CREATE TABLE employees (empid NUMBER, ename VARCHAR2(10)); CREATE TYPE projname_table_type AS TABLE OF VARCHAR2(10);
An example of a MULTISET expression with the above schema is:
SELECT e.name, CAST(MULTISET(SELECT p.projname
FROM projects p
WHERE p.empid=e.empid
ORDER BY p.projname)
AS projname_table_type)
FROM employees e;
Form VIIIA Form VIII expression returns a nested CURSOR. This form of expression is similar to the PL/SQL REF cursor.
A nested cursor is implicitly opened when the containing row is fetched from the parent cursor. The nested cursor is closed only when
The following restrictions apply to the CURSOR expression:
SELECT d.deptno, CURSOR(SELECT e.empno, CURSOR(SELECT p.projnum,
p.projname
FROM projects p
WHERE p.empno = e.empno)
FROM TABLE(d.employees) e)
FROM dept d
WHERE d.dno = 605;
Form IXA Form IX expression constructs a reference to an object.
In a SQL statement, REF takes as its argument a table alias associated with a row of an object table or an object view. A REF value is returned for the object instance that is bound to the variable or row. For more information about REFs, see Oracle8 Concepts.
SELECT REF(e) FROM employee_t e WHERE e.empno = 10000;
This example uses REF in a predicate:
SELECT e.name FROM employee_t e INTO :x WHERE REF(e) = empref1;
Form XA Form X expression returns the row object.
In a SQL statement, VALUE takes as its argument a correlation variable (table alias) associated with a row of an object table.
SELECT VALUE(e) FROM employee e WHERE e.name = 'John Smith';