Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
11g Release 2 (11.2.2)

E21642-07
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Functions

Functions manipulate data and return a result. In addition to an alphabetical listing of all functions, this chapter contains an overview of functions including:

Numeric functions

Numeric functions accept numeric input and return numeric values. The numeric functions are:

Character functions returning character values

The character functions that return character values are:

Character functions returning number values

Character functions that return number values are:

String functions

TimesTen supports these string functions in SELECT statements:

A selected value that specifies a string function causes the SELECT result to be materialized. This causes overhead in both time and space.

LOB functions

The following EMPTY_* functions initialize LOBs to a non-null value:

The following TO_* functions convert specific data types into the desired LOB data type.

NLS character set functions

The NLS character set functions return information about the specified character set.

General comparison functions

The general comparison functions perform comparisons between input expressions. The general comparison functions are:

Null-related comparison functions

The null-related comparison functions compare expressions against NULL or return NULL based on comparison of expressions. The null-related comparison functions are:

Conversion functions

Conversion functions convert a value from one data type to another. Some of the conversion function names follow the convention of TO_datatype.

The SQL conversion functions are:

Datetime functions

For a full description of the datetime data types, see "Datetime data types".

The datetime functions are:

Aggregate functions

Aggregate functions perform a specific operation over all rows in a group. Aggregate functions return a single result row based on groups of rows, rather than on single rows. They are commonly used with the GROUP BY clause in a SELECT statement, where the returned rows are divided into groups. If you omit the GROUP BY clause, the aggregate functions in the select list are applied to all the rows in the queried table or view.

Aggregate functions can be specified in the select list or the HAVING clause. See "SELECT" for more information. The value of the expression is computed using each row that satisfies the WHERE clause.

Many aggregate functions that take a single argument can use the ALL or DISTINCT keywords. The default is ALL. See each aggregate function syntax to see if ALL or DISTINCT can be used.

For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average for these results is 1.5.

The ROLLUP and CUBE clauses within a GROUP BY clause produce superaggregate rows where the column values are represented by null values. Because the superaggregate rows are denoted by NULL, it can be a challenge to differentiate between query results that include a null value and the superaggregate result. In addition, within the returned subtotals, how do you find the exact level of aggregation for a given subtotal? Use the GROUP_ID, GROUPING and GROUPING_ID functions to resolve these issues.

See Chapter 1, "Data Types" for information about the following.

The following is a list of aggregate functions:

Analytic functions

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins, WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. The final ORDER BY clause is used to order the result of analytic functions. Analytic functions can appear in the select list of a query or subquery and in the ORDER BY clause.

Analytic functions allow you to divide query result sets into groups of rows called partitions. You can define partitions on columns or expressions. You can partition a query result set into just one partition holding all rows, a few large partitions or many small partitions holding just a few rows each.

You can define a sliding window for each row in the partition. This window determines the range of rows used to perform the calculations for the current row. Window sizes are based on a physical number of rows. The window has a starting row and an ending row and the window may move at one or both ends. For example, a window defined for a cumulative sum function would have its starting row fixed at the first row of the partition and the ending rows would slide from the start point to the last row of the partition. In contrast, a window defined for a moving average would have both the start point and end point slide.

You can set the window as large as all the rows in the partition or as small as one row within a partition.

You can specify multiple ordering expressions within each function. This is useful when using functions that rank values because the second expression can resolve ties between identical values for the first expression.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

Restrictions:

The list of analytic functions follows. Functions followed by an asterisk (*) support the WindowingClause.

SQL syntax

Analytic function syntax:

AnalyticFunctionName ([arguments]) OVER ([AnalyticClause])

AnalyticClause::= QueryPartitionClause [ORDER BY OrderByClause [,...]
                                          [WindowingClause]] |
                  ORDER BY OrderByClause [,...] [WindowingClause]

QueryPartitionClause::= PARTITION BY { Expression[,Expression]... |
                                      (Expression [,Expression]...) 
                                     }

OrderByClause::= Expression [ASC|DESC] [NULLS {FIRST|LAST}]

WindowingClause::= ROWS { BETWEEN StartPoint AND EndPoint |
                          StartPoint
                        }

StartPoint::= UNBOUNDED PRECEDING | CURRENT ROW | PosNumConstantExpr 
                                                    { PRECEDING | FOLLOWING }

EndPoint::= UNBOUNDED FOLLOWING | CURRENT ROW | PosNumConstantExpr
                                                   { PRECEDING | FOLLOWING }

Parameters

Parameter Description
AnalyticFunctionName Name of analytic function.
arguments Arguments for the analytic function. Number of arguments depends on the analytic function. Refer to the particular function for specific information on the arguments to the function.
OVER ( [ AnalyticClause ] ) Indicates that the function is an analytic function. This clause is computed after the FROM, WHERE, GROUP BY, and HAVING clauses.

If you do not specify the AnalyticClause, then the analytic function is evaluated over the entire result set without partitioning, ordering, or using a window.

QueryPartitionClause Optional clause used in AnalyticClause. Denoted by the PARTITION BY clause. If specified, the query result set is partitioned into groups based on the Expression list. If you omit this clause, then the function treats all rows of the query result set as a single group.

You can specify multiple analytic functions in the same query using either the same or different PARTITION keys.

Valid values for Expression are constants, columns, non-analytic functions or function expressions.

ORDER BY OrderByClause Optional clause used in AnalyticClause. Use this clause to specify how data is ordered within the partition. Expression cannot be a column alias or position.

You can order the values in a partition on multiple keys each defined by Expression and each qualified by an ordering sequence.

Analytic functions operate in the order specified in this clause. However this clause does not guarantee the order of the result. Use the ORDER BY clause of the query to guarantee the final result ordering.

If you specify the ORDER BY OrderByClause and you do not specify either a QueryPartitionClause or a WindowingClause, then the default window is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

If you do not specify the ORDER BY OrderByClause, then the order is indeterminate.

ASC | DESC Specifies the ordering sequence (ascending or descending). ASC is the default.

Clause is optional.

NULLS FIRST | NULLS LAST Specifies whether rows that contain NULL values are specified first or last in the ordering sequence. NULLS LAST is the default for ascending order. NULLS FIRST is the default for descending order.

Clause is optional.

WindowingClause Clause is denoted by the ROWS keyword. Specifies for each row a window expressed in physical units (rows). The window is used for calculating the function result. The function is applied to all the rows in the window. The window moves through the query result set or partition from top to bottom.

You cannot specify the WindowingClause if you have not specified the ORDER BY OrderByClause.

The value returned by the analytic function may produce nondeterministic results unless the ordering sequence results in unique ordering. In this case, specify multiple columns in the OrderByClause to achieve unique ordering.

For the list of functions that allow the WindowingClause, see "Analytic functions".

BETWEEN...AND Use the BETWEEN...AND clause to specify a start point (StartPoint) and end point (EndPoint) for the window.

If you omit the BETWEEN...AND clause and attempt to specify one end point, then TimesTen considers this end point the start point and the end point defaults to the current row.

StartPoint Valid values are UNBOUNDED PRECEDING, CURRENT ROW, PosNumConstantExpr PRECEDING or PosNumConstantExpr FOLLOWING.

PosNumConstantExpr must be either a constant positive numeric value or an expression that evaluates to a constant positive numeric value.

EndPoint Valid values are UNBOUNDED FOLLOWING, CURRENT ROW, PosNumConstantExpr PRECEDING or PosNumConstantExpr FOLLOWING.

PosNumConstantExpr must be either a constant positive numeric value or an expression that evaluates to a constant positive numeric value.

UNBOUNDED PRECEDING Use UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition.

Cannot be used as the end point.

UNBOUNDED FOLLOWING Use UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition.

Cannot be used as the start point.

CURRENT ROW As a start point, CURRENT ROW specifies that the window begins at the current row. In this case, the end point cannot be PosNumConstantExpr PRECEDING.

As an end point, CURRENT ROW specifies that the window ends at the current row. In this case, the start point cannot be PosNumConstantExpr FOLLOWING.

PosNumConstantExpr {PRECEDING | FOLLOWING } If PosNumConstantExpr FOLLOWING is the start point, then the end point must be PosNumConstantExpr FOLLOWING or UNBOUNDED FOLLOWING. If PosNumConstantExpr PRECEDING is the end point, then the start point must be PosNumConstantExpr PRECEDING or UNBOUNDED PRECEDING.

The end point PosNumConstantExpr must be greater or equal to the start point PosNumConstantExpr.

PosNumConstantExpr must be either a constant positive numeric value or an expression that evaluates to a constant positive numeric value.


Encoding function

Encoding functions return a value based on input expressions.

Note:

DECODE is included in "General comparison functions".

User and session functions

TimesTen supports these user and session functions:

Cache grid functions

You may want to execute a global query without changing the location of the data. You can use cache grid functions to determine the location of data in a cache grid and then execute a query for the information from that member.

Use these SQL functions in a global query to obtain information about the location of data in the cache grid, which the user can use to map each returned row to a member of the grid.

These functions can be used in a SELECT statement and in these clauses of a SELECT statement:

See "Obtaining information about the location of data in the cache grid" in Oracle In-Memory Database Cache User's Guide for more information.


ABS

The ABS function returns the absolute value of Expression.

SQL syntax

ABS(Expression)

Parameters

ABS has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type. Absolute value of Expression is returned.

Description

Examples

Create table abstest and define columns with type BINARY_FLOAT and TT_INTEGER. Insert values -BINARY_FLOAT_INFINITY and -10. Call ABS to return the absolute value. You see INF and 10 are the returned values:

Command> CREATE TABLE abstest (col1 BINARY_FLOAT, col2 TT_INTEGER);
Command> INSERT INTO abstest VALUES 
       > (-BINARY_FLOAT_INFINITY, -10);
1 row inserted.
Command> SELECT ABS (col1) FROM abstest;
< INF >
1 row found.
Command> SELECT ABS (col2) FROM abstest;
< 10 >
1 row found.

ADD_MONTHS

The ADD_MONTHS function returns the date resulting from date plus integer months.

SQL syntax

ADD_MONTHS(Date,Integer)

Parameters

ADD_MONTHS has the parameters:

Parameter Description
Date A datetime value or any value that can be converted to a datetime value.
Integer An integer or any value that can be converted to an integer.

Description

Examples

Call the ADD_MONTHS function to add 1 month to date January 31, 2007. The last day of February is returned.

Command> SELECT ADD_MONTHS (DATE '2007-01-31', 1) FROM dual;
< 2007-02-28 00:00:00 >
1 row found.

ADD_MONTHS returns data type DATE if date is of type TIMESTAMP:

Command> DESCRIBE SELECT ADD_MONTHS (TIMESTAMP '2007-01-31
       > 10:00:00', 1) FROM dual;
Prepared Statement:
  Columns:
    EXP                             DATE NOT NULL

Use the HR schema to select the first 5 rows of the employees table, showing employee_id, last_name and hire_date. Create new table temp_hire_date using the CREATE TABLE ... AS SELECT statement. Call ADD_MONTHS to add 23 months to the original hire_date.

Command> SELECT FIRST 5 employee_id, last_name, hire_date FROM employees;
< 100, King, 1987-06-17 00:00:00 >
< 101, Kochhar, 1989-09-21 00:00:00 >
< 102, De Haan, 1993-01-13 00:00:00 >
< 103, Hunold, 1990-01-03 00:00:00 >
< 104, Ernst, 1991-05-21 00:00:00 >
5 rows found.
Command> CREATE TABLE temp_hire_date (employee_id, last_name,
        > hire_date) AS SELECT FIRST 5 employee_id, last_name,
        > ADD_MONTHS (hire_date, 23) FROM employees;
5 rows inserted.
Command> SELECT * FROM temp_hire_date;
< 100, King, 1989-05-17 00:00:00 >
< 101, Kochhar, 1991-08-21 00:00:00 >
< 102, De Haan, 1994-12-13 00:00:00 >
< 103, Hunold, 1991-12-03 00:00:00 >
< 104, Ernst, 1993-04-21 00:00:00 >
5 rows found.

ASCIISTR

The ASCIISTR function takes as its argument, either a string or any expression that resolves to a string, in any character set, and returns the ASCII version of the string in the database character set. Non-ASCII characters are converted to Unicode escapes.

SQL syntax

ASCIISTR ([N]'String')

Parameters

ASCIISTR has the parameter:

Parameter Description
[N]'String' The string or expression that evaluates to a string that is passed to the ASCIISTR function. The string can be in any character set. Value can be of any supported character data types including CHAR, VARCHAR, VARCHAR2, NCHAR, NVARCHAR, NVARCHAR2, CLOB, or NCLOB data types. Both TimesTen and Oracle Database data types are supported. The ASCII version of the string in the database character set is returned. Specify N if you want to pass the string in UTF-16 format.

Description

The ASCIISTR function enables you to see the representation of a string value that is not in the database character set.

Examples

The following example invokes the ASCIISTR function passing as an argument the string 'Aäa' in UTF-16 format. The ASCII version is returned in the WE8ISO8859P1 character set. The non-ASCII character ä is converted to Unicode encoding value:

Command> connect "dsn=test; ConnectionCharacterSet= WE8ISO8859P1";
Connection successful: DSN=test;UID=user1;DataStore=/datastore/user1/test;
DatabaseCharacterSet=WE8ISO8859P1;
ConnectionCharacterSet=WE8ISO8859P1;PermSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> SELECT ASCIISTR (n'Aäa') FROM dual;
< A\00E4a >
1 row found.

AVG

Computes the arithmetic mean of the values in the argument. Null values are ignored.

SQL syntax

AVG ([ALL | DISTINCT] Expression) [OVER ([AnalyticClause])]

Parameters

AVG has the following parameters:

Parameter Description
Expression Can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
ALL Includes duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.
DISTINCT Eliminates duplicate column values from the argument of an aggregate function.
OVER ([AnalyticClause]) If specified, indicates aggregate analytic function. For more information on analytic functions, see "Analytic functions".

Description

Examples

Calculate the average salary for employees in the HR schema. Use CAST to cast the average as the data type of the column:

Command> SELECT CAST(AVG (salary) AS NUMBER (8,2)) FROM employees;
< 6461.68 >

CAST

Enables you to convert data of one type to another type. CAST can be used wherever a constant can be used. CAST is useful in specifying the exact data type for an argument. This is especially true for unary operators like '-' or functions with one operand like TO_CHAR or TO_DATE.

A value can only be CAST to a compatible data type, with the exception of NULL. NULL can be cast to any data type. CAST is not needed to convert a NULL literal to the desired target type.

The following conversions are supported:

SQL syntax

CAST
  ( {Expression | NULL} AS DataType )

Parameters

CAST has the parameters:

Parameter Description
Expression Specifies the value to be converted.
AS DataType Specifies the resulting data type.

Description

Examples

INSERT INTO t1 VALUES(TO_CHAR(CAST(? AS REAL)));
SELECT CONCAT(x1, CAST (? AS CHAR(10))) FROM t1;        
SELECT * FROM t1 WHERE CAST (? AS INT)=CAST(? AS INT);

CHR

The CHR function returns the character having the specified binary value in the database character set.

SQL syntax

CHR(n)

Parameters

CHR has the parameter:

Parameter Description
n The binary value in the database character set. The character having this binary value is returned. The result is of type VARCHAR2.

Description

Examples

The following example is run on an ASCII-based system with the WE8ISO8859P1 character set.

Command> SELECT CHR(67)||CHR(65)||CHR(84) FROM dual;
< CAT >
1 row found.

CEIL

The CEIL function returns the smallest integer greater than or equal to Expression.

SQL syntax

CEIL(Expression)

Parameters

CEIL has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type.

Description

Examples

Sum the commission_pct for employees in the employees table, and then call CEIL to return the smallest integer greater than or equal to the value returned by SUM. You see the value returned by the SUM function is 7.8 and the value returned by the CEIL function is 8.

Command> SELECT SUM (commission_pct) FROM employees;
< 7.8 >
1 row found.
Command> SELECT CEIL (SUM (commission_pct)) FROM employees;
< 8 >
1 row found.

COALESCE

The COALESCE function returns the first non-null expression in the expression list. If all occurrences of expression evaluate to NULL, then the function returns NULL.

SQL syntax

COALESCE(Expression1, Expression2 [,...])

Parameters

COALESCE has the parameters:

Parameter Description
Expression1, Expression2 [,...] The expressions in the expression list. The first non-null expression in the expression list is returned.

Each expression is evaluated in order and there must be at least 2 expressions.


Description

Examples

The example illustrates the use of the COALESCE expression. The COALESCE expression is used to return the commission_pct for the first 10 employees with manager_id = 100. If the commission_pct is NOT NULL, then the original value for commission_pct is returned. If commission_pct is NULL, then 0 is returned.

Command> SELECT FIRST 10 employee_id, COALESCE (commission_pct, 0) FROM
 employees WHERE manager_id = 100;
< 101, 0 >
< 102, 0 >
< 114, 0 >
< 120, 0 >
< 121, 0 >
< 122, 0 >
< 123, 0 >
< 124, 0 >
< 145, .4 >
< 146, .3 >
10 rows found.

CONCAT

The CONCAT function concatenates one character string with another to form a new character string.

SQL syntax

CONCAT(Expression1, Expression2)

Parameters

CONCAT has the parameters:

Parameter Description
Expression1 A CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB expression.
Expression2 A CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB expression.

Description

Examples

The following example concatenates first names and last names.

Command> SELECT CONCAT(CONCAT(first_name, ' '), last_name), salary 
 FROM employees;
< Steven King, 24000 >
< Neena Kochhar, 17000 >
< Lex De Haan, 17000 >
< Alexander Hunold, 9000 >
...
107 rows found.

The following example concatenates column id with column id2. In this example, the result type is NCHAR(40).

Command> CREATE TABLE cat (id CHAR (20), id2 NCHAR (20));
Command> INSERT INTO cat VALUES ('abc', 'def');
1 row inserted.
Command> SELECT CONCAT (id,id2) FROM cat;
< abc                 def                  >
1 row found.

The description of the || operator is in "Expression specification".


COUNT

Counts all rows that satisfy the WHERE clause, including rows containing null values. The data type of the result is TT_INTEGER.

COUNT is an aggregate function and can also be an aggregate analytic function. For more details on aggregate functions, see "Aggregate functions". For information on analytic functions, see "Analytic functions". For more information on the number of rows in a table, see the description for the NUMTUPS field in "SYS.TABLES" in Oracle TimesTen In-Memory Database System Tables and Views Reference.

SQL syntax

COUNT ({* | [ALL | DISTINCT]{Expression|ROWID}}) 
[OVER ([AnalyticClause])]

Parameters

COUNT has the parameters:

Parameter Description
Expression Can be any numeric data type or any nonnumeric type that can be implicitly converted to a numeric type.

Counts all rows. Rows containing null values are not counted. The data type of the result is TT_INTEGER. For more information on the number of rows in a table, see the description for the NUMTUPS field in "SYS.TABLES" in Oracle TimesTen In-Memory Database System Tables and Views Reference.

* Counts all rows that satisfy the WHERE clause, including duplicates and null values. COUNT never returns NULL.The data type of the result is TT_INTEGER. For more information on the number of rows in a table, see the description for the NUMTUPS field in "SYS.TABLES" in Oracle TimesTen In-Memory Database System Tables and Views Reference.
ALL Includes duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.
DISTINCT Eliminates duplicate column values from the argument of an aggregate function.
ROWID TimesTen assigns a unique ID called a rowid to each row stored in a table. The rowid value can be retrieved through the ROWID pseudocolumn. See "ROWID" for more details.
OVER ([AnalyticClause]) If specified, indicates aggregate analytic function. For more information on analytic functions, see "Analytic functions".

Description

Examples

Count the number of employees.

Command> SELECT COUNT(*) "TOTAL EMP" FROM employees;
 
TOTAL EMP
< 107 >
1 row found.

Count the number of managers by selecting out each individual manager ID without duplication.

Command> SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees;
 
MANAGERS
< 18 >
1 row found.

CURRENT_USER

Returns the name of the TimesTen user currently connected to the database.

SQL syntax

CURRENT_USER

Parameters

CURRENT_USER has no parameters.

Examples

To return the name of the user who is currently connected to the database:

SELECT CURRENT_USER FROM dual;

DECODE

The DECODE function compares an expression to each search value one by one. If the expression is equal to the search value, the result value is returned. If no match is found, then the default value (if specified) is returned. Otherwise NULL is returned.

SQL syntax

DECODE(Expression, {SearchValue, Result [,...])} [,Default])

Parameters

DECODE has the parameters:

Parameter Description
Expression The expression that is compared to the search value. Expression can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data types. Both TimesTen and Oracle Database data types are supported.
SearchValue An expression is compared to one or more search values.
Result If the expression is equal to a SearchValue, the specified Result value is returned.
Default If no match is found, the default value is returned. Default is optional. If Default is not specified and no match is found, then NULL is returned.

Description

If an expression is NULL, then the null expression equals a null search value.

Examples

The following example invokes the DECODE function. In the locations table, if the column country_id is equal to 'IT', the function returns 'Italy'. If the country_id is equal to 'JP', the function returns 'Japan'. If the country_id is equal to 'US', 'United States' is returned. If the country_id is not equal to 'IT' or 'JP' or 'US', the function returns 'Other'.

Command> SELECT location_id, 
> DECODE (country_id, 'IT', 'Italy', 
> 'JP', 'Japan', 'US', 'United States', 'Other') 
> FROM locations WHERE location_id < 2000;

LOCATION_ID, EXP
< 1000, Italy >
< 1100, Italy >
< 1200, Japan >
< 1300, Japan >
< 1400, United States >
< 1500, United States >
< 1600, United States >
< 1700, United States >
< 1800, Other >
< 1900, Other >
10 rows found.

DENSE_RANK

The DENSE_RANK function is an analytic function that computes the rank of rows in an ordered group of rows and returns the ranks as type NUMBER.

SQL syntax

DENSE_RANK () OVER ( [QueryPartitionClause] OrderByClause )

Parameters

DENSE_RANK has the parameters:

Parameter Description
QueryPartitionClause For information on syntax, semantics, and restrictions, see "Analytic functions".
OrderByClause For information on syntax, semantics, and restrictions, see "Analytic functions".

Description

Example

Select the department name, employee name, and salary of all employees who work in the human resources or purchasing department. Compute a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank.

Command> SELECT d.department_name, e.last_name, e.salary, DENSE_RANK()
       >  OVER (PARTITION BY e.department_id ORDER BY e.salary) AS dense
       > FROM employees e, departments d
       > WHERE e.department_id = d.department_id
       >    AND d.department_id IN ('30', '40')
       > ORDER BY e.last_name, e.salary, d.department_name, dense;
< Purchasing, Baida, 2900, 4 >
< Purchasing, Colmenares, 2500, 1 >
< Purchasing, Himuro, 2600, 2 >
< Purchasing, Khoo, 3100, 5 >
< Human Resources, Mavris, 6500, 1 >
< Purchasing, Raphaely, 11000, 6 >
< Purchasing, Tobias, 2800, 3 >
7 rows found.

EMPTY_BLOB

A BLOB column can be initialized to a zero-length, empty BLOB using the EMPTY_BLOB function. This function initializes the LOB to a non-null value, so can be used for initializing any BLOB that has been declared as NOT NULL.

SQL syntax

EMPTY_BLOB ()

Parameters

EMPTY_BLOB has no parameters.

Description

You can only use EMPTY_BLOB in the VALUES clause of an INSERT statement or the SET clause of an UPDATE statement.

Examples

The following example uses the EMPTY_BLOB function to initialize a non-null BLOB column to a zero-length value.

Command> CREATE TABLE blob_content (
 > id NUMBER PRIMARY KEY, 
 > blob_column BLOB NOT NULL); -- Does not allow a NULL value

Command> INSERT INTO blob_content (id, blob_column) 
 > VALUES (1, EMPTY_BLOB( ) );
1 row inserted.

EMPTY_CLOB

A CLOB or NCLOB column can be initialized to a zero-length, empty CLOB or NCLOB using the EMPTY_CLOB function. Both CLOB and NCLOB data types are initialized with the EMPTY_CLOB function. This function initializes the LOB to a non-null value, so can be used for initializing any CLOB or NCLOB that has been declared as NOT NULL.

SQL syntax

EMPTY_CLOB () 

Parameters

EMPTY_CLOB has no parameters.

Description

You can only use EMPTY_CLOB in the VALUES clause of an INSERT statement or the SET clause of an UPDATE statement.

Examples

The following example uses the EMPTY_CLOB function to initialize a non-null CLOB column to a zero-length value.

Command> CREATE TABLE clob_content ( 
 > id NUMBER PRIMARY KEY, 
 > clob_column CLOB NOT NULL ); -- This definition does not allow a NULL value

Command> INSERT INTO clob_content (id, clob_column)
       > VALUES (1, EMPTY_CLOB( ) );
1 row inserted.

EXTRACT

The EXTRACT function extracts and returns the value of a specified datetime field from a datetime or interval value expression as a NUMBER data type. This function can be useful for manipulating datetime field values in very large tables.

If you are using TimesTen type mode, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation for information about the EXTRACT function.

SQL syntax

EXTRACT (DateTimeField FROM IntervalExpression | DateTimeExpression)

Parameters

EXTRACT has the following parameters:

Parameter Description
DateTimeField The field to be extracted from IntervalExpression or DateTimeExpression. Accepted fields are YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.
IntervalExpression An interval result.
DateTimeExpression A datetime expression. For example, TIME, DATE, TIMESTAMP.

Description

Examples

The following example extracts the second field out of the interval result sysdate-t1.createtime.

SELECT EXTRACT(SECOND FROM sysdate-t1.createtime) FROM t1;

The following example extracts the second field out of sysdate from the dual system table.

Command> SELECT EXTRACT (SECOND FROM sysdate) FROM dual;
< 20 >
1 row found.

FIRST_VALUE

The FIRST_VALUE function is an analytic function that returns the first value in an ordered set of values.

SQL syntax

FIRST_VALUE (Expression [IGNORE NULLS]) OVER (AnalyticClause)

Parameters

FIRST_VALUE has the parameters:

Parameter Description
Expression For information on supported Expressions, see "Analytic functions".
IGNORE NULLS Specify IGNORE NULLS if you want FIRST_VALUE to return the first non-null value in the set or NULL if all values in the set are NULL. Clause is optional.
OVER (AnalyticClause) For information on syntax, semantics, and restrictions, see "Analytic functions".

Description

Example

Use the FIRST_VALUE function to select for each employee in department 90, the last name of the employee with the lowest salary.

Command> SELECT department_id, last_name, salary, FIRST_VALUE (last_name) OVER
       >   (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS lowest_sal
       > FROM
       >  (SELECT * FROM employees WHERE department_id = 90 ORDER BY employee_id)
       > ORDER BY department_id, last_name, salary, lowest_sal;
< 90, De Haan, 17000, Kochhar >
< 90, King, 24000, Kochhar >
< 90, Kochhar, 17000, Kochhar >
3 rows found.

FLOOR

The FLOOR function returns the largest integer equal to or less than Expression.

SQL syntax

FLOOR (Expression)

Parameters

FLOOR has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type.

Description

Examples

Sum the commission_pct for employees in the employees table. Then call FLOOR to return the largest integer equal to or less than the value returned by SUM. You see the value returned by the SUM function is 7.8 and the value returned by the FLOOR function is 7:

Command> SELECT SUM (commission_pct) FROM employees;
< 7.8 >
1 row found.
Command> SELECT FLOOR (SUM (commission_pct)) FROM employees;
< 7 >
1 row found.

GREATEST

The GREATEST function returns the greatest of the list of one or more expressions.

SQL syntax

GREATEST (Expression [,...])

Parameters

GREATEST has the parameter:

Parameter Description
Expression [,...] List of one or more expressions that is evaluated to determine the greatest expression value. Operand or column can be numeric, character or date. Each expression in the list must be from the same data type family.

Description

Use the GREATEST function to return the string with the greatest value:

Command> SELECT GREATEST ('GREAT', 'GREATER', 'GREATEST') FROM dual;
< GREATEST >
1 row found.

Use the GREATEST function to return the numeric expression with the greatest value. In this example, BINARY_DOUBLE is the data type with the highest numeric precedence, so arguments are implicitly converted to BINARY_DOUBLE before the comparison and the data type BINARY_DOUBLE is returned:

Command> SELECT GREATEST (10, 10.55, 10.1D) FROM dual;
< 10.5500000000000 >
1 row found.

Use the DESCRIBE command to confirm the data type returned is BINARY_DOUBLE:

Command> DESCRIBE SELECT GREATEST (10, 10.55, 10.1D) FROM dual;

Prepared Statement:
  Columns:
    EXP                             BINARY_DOUBLE NOT NULL

Use the GREATEST function to return the DATE expression with the greatest value. DATE and TIMESTAMP are in the same date family.

Command> SELECT GREATEST (DATE '2007-09-30',
       > TIMESTAMP '2007-09-30:10:00:00') FROM dual;
< 2007-09-30 10:00:00 >
1 row found.

Attempt to use the GREATEST function to return the greatest value in the list of TT_DATE and TT_TIMESTAMP expressions. You see an error because TT_DATE and TT_TIMESTAMP are in different date subfamilies and cannot be used in the same list of expressions.

Command> SELECT GREATEST (TT_DATE '2007-09-30', TT_TIMESTAMP
       > '2007-09-30:10:00:00') FROM dual;
2817: Invalid data type TT_TIMESTAMP for argument 2 for function GREATEST
The command failed.

Use the GREATEST function to return the TT_DATE expression with the greatest value.

Command> SELECT GREATEST (TT_DATE '2007-09-30',
       > TT_DATE '2007-09-29', TT_DATE '2007-09-28') FROM dual;
< 2007-09-30 >
1 row found.

GROUP_ID

The GROUP_ID function identifies duplicate groups in a SELECT query resulting from a GROUP BY clause. This function returns the number 0 for a unique group; any subsequent duplicate grouping row receives a higher number, starting with 1. The GROUP_ID function filters out duplicate groupings from the query result. If you have complicated queries that may generate duplicate values, you can eliminate those rows by including the HAVING GROUP_ID() = 0 condition.

Note:

For full details on the GROUP BY clause, see "GROUP BY clause". For details on the HAVING clause, see "SELECT".

Syntax

The GROUP_ID function is applicable only in a SELECT statement that contains a GROUP BY clause; it can be used in the select list and HAVING clause of the SELECT query.

GROUP_ID()

Parameters

GROUP_ID has no parameters.

Example

The following example shows how GROUP_ID returns 0 for a unique group and a number > 0 to identify duplicate groups. The following example prints out the department number, manager id and the sum of the salary within the manager. The resulting output is grouped using the ROLLUP clause on the manager and department providing superaggregate results.

Command> SELECT department_id as DEPT, manager_id AS MGR, 
 GROUP_ID(), SUM(salary) as SALARY
 FROM employees 
 WHERE manager_id > 146 
 GROUP BY manager_id, ROLLUP(manager_id, department_id) 
 ORDER BY manager_id, department_id;
 
DEPT, MGR, EXP, SALARY
< 80, 147, 0, 46600 >
< <NULL>, 147, 1, 46600 >
< <NULL>, 147, 0, 46600 >
< 80, 148, 0, 51900 >
< <NULL>, 148, 0, 51900 >
< <NULL>, 148, 1, 51900 >
< 80, 149, 0, 43000 >
< <NULL>, 149, 0, 7000 >
< <NULL>, 149, 0, 50000 >
< <NULL>, 149, 1, 50000 >
< 20, 201, 0, 6000 >
< <NULL>, 201, 0, 6000 >
< <NULL>, 201, 1, 6000 >
< 110, 205, 0, 8300 >
< <NULL>, 205, 0, 8300 >
< <NULL>, 205, 1, 8300 >
16 rows found.

GROUPING

The GROUPING function enables you to determine whether a NULL is a stored NULL or an indication of a subtotal or grand total. Using a single column as its argument, GROUPING returns a 1 when it encounters a null value created by a ROLLUP or CUBE operation, indicating a subtotal or grand total. Any other type of value, including a stored NULL, returns a 0.

Note:

For full details on ROLLUP and CUBE clauses, see "GROUP BY clause".

Syntax

The GROUPING function is applicable only in a SELECT statement that contains a GROUP BY clause. It can be used in the select list and HAVING clause of the SELECT query that includes the GROUP BY clause. The expression indicated in the GROUPING function syntax must match one of the expressions contained in the GROUP BY clause.

The following syntax uses GROUPING to identify the results from the expression listed as an aggregate or not:

SELECT …  [GROUPING(Expression)…]  … 
  GROUP BY … { RollupCubeClause | GroupingSetsClause } ...

The following syntax uses GROUPING within a HAVING clause to identify the results from the expression listed as an aggregate or not:

SELECT …
  GROUP BY … { RollupCubeClause | GroupingSetsClause } ...
 HAVING GROUPING(Expression) = 1

Parameters

Parameter Description
Expression Valid expression syntax. See Chapter 3, "Expressions".
RollupCubeClause The GROUP BY clause may include one or more ROLLUP or CUBE clauses. See "GROUP BY clause" for full details.
GroupingSetsClause The GROUP BY clause may include one or more GROUPING SETS clauses. The GROUPING SETS clause enables you to explicitly specify which groupings of data that the database returns. For more information, see "GROUPING SETS".

Examples

The following example shows how the grouping function returns a '1' when it encounters the grand total for the department.

Command> SELECT department_id AS DEPT, 
GROUPING(department_id) AS DEPT_GRP, SUM(salary) AS SALARY 
FROM emp_details_view 
GROUP BY ROLLUP(department_id) 
ORDER BY department_id; 
DEPT, DEPT_GRP, SALARY
< 10, 0, 4400 >
< 20, 0, 19000 >
< 30, 0, 24900 >
< 40, 0, 6500 >
< 50, 0, 156400 >
< 60, 0, 28800 >
< 70, 0, 10000 >
< 80, 0, 304500 >
< 90, 0, 58000 >
< 100, 0, 51600 >
< 110, 0, 20300 >
< <NULL>, 1, 684400 >
12 rows found.

The following example shows that you can use the GROUPING function for each column to determine which null values are for the totals.

Command> SELECT department_id AS DEPT, job_id AS JOB, 
GROUPING(department_id) AS DEPT_GRP, GROUPING(job_id) AS JOB_GRP, 
GROUPING_ID(department_id, job_id) AS GRP_ID, SUM(salary) AS SALARY 
FROM emp_details_view 
GROUP BY CUBE(department_id, job_id) 
ORDER BY department_id, job_id, grp_id ASC;
 
DEPT, JOB, DEPT_GRP, JOB_GRP, GRP_ID, SALARY
< 10, AD_ASST, 0, 0, 0, 4400 >
< 10, <NULL>, 0, 1, 1, 4400 >
< 20, MK_MAN, 0, 0, 0, 13000 >
< 20, MK_REP, 0, 0, 0, 6000 >
< 20, <NULL>, 0, 1, 1, 19000 >
< 30, PU_CLERK, 0, 0, 0, 13900 >
< 30, PU_MAN, 0, 0, 0, 11000 >
< 30, <NULL>, 0, 1, 1, 24900 >
...
< 110, AC_ACCOUNT, 0, 0, 0, 8300 >
< 110, AC_MGR, 0, 0, 0, 12000 >
< 110, <NULL>, 0, 1, 1, 20300 >
< <NULL>, AC_ACCOUNT, 1, 0, 2, 8300 >
< <NULL>, AC_MGR, 1, 0, 2, 12000 >
< <NULL>, AD_ASST, 1, 0, 2, 4400 >
< <NULL>, AD_PRES, 1, 0, 2, 24000 >
< <NULL>, AD_VP, 1, 0, 2, 34000 >
< <NULL>, FI_ACCOUNT, 1, 0, 2, 39600 >
< <NULL>, FI_MGR, 1, 0, 2, 12000 >
< <NULL>, HR_REP, 1, 0, 2, 6500 >
< <NULL>, IT_PROG, 1, 0, 2, 28800 >
< <NULL>, MK_MAN, 1, 0, 2, 13000 >
< <NULL>, MK_REP, 1, 0, 2, 6000 >
< <NULL>, PR_REP, 1, 0, 2, 10000 >
< <NULL>, PU_CLERK, 1, 0, 2, 13900 >
< <NULL>, PU_MAN, 1, 0, 2, 11000 >
< <NULL>, SA_MAN, 1, 0, 2, 61000 >
< <NULL>, SA_REP, 1, 0, 2, 243500 >
< <NULL>, SH_CLERK, 1, 0, 2, 64300 >
< <NULL>, ST_CLERK, 1, 0, 2, 55700 >
< <NULL>, ST_MAN, 1, 0, 2, 36400 >
< <NULL>, <NULL>, 1, 1, 3, 684400 >
50 rows found.

GROUPING_ID

The GROUPING_ID function returns a number that shows the exact GROUP BY level of aggregation resulting from a ROLLUP or CUBE clause.

Note:

For details on ROLLUP and CUBE clauses, see "GROUP BY clause".

The GROUPING_ID function takes the ordered list of grouping columns from the ROLLUP or CUBE as input and computes the grouping ID as follows:

  1. Applies the GROUPING function to each of the individual columns in the list. The result is a set of ones and zeros, where 1 represents a superaggregate generated by the ROLLUP or CUBE.

  2. Puts these ones and zeros in the same order as the order of the columns in its argument list to produce a bit vector.

  3. Converts this bit vector from a binary number into a decimal (base 10) number, which is returned as the grouping ID.

For instance, if you group with CUBE(department_id, job_id), the returned values are as shown in Table 4-1.

Table 4-1 GROUPING_ID Example for CUBE(department_id, job_id)

Aggregation Level Bit Vector GROUPING_ID

Normal grouping rows for department and job

0 0

0

Subtotal for department_id, aggregated at job_id

0 1

1

Subtotal for job_id, aggregated at department_id

1 0

2

Grand total

1 1

3


The GROUPING_ID function can be used in a query to filter rows so that only the summary rows are displayed. You can use the GROUPING_ID function in the HAVING clause to restrict output to only those rows that contain totals and subtotals. This can be accomplished when adding a comparison of the GROUPING_ID function results as greater than zero in the HAVING clause.

Syntax

The GROUPING_ID function is applicable only in a SELECT statement that contains the GROUP BY clause, a GROUPING function, and one of the following clauses: ROLLUP, CUBE or GROUPING SETS clauses. It can be used in the select list and HAVING clause of the SELECT query.

GROUPING_ID(Expression [, Expression ]...)

Parameters

Parameter Description
Expression Valid expression syntax. See Chapter 3, "Expressions".

Examples

The following example has the HAVING clause filter on the GROUPING_ID function, where the returned value is greater than zero. This excludes rows that do not contain either a subtotal or grand total. The following example shows the subtotals for the departments are identified with a group ID of 1, subtotals for the job ID with a group ID of 2 and the grand total with a group ID of 3:

Command> SELECT department_id AS DEPT, job_id AS JOB, 
GROUPING_ID(department_id, job_id) AS GRP_ID, 
SUM(salary) AS SALARY 
FROM emp_details_view 
GROUP BY CUBE(department_id, job_id) 
HAVING GROUPING_ID(department_id, job_id) > 0 
ORDER BY department_id, job_id, grp_id ASC;
 
DEPT, JOB, GRP_ID, SALARY
< 10, <NULL>, 1, 4400 >
< 20, <NULL>, 1, 19000 >
< 30, <NULL>, 1, 24900 >
< 40, <NULL>, 1, 6500 >
< 50, <NULL>, 1, 156400 >
< 60, <NULL>, 1, 28800 >
< 70, <NULL>, 1, 10000 >
< 80, <NULL>, 1, 304500 >
< 90, <NULL>, 1, 58000 >
< 100, <NULL>, 1, 51600 >
< 110, <NULL>, 1, 20300 >
< <NULL>, AC_ACCOUNT, 2, 8300 >
< <NULL>, AC_MGR, 2, 12000 >
< <NULL>, AD_ASST, 2, 4400 >
< <NULL>, AD_PRES, 2, 24000 >
< <NULL>, AD_VP, 2, 34000 >
< <NULL>, FI_ACCOUNT, 2, 39600 >
< <NULL>, FI_MGR, 2, 12000 >
< <NULL>, HR_REP, 2, 6500 >
< <NULL>, IT_PROG, 2, 28800 >
< <NULL>, MK_MAN, 2, 13000 >
< <NULL>, MK_REP, 2, 6000 >
< <NULL>, PR_REP, 2, 10000 >
< <NULL>, PU_CLERK, 2, 13900 >
< <NULL>, PU_MAN, 2, 11000 >
< <NULL>, SA_MAN, 2, 61000 >
< <NULL>, SA_REP, 2, 243500 >
< <NULL>, SH_CLERK, 2, 64300 >
< <NULL>, ST_CLERK, 2, 55700 >
< <NULL>, ST_MAN, 2, 36400 >
< <NULL>, <NULL>, 3, 684400 >
31 rows found.

INSTR, INSTRB, INSTR4

Determines the first position, if any, at which one string occurs within another. If the substring does not occur in the string, 0 is returned. The position returned is always relative to the beginning of SourceExpr. INSTR returns type NUMBER.

If you are using TimesTen type mode, for information on the INSTR function, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

{INSTR | INSTRB | INSTR4} ( SourceExpr, SearchExpr [,m[,n]])

Parameters

INSTR has the parameters:

Parameter Description
SourceExpr The string to be searched to find the position of SearchExpr. Value can be any supported character data types including CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB data types. Both TimesTen and Oracle Database data types are supported.
SearchExpr The substring to be found in string SourceExpr. If SearchExpr does not occur in SourceExpr, zero is returned. If either string is of length zero, NULL is returned.
m The optional position at which to begin the search. If m is specified as zero, the result is zero. If m is positive, the search begins at the CharExpr2+m. If m is negative, the search begins m characters from the end of CharExpr2.
n If n is specified it must be a positive value and the search returns the position of the nth occurrence of CharExpr1

Description

INSTR calculates strings using characters as defined by character set. INSTRB uses bytes instead of characters. INSTR4 uses UCS4 code points.

Examples

The following example uses INSTR to determine the position at which the substring 'ing' occurs in the string 'Washington':

Command> SELECT INSTR ('Washington', 'ing') FROM dual;
< 5 >
1 row found.

The following example uses INSTR to provide the number of employees with a '650' area code as input to the COUNT function:

Command> SELECT COUNT(INSTR(phone_number, '650')) FROM employees;
< 107 >
1 row found.

LAST_VALUE

The LAST_VALUE function is an analytic function that returns the last value in an ordered set of values.

SQL syntax

LAST_VALUE (Expression [IGNORE NULLS]) OVER (AnalyticClause)

Parameters

LAST_VALUE has the parameters:

Parameter Description
Expression For information on supported Expressions, see "Analytic functions".
IGNORE NULLS Specify IGNORE NULLS if you want LAST_VALUE to return the last non-NULL value in the set or NULL if all values in the set are NULL. Clause is optional.
OVER (AnalyticClause) For information on syntax, semantics, and restrictions, see "Analytic functions".

Description

Example

Use the LAST_VALUE function to return for each row the hire date of the employee with the highest salary.

Command> SELECT last_name, salary, hire_date,
       >  LAST_VALUE (hire_date) OVER (ORDER BY salary
       >    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
       > FROM
       > (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date)
       > ORDER BY last_name, salary, hire_date, lv;
< De Haan, 17000, 1993-01-13 00:00:00, 1987-06-17 00:00:00 >
< King, 24000, 1987-06-17 00:00:00, 1987-06-17 00:00:00 >
< Kochhar, 17000, 1989-09-21 00:00:00, 1987-06-17 00:00:00 >
3 rows found.

LEAST

The LEAST function returns the smallest of the list of one or more expressions.

SQL syntax

LEAST (Expression [,...])

Parameters

LEAST has the parameter:

Parameter Description
Expression [,...] List of one or more expressions that is evaluated to determine the smallest expression value. Operand or column can be numeric, character, or date. Each expression in the list must be from the same data type family.

Description

Use the LEAST function to return the string with the smallest value:

Command> SELECT LEAST ('SMALL','SMALLER','SMALLEST') FROM dual;
< SMALL >
1 row found.

Use the LEAST function to return the numeric expression with the smallest value. In this example, NUMBER is the data type with the highest numeric precedence, so arguments are implicitly converted to NUMBER before the comparison and the data type NUMBER is returned. First describe the table leastex to see the data types defined for columns col1 and col2. Then SELECT * from leastex to see the data. Then invoke the LEAST function.

Command> DESCRIBE leastex;

Table SAMPLEUSER.LEASTEX:
  Columns:
    COL1                            NUMBER (2,1)
    COL2                            TT_BIGINT

1 table found.
(primary key columns are indicated with *)
Command> SELECT * FROM leastex;
< 1.1, 1 >
1 row found.
Command> SELECT LEAST (Col2,Col1) from leastex;
< 1 >
1 row found.

Use the DESCRIBE command to confirm that the data type returned is NUMBER:

Command> DESCRIBE SELECT LEAST (Col2,Col1) FROM leastex;

Prepared Statement:
  Columns:
    EXP                             NUMBER

Use the LEAST function to return the DATE expression with the smallest value. DATE and TIMESTAMP are in the same date family.

Command> SELECT LEAST (DATE '2007-09-17', 
       > TIMESTAMP '2007-09-17:10:00:00') FROM dual;
< 2007-09-17 00:00:00 >
1 row found.

Attempt to use the LEAST function to return the smallest value in the list of TT_DATE and TT_TIMESTAMP expressions. You see an error because TT_DATE and TT_TIMESTAMP are in different date subfamilies and cannot be used in the same list of expressions.

Command> SELECT LEAST (TT_DATE '2007-09-17',
       > TT_TIMESTAMP '2007-09-17:01:00:00') FROM dual;
2817: Invalid data type TT_TIMESTAMP for argument 2 for function LEAST
The command failed.

Use the LEAST function to return the TIME expression with the smallest value.

Command> SELECT LEAST (TIME '13:59:59', TIME '13:59:58',
       > TIME '14:00:00') FROM dual;
< 13:59:58 >
1 row found.

LENGTH, LENGTHB, LENGTH4

Returns the length of a given character string in an expression. LENGTH returns type NUMBER.

If you are using TimesTen type mode, for information on the LENGTH function, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

{LENGTH|LENGTHB|LENGTH4} (CharExpr) 

Parameters

LENGTH has the parameter:

Parameter Description
CharExpr The string for which to return the length. Supported data types for CharExpr for are CHAR, VARCHAR2, NCHAR, or NVARCHAR2. LENGTH and LENGTHB also support CLOB, NCLOB, and BLOB data types.

Description

The LENGTH functions return the length of CharExpr. LENGTH calculates the length using characters as defined by the character set. LENGTHB uses bytes rather than characters. LENGTH4 uses UCS4 code points.

Examples

Determine the length of the string 'William':

Command> SELECT LENGTH('William') FROM dual;
< 7 >
1 row found.

The following determines the length of the NCLOB data:

Command> SELECT nclob_column FROM nclob_content;
< Demonstration of the NCLOB data type >
1 row found.

Command> SELECT LENGTH(nclob_column) FROM nclob_content;
< 36 >
1 row found.

LOWER and UPPER

The LOWER function converts expressions of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, or NCLOB to lowercase. The UPPER function converts expressions of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, or NCLOB to uppercase. Character semantics are supported for CHAR and VARCHAR2 types. The data type of the result is the same as the data type of the expression.

SQL syntax

{UPPER | LOWER} (Expression1)

Parameters

LOWER and UPPER have the following parameter:

Parameter Description
Expression1 An expression which is converted to lowercase (using LOWER) or uppercase (using UPPER).

Description

LOWER(?) and UPPER(?) are not supported, but you can combine it with the CAST operator. For example:

LOWER(CAST(? AS CHAR(30))) 

Command> SELECT LOWER (last_name) FROM employees WHERE employee_id = 100;
< king >
1 row found.

LPAD

The LPAD function returns Expression1, left-padded to length n characters with the sequence of characters in Expression2. This function is useful for formatting the output of a query.

SQL syntax

LPAD (Expression1, n [,Expression2])

Parameters

LPAD has the parameters:

Parameter Description
Expression1 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column to be left-padded. If Expression1 is longer than n, then LPAD returns the portion of Expression1 that fits in n.
n Length of characters returned by the LPAD function. Must be a NUMBER integer or a value that can be implicitly converted to a NUMBER integer.
Expression2 Sequence of characters to be left-padded in Expression1. If you do not specify Expression2, the default is a single blank. Operand or column can be of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

Description

Examples

The following prints out the last names of the first 5 employees, left-padded with periods out to 20 characters.

Command> SELECT FIRST 5 LPAD (last_name, 20, '.') 
 > FROM employees
 > ORDER BY last_name;
< ................Abel >
< ................Ande >
< ............Atkinson >
< ..............Austin >
< ................Baer >
5 rows found.

Use LPAD function to left-pad the string 'LPAD Function' with string 'DEMO-ONLY' plus 2 spaces. The DEMO-ONLY string is replicated as much as it can as defined by the total characters output by the function, which is replicated three times.

Command> SELECT LPAD ('LPAD Function', 46, 'DEMO-ONLY  ') FROM dual;
< DEMO-ONLY  DEMO-ONLY  DEMO-ONLY  LPAD Function >
1 row found.

Call LPAD function with length of -1. NULL is returned.

Command> SELECT LPAD ('abc', -1, 'a')  FROM dual;
< <NULL> >
1 row found.

LTRIM

The LTRIM function removes from the left end of Expression1 all of the characters contained in Expression2. TimesTen begins scanning Expression1 from its first character and removes all characters that appear in Expression2 until reaching a character not in Expression2 and returns the result.

SQL syntax

LTRIM (Expression1 [,Expression2])

Parameters

LTRIM has the parameters:

Parameter Description
Expression1 The CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column to be trimmed. If Expression1 is a character literal, then enclose it in single quotes.
Expression2 Optional expression used for trimming Expression1. If Expression2 is a character literal, enclose it in single quotes. If you do not specify Expression2, it defaults to a single blank. Operand or column can be of type CHAR,VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

Description

Examples

Call the LTRIM function to remove left-most 'x' and 'y' from string. LTRIM removes individual occurrences of 'x' and 'y', not pattern 'xy'.

Command> SELECT LTRIM ('xxxyyyxyxyLTRIM Example', 'xy') FROM dual;
< LTRIM Example >
1 row found.

Call the LTRIM function to remove YYYY-MM-DD from SYSDATE. Call TO_CHAR to convert SYSDATE to VARCHAR2.

Command> SELECT LTRIM (TO_CHAR(SYSDATE), '2007-08-21') FROM dual;
<  22:54:39 >
1 row found.

Call LTRIM to remove all characters from Expression1. In the first example, the data type is CHAR, so NULL is returned. In the second example, the data type is TT_CHAR, so the empty string is returned.

Command> CREATE TABLE ltrimtest (col1 CHAR (4), col2 TT_CHAR (4));
Command> INSERT INTO ltrimtest VALUES ('ABBB','ABBB');
1 row inserted.
Command> SELECT LTRIM (col1, 'AB') FROM ltrimtest;
< <NULL> >
1 row found.
Command> SELECT LTRIM (col2, 'AB') FROM ltrimtest;
<  >
1 row found.

MAX

Finds the largest of the values in the argument (ASCII comparison for alphabetic types). Null values are ignored. MAX can be applied to numeric, character, and BINARY data types. MAX is an aggregate function and can also be an aggregate analytic function. For more details on aggregate functions, see "Aggregate functions". For more information on analytic functions, see "Analytic functions".

SQL syntax

MAX ([ALL | DISTINCT]{Expression | ROWID}) [OVER ([AnalyticClause])]

Parameters

MAX has the parameters:

Parameter Description
Expression Can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
ALL Includes any duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.
DISTINCT Eliminates duplicate column values from the argument of an aggregate function.
ROWID TimesTen assigns a unique ID called a rowid to each row stored in a table. The rowid value can be retrieved through the ROWID pseudocolumn. See "ROWID" for more details.
OVER ([AnalyticClause]) If specified, indicates aggregate analytic function. For more information on analytic functions, see "Analytic functions".

Description

Examples

Find the largest salary:

Command> SELECT MAX(salary) "Max Salary" FROM employees;
 
MAX SALARY
< 24000 >
1 row found.

MIN

Finds the smallest of the values in the argument (ASCII comparison for alphabetic types). Null values are ignored. MIN can be applied to numeric, character, and BINARY data types. For more details on aggregate functions, see "Aggregate functions". MIN can also be an aggregate analytic function. For information on analytic functions, see "Analytic functions".

SQL syntax

 MIN ([ALL | DISTINCT]{Expression|ROWID}) [OVER (AnalyticClause)]

Parameters

MIN has the parameters:

Parameter Description
Expression Can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
ALL Includes any duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.
DISTINCT Eliminates duplicate column values from the argument of an aggregate function.
ROWID TimesTen assigns a unique ID called a rowid to each row stored in a table. The rowid value can be retrieved through the ROWID pseudocolumn. See "ROWID" for more details.
OVER (AnalyticClause) If specified, indicates aggregate analytic function. For more information on analytic functions, see "Analytic functions".

Description

Examples

Show the smallest salary:

Command> SELECT MIN(salary) "Min Salary" FROM employees;
 
MIN SALARY
< 2100 >

Show the earliest hire date:

1 row found.
Command> SELECT MIN(hire_date) "Earliest Hire Date" FROM employees;
 
EARLIEST HIRE DATE
< 1987-06-17 00:00:00 >
1 row found.

MOD

Returns the remainder of an INTEGER expression divided by a second INTEGER expression.

SQL syntax

MOD(Expression1, Expression2)

Parameters

MOD has the following parameters:

Parameter Description
Expression1 An INTEGER expression.
Expression2 An INTEGER expression.

Description

The following example tests whether the value of the expression m is divisible by the value of expression n.

SELECT m, n FROM test WHERE MOD(m, n) = 0;

MONTHS_BETWEEN

The MONTHS_BETWEEN function returns number of months between dates date1 and date2.

SQL syntax

MONTHS_BETWEEN(date1, date2)

Parameters

MONTHS_BETWEEN has the parameters:

Parameter Description
date1 A datetime value or any value that can be converted to a datetime value.
date2 A datetime value or any value that can be converted to a datetime value.

Description

Input parameters can be any combination of all supported datetime data types, excluding the TIME or TT_TIME data types. The supported datetime data types include DATE, TIMESTAMP, TT_DATE, TT_TIMESTAMP, ORA_DATE, and ORA_TIMESTAMP. For details on all datetime data types, see Chapter 1, "Data Types".

The return data type is a NUMBER.

MONTHS_BETWEEN returns number of months between dates date1 and date2.

Examples

The following examples calculate months between two given dates.

Command> SELECT MONTHS_BETWEEN(DATE '1995-02-02', DATE '1995-01-01') 
AS Months FROM dual;
 
MONTHS
< 1.03225806451613 >
1 row found. 

Command> SELECT MONTHS_BETWEEN(DATE '2010-02-02', DATE '2010-10-01') "Months"
 FROM dual;
 
MONTHS
< -7.96774193548387 >
1 row found.
 

The following command uses CAST to explicitly convert CHAR strings into timestamps. The first result is rounded to an integer.

Command> SELECT ROUND ( MONTHS_BETWEEN (CAST ('2010-04-15 14:13:52' 
AS TIMESTAMP), CAST ('2000-12-31 00:00:00' AS TIMESTAMP))), 
MONTHS_BETWEEN (CAST ('2010-04-15 14:13:52' AS TIMESTAMP), 
CAST ('2000-12-31 00:00:00' AS TIMESTAMP)) 
FROM dual;

< 112, 111.502998805257 >
1 row found.

NCHR

The NCHR function returns the character having the specified Unicode value.

SQL syntax

NCHR(n)

Parameters

NCHR has the parameter:

Parameter Description
n The specified Unicode value. The character having this Unicode value is returned. The result is of type NVARCHAR2.

Example

The following example returns the NCHAR character 187:

Command> SELECT NCHR(187) FROM dual;
< > >
1 row found.

NLS_CHARSET_ID

NLS_CHARSET_ID returns the character set ID number corresponding to the character set name.

Note:

For a complete list of supported character sets, see "Supported character sets" in the Oracle TimesTen In-Memory Database Reference.

SQL syntax

NLS_CHARSET_ID(String)

Parameters

NLS_CHARSET_ID has the parameter:

Parameter Description
String The input string argument is a run-time VARCHAR2 value that represents the character set. This string is case-insensitive.

If the input string corresponds to a legal TimesTen character set, the associated character set ID number is returned; otherwise, NULL is returned.

Providing CHAR_CS returns the database character set ID number. Providing NCHAR_CS returns the national character set ID number. Other input string values are interpreted as Oracle Database NLS character set names, such as AL32UTF8.


Examples

The following example returns the character set ID number of character set JA16EUC:

SELECT NLS_CHARSET_ID('JA16EUC') FROM dual; 
< 830 >
1 row found.

NLS_CHARSET_NAME

NLS_CHARSET_NAME returns the name of the character set corresponding to the character set ID number.

Note:

For a complete list of supported character sets, see "Supported character sets" in the Oracle TimesTen In-Memory Database Reference.

SQL syntax

NLS_CHARSET_NAME(Number)

Parameters

NLS_CHARSET_NAME has the parameter:

Parameter Description
Number The number represents a character set ID. If the number does not correspond to a legal TimesTen character set ID, NULL is returned.

Description

The character set name is returned as a VARCHAR2 value in the database character set.

Examples

The following example returns the character set name corresponding to character set ID number 2:

SELECT NLS_CHARSET_NAME(2) FROM dual;
< WE8DEC >
1 row found.

The following example determines the name of the database character set by providing CHAR_CS as the character set name within the NLS_CHARSET_ID function, whose results are provided to the NLS_CHARSET_NAME function:

SELECT NLS_CHARSET_NAME(NLS_CHARSET_ID('CHAR_CS')) FROM dual;
< US7ASCII >
1 row found.

NLSSORT

Returns the sort key value for the given string.

SQL syntax

NLSSORT (String [,'NLS_SORT = SortName'])

Parameters

NLSSORT has the following parameters:

Parameter Description
String Given the String, NLSSORT returns the sort key value used to sort the String. Supported data types for String are CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, and NCLOB.
['NLS_SORT = SortName'] SortName is either the linguistic sort sequence or BINARY. If you omit this parameter, then the default sort sequence for the session is used. Append to the SortName the suffix -ai for accent-insensitive sorting or -ci for case-insensitive sorting. For more information on acceptable linguistic SortName values, see "Supported linguistic sorts" in Oracle TimesTen In-Memory Database Reference.

Description

Examples

The following example illustrates sorting and comparison operations based on a linguistic sort sequence rather than on the binary value of the string. In addition, the example shows the same results can be obtained by using the ALTER SESSION... SET NLS_SORT statement.

Command> CREATE TABLE nsortdemo (name VARCHAR2 (15));
Command> INSERT INTO nsortdemo VALUES ('Gaardiner');
1 row inserted.
Command> INSERT INTO nsortdemo VALUES ('Gaberd');
1 row inserted.
Command> INSERT INTO nsortdemo VALUES ('Gaasten');
1 row inserted.
Command> # Perform Sort
Command> SELECT * FROM nsortdemo ORDER BY name;
< Gaardiner >
< Gaasten >
< Gaberd >
3 rows found.
Command> #Use function to perform sort
Command> SELECT * FROM nsortdemo ORDER BY NLSSORT (name, 'NLS_SORT = XDanish');
< Gaberd >
< Gaardiner >
< Gaasten >
3 rows found.
Command># comparison operation
Command> SELECT * FROM nsortdemo where Name > 'Gaberd';
< Gardiner >
1 row found.
Command> #Use function in comparison operation
Command> SELECT * FROM nsortdemo WHERE NLSSORT (name, 'NLS_SORT = XDanish') >
> NLSSORT ('Gaberd', 'NLS_SORT = XDanish');
< Gaardiner >
< Gaasten >
2 rows found.
Command> #Use ALTER SESSION to obtain the same results
Command> ALTER SESSION SET NLS_SORT = 'XDanish';
Session altered.
Command> SELECT * FROM nsortdemo ORDER BY name;
< Gaberd >
< Gaardiner >
< Gaasten >
3 rows found.
Command> SELECT * FROM nsortdemo WHERE name > 'Gaberd';
< Gaardiner >
< Gaasten >
2 rows found.

The following example creates a linguistic index:

Command> CREATE INDEX danishindex 
       > ON nsortdemo (NLSSORT (name, 'NLS_SORT =XDanish'));
Command> INDEXES N%;
Indexes on table USER1.NSORTDEMO:
  DANISHINDEX: non-unique range index on columns:
    NLSSORT(NAME,'NLS_SORT = XDanish')
  1 index found.
1 index found on 1 table.

NULLIF

NULLIF compares two expressions. If the values are equal, NULLIF returns a NULL; otherwise, the function returns the first expression.

SQL syntax

NULLIF(Expression1, Expression2)

Parameters

NULLIF has the following parameters:

Parameter Description
Expression1 The expression which is tested to see if equal to Expression2. You cannot specify the literal NULL for Expression1.
Expression2 The expression which is tested to see if equal to Expression1.

Description

Note:

See "CASE expressions" for more details.

Examples

The following example selects employees who have changed jobs since they were hired, which is indicated by a different job_id in the job_history table from the current job_id in the employees table. Thus, when you apply NULLIF to the old and new job_id entries, those that are the same returns a NULL; those that are different indicate those employees who have changed jobs.

Command> SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID" 
 FROM employees e, job_history j 
 WHERE e.employee_id = j.employee_id 
 ORDER BY last_name, "Old Job ID";

< De Haan, AD_VP >
< Hartstein, MK_MAN >
< Kaufling, ST_MAN >
< Kochhar, AD_VP >
< Kochhar, AD_VP >
< Raphaely, PU_MAN >
< Taylor, SA_REP >
< Taylor, <NULL> >
< Whalen, AD_ASST >
< Whalen, <NULL> >
10 rows found.

NUMTODSINTERVAL

Converts a number or expression to an INTERVAL DAY TO SECOND type.

SQL syntax

NUMTODSINTERVAL (Expression1, IntervalUnit)

Parameters

NUMTODSINTERVAL has the parameters:

Parameter Description
Expression1 The argument can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.
IntervalUnit One of the string constants: 'DAY', 'HOUR', 'MINUTE', or 'SECOND'.

Examples

Example using NUMTODSINTERVAL with SYSDATE:

Command> SELECT SYSDATE + NUMTODSINTERVAL(20,'SECOND') FROM dual;
< 2007-01-28 09:11:06 >

NUMTOYMINTERVAL

Converts a number or expression to an INTERVAL YEAR TO MONTH type.

SQL syntax

NUMTOYMINTERVAL (Expression1, 'IntervalUnit')

Parameters

NUMTOYMINTERVAL has the parameters:

Parameter Description
Expression1 The argument can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.
IntervalUnit One of the string constants 'YEAR' or 'MONTH'.

Examples

An example using NUMTOYMINTERVAL:

Command> SELECT SYSDATE + NUMTOYMINTERVAL(1,'MONTH') FROM dual;
< 2007-02-28 09:23:28 >
1 row found.

NVL

The NVL function replaces a null value with a second value.

SQL syntax

NVL(Expression1, Expression2)

Parameters

NVL has the parameters:

Parameter Description
Expression1 The expression whose values are to be tested for NULL, which can be a CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB, or BLOB expression.
Expression2 The alternate value to use if the value of Expression1 is NULL, which can be a CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB, or BLOB expression.

Description

Examples

This example checks for null values of commission_pct and replaces them with 'Not Applicable' for employees whose last names start with "B".

Command> SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
       > FROM employees 
       > WHERE last_name LIKE 'B%'
       > ORDER BY last_name;

< Baer, Not Applicable >
< Baida, Not Applicable >
< Banda, .1 >
< Bates, .15 >
< Bell, Not Applicable >
< Bernstein, .25 >
< Bissot, Not Applicable >
< Bloom, .2 >
< Bull, Not Applicable >
9 rows found.

POWER

The POWER function returns Base raised to the Exponent power. The base and exponent can be any numbers, but if the Base is negative, the exponent must be an integer.

SQL syntax

POWER (Base, Exponent)

Parameters

POWER has the parameters:

Parameter Description
Base Operand or column can be any numeric type. POWER returns this value raised to Exponent power.
Exponent Operand or column can be any numeric type. If Base is negative, Exponent must be an integer.

Description

If either Base or Exponent is of type BINARY_FLOAT or BINARY_DOUBLE, the data type returned is BINARY_DOUBLE. If the Base is of type NUMBER or TT_DECIMAL, and the Exponent is not of type BINARY_FLOAT or BINARY_DOUBLE, the date type returned is NUMBER with maximum precision and scale. If Base is one of the TT* numeric types (TT_BIGINT, TT_INTEGER, TT_SMALLINT, or TT_TINYINT), the data type returned is BINARY_DOUBLE.

Example

Use the POWER function to return the commission_pct squared for the employee with employee_id equal to 145.

Command> SELECT employee_id, commission_pct FROM employees 
 WHERE employee_id = 145;

< 145, .4 >
1 row found.

Command> SELECT POWER (commission_pct,2) FROM employees WHERE employee_id = 145;
< .16 >
1 row found.

RANK

The RANK function is an analytic function that calculates the rank of a value in a group of values.

SQL syntax

RANK () OVER ( [QueryPartitionClause] OrderByClause )

Parameters

RANK has the parameters:

Parameter Description
QueryPartitionClause For information on syntax, semantics, and restrictions, see "Analytic functions".
OrderByClause For information on syntax, semantics, and restrictions, see "Analytic functions".

Description

Example

Use the RANK function to rank the first 10 employees in department 80 based on their salary and commission. Identical salary values receive the same rank and cause nonconsecutive ranks.

Command> SELECT first 10 department_id, last_name, salary, commission_pct,
       >  RANK() OVER (PARTITION BY department_id
       >    ORDER BY salary DESC, commission_pct) "Rank"
       > FROM employees WHERE department_id = 80
       > ORDER BY department_id, last_name, salary, commission_pct, "Rank";
< 80, Abel, 11000, .3, 5 >
< 80, Ande, 6400, .1, 31 >
< 80, Banda, 6200, .1, 32 >
< 80, Bates, 7300, .15, 26 >
< 80, Bernstein, 9500, .25, 14 >
< 80, Bloom, 10000, .2, 9 >
< 80, Cambrault, 7500, .2, 23 >
< 80, Cambrault, 11000, .3, 5
< 80, Doran, 7500, .3, 24 >
< 80, Errazuriz, 12000, .3, 3 >
10 rows found.

REPLACE

REPLACE substitutes a sequence of characters in a given string with another set of characters or removes the string entirely.

SQL syntax

REPLACE (String, SearchString [,ReplacementString] )

Parameters

REPLACE has the parameters:

Parameter Description
String Source string containing the substring to replace.
SearchString String of characters to be replaced in the original string. If SearchString is NULL, the original String is returned without any modification.
ReplacementString String of characters that are used to replace all occurrences of the search string in the original string. If ReplacementString is omitted or NULL, all occurrences of SearchString are removed from the source String.

Description

Examples

The following prints out all locations in Canada, replacing the country code of CA with Canada for easier readability.

Command> SELECT location_id, street_address,
> city, state_province, postal_code,
> REPLACE(country_id, 'CA', 'Canada') 
> FROM LOCATIONS 
> WHERE country_id LIKE 'CA';

< 1800, 147 Spadina Ave, Toronto, Ontario, M5V 2L7, Canada >
< 1900, 6092 Boxwood St, Whitehorse, Yukon, YSW 9T2, Canada >
2 rows found.

ROUND (date)

Returns date rounded to the unit specified by the format model fmt. The value returned is of type DATE. If you do not specify fmt, then date is rounded to the nearest day.

SQL syntax

ROUND (Date [,Fmt]) 

Parameters

ROUND (Date) has the parameters:

Parameter Description
Date The date that is rounded. Must resolve to a date value.

If you do not specify fmt, then date is rounded to the nearest day.

[,Fmt] The format model rounding unit. Specify either a constant or a parameter for fmt.

Description

Examples

Round Date to the first day of the following year by specifying 'YEAR' as the format model:

Command> SELECT ROUND (DATE '2007-08-25','YEAR') FROM dual;
< 2008-01-01 00:00:00 >
1 row found.

Omit Fmt. Specify Date as type TIMESTAMP with a time of 13:00:00. Date is rounded to nearest day:

Command> SELECT ROUND (TIMESTAMP '2007-08-16 13:00:00') FROM dual;
< 2007-08-17 00:00:00 >
1 row found.

ROUND (expression)

The ROUND function returns Expression1 rounded to Expression2 places to the right of the decimal point.

SQL syntax

ROUND (Expression1 [,Expression2])

Parameters

ROUND has the parameters:

Parameter Description
Expression1 Operand or column can be any numeric type.
Expression2 Operand or column that indicates how many places to round. Can be negative to round off digits left of the decimal point. If you omit Expression2, then Expression1 is rounded to 0 places. Must be an integer.

Description

Examples

Round a number 2 places to the right of the decimal point.

Command> SELECT ROUND (15.5555,2) FROM dual;
< 15.56 >
1 row found.

Round a number to the left of the decimal point by specifying a negative number for Expression2.

Command> SELECT ROUND (15.5555,-1) FROM dual;
< 20 >
1 row found.

Round a floating point number. Floating point numbers are rounded to nearest even value. Contrast this to rounding an expression of type NUMBER where the value is rounded up (for positive values).

Command> SELECT ROUND (1.5f), ROUND (2.5f) FROM dual;
< 2.00000000000000, 2.00000000000000 >
1 row found.
Command> SELECT ROUND (1.5), ROUND (2.5) FROM dual;
< 2, 3 >
1 row found.

ROW_NUMBER

The ROW_NUMBER function is an analytic function that assigns a unique number to each row to which it is applied (either each row in a partition or each row returned by the query), in the ordered sequence of rows specified in OrderByClause, beginning with 1.

SQL syntax

ROW_NUMBER () OVER ( [QueryPartitionClause] OrderByClause )

Parameters

ROW_NUMBER has the parameters:

Parameter Description
QueryPartitionClause For information on syntax, semantics, and restrictions, see "Analytic functions".
OrderByClause For information on syntax, semantics, and restrictions, see "Analytic functions".

Description

Example

Use ROW_NUMBER to return the three highest paid employees in each department. Fewer then three rows are returned for departments with fewer than three employees.

Command> SELECT FIRST 10 department_id, first_name, last_name, salary
       > FROM
       >    (SELECT department_id, first_name, last_name, salary, ROW_NUMBER()
       >       OVER (PARTITION BY department_id ORDER BY salary desc) rn
       >     FROM employees )
       > WHERE rn <= 3
       > ORDER BY department_id, salary DESC, last_name;
< 10, Jennifer, Whalen, 4400 >
< 20, Michael, Hartstein, 13000 >
< 20, Pat, Fay, 6000 >
< 30, Den, Raphaely, 11000 >
< 30, Alexander, Khoo, 3100 >
< 30, Shelli, Baida, 2900 >
< 40, Susan, Mavris, 6500 >
< 50, Adam, Fripp, 8200 >
< 50, Matthew, Weiss, 8000 >
< 50, Payam, Kaufling, 7900 >
10 rows found.

RPAD

The RPAD function returns Expression1, right-padded to length n characters with Expression2, replicated as many times as necessary. This function is useful for formatting the output of a query.

SQL syntax

RPAD (Expression1, n [,Expression2])

Parameters

RPAD has the parameters:

Parameter Description
Expression1 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column to be right-padded. If Expression1 is longer than n, then RPAD returns the portion of Expression1 that fits in n.
n Length of characters returned by RPAD function. Must be a NUMBER integer or a value that can be implicitly converted to a NUMBER integer.
Expression2 CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column to be right-padded to Expression1. If you do not specify Expression2, the default is a single blank.

Description

Examples

Concatenate first_name and last_name from the employees table. Call the RPAD function to return first_name right-padded to length 12 with spaces and call RPAD a second time to return last_name right-padded to length 12 with spaces. Select first five rows.

Command> SELECT FIRST 5 CONCAT (RPAD (first_name,12),
       > RPAD (last_name,12)) FROM employees
       > ORDER BY first_name, last_name;
< Adam        Fripp        >
< Alana       Walsh        >
< Alberto     Errazuriz    >
< Alexander   Hunold       >
< Alexander   Khoo         >
5 rows found.

Call the RPAD function to return last_name right-padded to length 20 characters with the dot ('.') character. Use the employees table and select first five rows.

Command> SELECT FIRST 5 RPAD (last_name,20,'.') FROM employees
       > ORDER BY last_name;
< Abel................ >
< Ande................ >
< Atkinson............ >
< Austin.............. >
< Baer................ >
5 rows found.

RTRIM

The RTRIM function removes from the right end of Expression1 all of the characters contained in Expression2. TimesTen scans Expression1 backward from its last character and removes all characters that appear in Expression2 until reaching a character not in Expression2 and then returns the result.

SQL syntax

RTRIM (Expression1 [,Expression2])

Parameters

RTRIM has the parameters:

Parameter Description
Expression1 The CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column to be trimmed. If Expression1 is a character literal, then enclose it in quotes.
Expression2 Optional expression used for trimming Expression1. If Expression2 is a character literal, enclose it in single quotes. If you do not specify Expression2, it defaults to a single blank. Operand or column can be of type CHAR,VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.

Description

Examples

The following example trims the trailing spaces from col1 in table rtrimtest.

Command> CREATE TABLE rtrimtest (col1 VARCHAR2 (25));
Command> INSERT INTO rtrimtest VALUES ('abc     ');
1 row inserted.
Command> SELECT * FROM rtrimtest;
< abc      >
1 row found.
Command> SELECT RTRIM (col1) FROM rtrimtest;
< abc >
1 row found.

Call the RTRIM function to remove right-most 'x' and 'y' from string. RTRIM removes individual occurrences of 'x' and 'y', not pattern 'xy'.

Command> SELECT RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy') FROM dual;
< RTRIM Example >
1 row found.

Call RTRIM to remove all characters from Expression1. In the first example, the data type is CHAR, so NULL is returned. In the second example, the data type is TT_CHAR, so the empty string is returned.

Command> CREATE TABLE rtrimtest (col1 CHAR (4), col2 TT_CHAR (4));
Command> INSERT INTO rtrimtest VALUES ('BBBA', 'BBBA');
1 row inserted.
Command> SELECT RTRIM (col1, 'AB') FROM rtrimtest;
< <NULL> >
1 row found.
Command> SELECT RTRIM (col2, 'AB') FROM rtrimtest;
<  >
1 row found.

SESSION_USER

Returns the name of the TimesTen user currently connected to the database.

SQL syntax

SESSION_USER

Parameters

SESSION_USER has no parameters.

Examples

To return the name of the session user:

SELECT SESSION_USER FROM dual;

SIGN

The SIGN function returns the sign of Expression.

SQL syntax

SIGN (Expression)

Parameters

SIGN has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type.

Description

Examples

These examples illustrate use of the SIGN function with different data types. Table signex has been created and the columns have been defined with different data types. First, describe the table signex to see the data types of the columns. Then select each column to retrieve values for that column. Use the SIGN function to return the sign for the column.

Command> DESCRIBE signex;

Table SAMPLEUSER.SIGNEX:
  Columns:
    COL1                            TT_INTEGER
    COL2                            TT_BIGINT
    COL3                            BINARY_FLOAT
    COL4                            NUMBER (3,2)

1 table found.
(primary key columns are indicated with *)
Command> SELECT col1 FROM signex;
< 10 >
< -10 >
< 0 >
3 rows found.
Command> SELECT SIGN (col1) FROM signex;
< 1 >
< -1 >
< 0 >
3 rows found.
Command> SELECT col2 FROM signex;
< 0 >
< -3 >
< 0 >
3 rows found.
Command> SELECT SIGN (col2) FROM signex;
< 0 >
< -1 >
< 0 >
3 rows found.
Command> SELECT col3 FROM signex;
< 3.500000 >
< -3.560000 >
< NAN >
3 rows found.
Command> SELECT SIGN (col3) FROM signex;
< 1 >
< -1 >
< 1 >
3 rows found.
Command> SELECT col4 FROM signex;
< 2.2 >
< -2.2 >
< 0 >
3 rows found.
Command> SELECT SIGN (col4) FROM signex;
< 1 >
< -1 >
< 0 >
3 rows found.

SOUNDEX

The SOUNDEX function determines a phonetic signature for a string and allows comparisons of strings based on phonetic similarity. SOUNDEX lets you compare words that are spelled differently, but sound alike in English.

SQL syntax

SOUNDEX (InputString)

Parameters

SOUNDEX has the parameters:

Parameter Description
InputString Valid types are CHAR, VARCHAR2, NCHAR and NVARCHAR2 with both ORA and TT variants and CLOB and NCLOB. If the data type is CLOB or NCLOB, TimesTen performs implicit conversion before returning the result.

Description

Examples

Use SOUNDEX function to return the phonetic signature for employees with last name equal to 'Taylor'.

Command> SELECT last_name, first_name, SOUNDEX (last_name)
       > FROM employees where last_name = 'Taylor';
< Taylor, Jonathon, T460 >
< Taylor, Winston, T460 >
2 rows found.

Invoke the function again to return the phonetic signature for the string 'Tailor'. Invoke the function a third time to return the last name and first name of each employee whose last name is phonetically similar to the string 'Tailor'.

Command> SELECT SOUNDEX ('Tailor') FROM dual;
< T460 >
1 row found.

Command> SELECT last_name, first_name FROM employees WHERE SOUNDEX (last_name) = 
       > SOUNDEX ('Tailor');
< Taylor, Jonathon >
< Taylor, Winston >
2 rows found.

SQRT

The SQRT function returns the square root of Expression.

SQL syntax

SQRT(Expression)

Parameters

SQRT has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type.

Description

Examples

Use SQRT function to return the square root of the absolute value of -10. Then cast the value as BINARY_FLOAT.

Command> SELECT CAST (SQRT (ABS (-10)) AS BINARY_FLOAT ) FROM dual;
< 3.162278 >
1 row found.

SUBSTR, SUBSTRB, SUBSTR4

Returns a string that represents a substring of a source string. The returned substring is of a specified number of characters, beginning from a designated starting point, relative to either the beginning or end of the string.

SQL syntax

{SUBSTR | SUBSTRB | SUBSTR4}=(Source, m, n)

Parameters

SUBSTR has the parameters:

Parameter Description
Source The string for which this function returns a substring. Value can be any supported character data types including CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB data types. Both TimesTen and Oracle Database data types are supported.

If Source is a CHAR string, the result is a CHAR or VARCHAR2 string. If Source is a NCHAR string, the result is a NVARCHAR2 string. If Source is a LOB, the result is the same LOB data type.

m The position at which to begin the substring. If m is positive, the first character of the returned string is m characters from the beginning of the string specified in char. Otherwise it is m characters from the end of the string. If ABS(m) is bigger than the length of the character string, a null value is returned.
n The number of characters to be included in the substring. If n is omitted, all characters to the end of the string specified in char are returned. If n is less than 1 or if char, m or n is NULL, NULL is returned.

Description

SUBSTR calculates lengths using characters as defined by character set. SUBSTRB uses bytes instead of characters. SUBSTR4 uses UCS4 code points.

Examples

In the first five rows of employees, select the first three characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,1,3) FROM employees;
< Kin >
< Koc >
< De  >
< Hun >
< Ern >
5 rows found.

In the first five rows of employees, select the last five characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,-5,5) FROM employees;
< <NULL> >
< chhar >
<  Haan >
< unold >
< Ernst >
5 rows found.

SUM

Finds the total of all values in the argument. Null values are ignored. SUM is an aggregate function. SUM can also be an aggregate analytic function. For more details on aggregate functions, see "Aggregate functions". For more information on analytic functions, see "Analytic functions".

SQL syntax

SUM ([ALL | DISTINCT] Expression) [OVER ([AnalyticClause])]

Parameters

SUM has the parameters:

Parameter Description
Expression Can be any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
ALL Includes any duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.
DISTINCT Eliminates duplicate column values from the argument of an aggregate function.
OVER ([AnalyticClause]) If specified, indicates aggregate analytic function. For more information on analytic functions, see "Analytic functions".

Description

Examples

Sum all employee salaries:

Command> SELECT SUM(salary) Total FROM employees;
 
TOTAL
< 691400 >
1 row found.

SYS_CONTEXT

Returns information about the current session.

The data type of the return value is VARCHAR2.

SQL syntax

SYS_CONTEXT('namespace', 'parameter' [, length ])

Parameters

SYS_CONTEXT has the parameters:

Parameter Description
namespace Value: USERENV

Other values result in a return of NULL.

parameter Supported values:
  • AUTHENTICATION_METHOD

  • CURRENT_USER

  • CURRENT_USERID

  • IDENTIFICATION_TYPE

  • LANG

  • LANGUAGE

  • NLS_SORT

  • SESSION_USER

  • SESSION_USERID

  • SID

length Length in bytes, from 1 to 4000.

These are descriptions of the supported values for parameter:

Parameter Description
AUTHENTICATION_METHOD Returns the method of authentication for these types of users:
  • Local database user authenticated by password

  • External user authenticated by the operating system

CURRENT_USER The name of the database user whose privileges are currently active. This may change during the duration of a session to reflect the owner of any active definer's rights object. When no definer's rights object is active, CURRENT_USER returns the same value as SESSION_USER. When used directly in the body of a view definition, this returns the user that is executing the cursor that is using the view. It does not respect views used in the cursor as being definer's rights.
CURRENT_USERID The identifier of the database user whose privileges are currently active
IDENTIFICATION_TYPE Returns the way the user was created in the database. Specifically, it reflects the IDENTIFIED clause in the CREATE/ALTER USER syntax. In the list that follows, the syntax used during user creation is followed by the identification type returned:
  • IDENTIFIED BY password: LOCAL

  • IDENTIFIED EXTERNALLY: EXTERNAL

LANG The ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter.
LANGUAGE The language and territory currently used by the session, along with the database character set, in this form:

language_territory.characterset

NLS_SORT Binary or linguistic sort.
SESSION_USER The name of the database user at logon. This value remains the same throughout the duration of the session.
SESSION_USERID The identifier of the database user at logon.
SID The connection ID of the current connection.

Description

The data type of the return value is VARCHAR2.

Examples

SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM dual;
< TTUSER >
1 row found.

SELECT SYS_CONTEXT('USERENV', 'LANGUAGE') FROM dual;
< AMERICAN_AMERICA.AL32UTF8 >
1 row found.

SELECT SYS_CONTEXT('USERENV', 'IDENTIFICATION_TYPE') FROM dual;
< EXTERNAL >
1 row found.

SYSDATE and GETDATE

Returns the date in the format YYYY-MM-DD HH:MM:SS. The date represents the local current date and time, which is determined by the system on which the statement is executed.

If you are using TimesTen type mode, for information on SYSDATE, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

SYSDATE | GETDATE( )

Parameters

The SYSDATE and GETDATE functions have no parameters.

Description

Examples

In this example, invoking SYSDATE returns the same date and time for all rows in the table:

Command> SELECT SYSDATE FROM dual;
< 2006-09-03 10:33:43 >
1 row found. 

This example invokes SYSDATE to insert the current data and time into column datecol:

Command> CREATE TABLE t (datecol DATE);
Command> INSERT INTO t VALUES (SYSDATE);
1 row inserted.
Command> SELECT * FROM t;
< 2006-09-03 10:35:50 >
1 row found.

In this example, GETDATE inserts the same date value for each new row in the table, even if the query takes several seconds.

INSERT INTO t1 SELECT GETDATE(), col1
   FROM t2 WHERE ...;

TO_CHAR is used with SYSDATE to return the date from table dual:

Command> SELECT TO_CHAR (SYSDATE) FROM dual;
< 2006-09-03 10:56:35 >
1 row found.

This example invokes TT_SYSDATE to return the TT_TIMESTAMP data type and then invokes ORA_SYSDATE to return the DATE data type:

Command> SELECT tt_sysdate FROM dual;
< 2006-10-31 20:02:19.440611 >
1 row found.
Command> SELECT ora_sysdate FROM dual;
< 2006-10-31 20:02:30 >
1 row found.

SYSTEM_USER

Returns the name of the current database user as identified by the operating system.

SQL syntax

SYSTEM_USER

Parameters

SYSTEM_USER has no parameters.

Examples

To return the name of the operating system user:

SELECT SYSTEM_USER FROM dual;

TIMESTAMPADD

The TIMESTAMPADD function adds a specified number of intervals to a timestamp and returns the modified timestamp.

SQL syntax

TIMESTAMPADD (Interval, IntegerExpression, TimestampExpression)

Parameters

TIMESTAMPADD has the parameters:

Parameter Description
Interval Specified interval. Must be expressed as literal. Valid values are listed in the description section.
IntegerExpression Expression that evaluates to TT_BIGINT.
TimestampExpression Datetime expressions. Valid data types are ORA_DATE, ORA_TIMESTAMP, TT_DATE, and TT_TIMESTAMP. (The alias DATE and TIMESTAMP data types are also valid). TT_TIME is not supported.

Description

Examples

Use the TIMESTAMPADD function to add 3 months to timestamp '2009-11-30 10:00:00'. TimesTen increments the year and adjusts the day component to accommodate the 28 days in the month of February.

Command> SELECT TIMESTAMPADD (SQL_TSI_MONTH, 3, TIMESTAMP '2010-11-30 10:00:00')
       > FROM dual;
< 2011-02-28 10:00:00 >
1 row found.

Use the TIMESTAMPADD function to add 1 second to timestamp '2010-12-31 23:59:59'. TimesTen propagates the overflow through all components of the timestamp and advances the components appropriately.

Command> SELECT TIMESTAMPADD (SQL_TSI_SECOND, 1, TIMESTAMP '2010-12-31 23:59:59')
       > FROM dual;
< 2011-01-01 00:00:00 >
1 row found.

TIMESTAMPDIFF

The TIMESTAMPDIFF function returns the total number of specified intervals between two timestamps.

SQL syntax

TIMESTAMPDIFF (Interval, TimestampExpression1, TimestampExpression2)

Parameters

TIMESTAMPDIFF has the parameters:

Parameter Description
Interval Specified interval. Must be expressed as literal. Valid values are listed in the description section.
TimestampExpression1 Datetime expressions. Valid data types are ORA_DATE, ORA_TIMESTAMP, TT_DATE, and TT_TIMESTAMP. (The alias DATE and TIMESTAMP data types are also valid). TT_TIME is not supported.
TimestampExpression2 Datetime expressions. Valid data types are ORA_DATE, ORA_TIMESTAMP, TT_DATE, and TT_TIMESTAMP. (The alias DATE and TIMESTAMP data types are also valid). TT_TIME is not supported.

Description

Examples

Use the TIMESTAMPDIFF function to calculate the difference in days between dates 2008-02-01 and 2008-03-01. Because 2008 is a leap year, the result is 29 days. The calculation is precise with no assumption of a 30-day month.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_DAY, DATE '2008-02-01',
       > DATE '2008-03-01') FROM dual;
< 29 >
1 row found.

Use the TIMESTAMPDIFF function to calculate the difference in months between dates 2009-02-01 and 2009-03-01. Because there is a crossing of the interval month boundary, the function returns 1. In the second example, because days is specified for the interval, the result is 28.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_MONTH, DATE '2009-02-01',
       > DATE '2009-03-01') FROM dual;
< 1 >
1 row found.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_DAY, DATE '2009-02-01',
       > DATE '2009-03-01') FROM dual;
< 28 >
1 row found.

Use the TIMESTAMPDIFF function to calculate the difference in months between dates 2009-02-01 and 2009-02-29. Because there is not a crossing of the interval month boundary, the function returns 0.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_MONTH, DATE '2009-02-01',
       > DATE '2009-02-28') FROM dual;
< 0 >
1 row found.

Use the TIMESTAMPDIFF function to illustrate the time difference in fractional seconds between mixed types. The time difference of one hour is returned in nanoseconds (unit for fractional seconds). The time element of the data type is set to 00:00:00.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_FRAC_SECOND,
       > TT_TIMESTAMP '2009-12-31 01:00:00.00', DATE '2009-12-31') FROM dual;
< -3600000000000 >
1 row found.

TO_BLOB

The TO_BLOB function converts VARBINARY or BINARY to a BLOB:

SQL syntax

TO_BLOB ( ValidDataType )

Parameters

TO_BLOB has the parameters:

Parameter Description
ValidDataType A value that is of VARBINARY or BINARY data type.

Examples

The following example creates a table with a BINARY and a VARBINARY columns. The TO_BLOB function is used on the values of these columns to convert the BINARY and VARBINARY data to a BLOB.

Command> CREATE TABLE bvar 
 > (col1 BINARY (10), col2 VARBINARY (10));

Command> INSERT INTO bvar (col1, col2)
 > VALUES (0x4D7953514C, 0x39274D);
1 row inserted.

Command> SELECT * FROM bvar;
 < 4D7953514C0000000000, 39274D >
1 row found.

Command> SELECT TO_BLOB(col1), TO_BLOB(col2) 
 > FROM bvar;
< 4D7953514C0000000000, 39274D >
1 row found.

TO_CHAR

The TO_CHAR function converts a DATE, TIMESTAMP or numeric input value to a VARCHAR2.

If you are using TimesTen type mode, for information on the TO_CHAR function, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

TO_CHAR ( Expression1[, Expression2 [, Expression3]])

Parameters

TO_CHAR has the parameters:

Parameter Description
Expression1 A DATE, TIMESTAMP, CLOB, NCLOB, or numeric expression.
Expression2 The format string. If omitted, TimesTen uses the default date format (YYYY-MM-DD).
Expression3 A CHAR or VARCHAR2 expression to specify the NLS parameter, which is currently ignored.

Description

Examples

SELECT FIRST 5 first_name, 
               TO_CHAR (hire_date, 'MONTH DD, YYYY'),
               TO_CHAR (salary, '$999999.99')
  FROM employees;
< Steven, JUNE      17, 1987,   $24000.00 >
< Neena, SEPTEMBER 21, 1989,   $17000.00 >
< Lex, JANUARY   13, 1993,   $17000.00 >
< Alexander, JANUARY   03, 1990,    $9000.00 >
< Bruce, MAY       21, 1991,    $6000.00 >
5 rows found.

SELECT TO_CHAR(-0.12,'$B99.9999') FROM dual;
<   -$.1200 >
1 row found.

SELECT TO_CHAR(-12, 'B99999PR') FROM dual;
<     12  >
1 row found.

SELECT TO_CHAR(-12,'FM99999') FROM dual;
< -12 >
1 row found.

SELECT TO_CHAR(1234.1,'9,999.999') FROM dual;
<  1,234.100 >
1 row found.

TO_CLOB

The TO_CLOB function converts one of the following values to a CLOB:

SQL syntax

TO_CLOB ( ValidDataType )

Parameters

TO_CLOB has the parameters:

Parameter Description
ValidDataType A value of one of the valid data types mentioned above.

Description

The TO_CLOB function will not operate on values contained in INTERVAL or TIMESTAMP with TIMEZONE data types.

Examples

The following example uses the TO_CLOB function to convert a string.

Command> DESCRIBE clob_content;
 
Table USER1.CLOB_CONTENT:
  Columns:
   *ID                              NUMBER (38) NOT NULL
    CLOB_COLUMN                     CLOB NOT NULL
 
1 table found.
(primary key columns are indicated with *)

Command> INSERT INTO clob_content (id, clob_column) 
 > VALUES (3, EMPTY_CLOB());
1 row inserted.

Command> UPDATE clob_content 
 > SET clob_column = TO_CLOB('Demonstration of the TO_CLOB function.') 
 > WHERE id = 3;
1 row updated.

TO_DATE

The TO_DATE function converts a CHAR, VARCHAR2, CLOB, or NCLOB argument to a value of DATE data type

If you are using TimesTen type mode, for information on the TO_DATE function, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

TO_DATE (Expression1[, Expression2 [, Expression3]])

Parameters

TO_DATE has the parameters:

Parameter Description
Expression1 A CHAR, VARCHAR2, CLOB, or NCLOB expression.
Expression2 The format string. This expression is usually required. It is optional only when Expression1 is in the default date format YYYY-MM-DD HHMMSS.
Expression3 A CHAR or VARCHAR2 expression to specify the NLS parameter, which is currently ignored.

Description

You can use a datetime format model with the TO_DATE function. For more information on datetime format models, see "Datetime format models".

Examples

Command> SELECT TO_DATE ('1999, JAN 14', 'YYYY, MON DD') FROM dual;
< 1999-01-14 00:00:00 >
1 row found.

Command> SELECT TO_CHAR(TO_DATE('1999-12:23','YYYY-MM:DD')) FROM dual;
< 1999-12-23 00:00:00 >
1 row found.

Command> SELECT TO_CHAR(TO_DATE('12-23-1997 10 AM:56:20', 
'MM-DD-YYYY HH AM:MI:SS'), 'MONTH DD, YYYY HH:MI-SS AM') FROM DUAL;
< DECEMBER  23, 1997 10:56-20 AM >
1 row found.

Command> SELECT TO_CHAR(TO_DATE('12-23-1997 15:56:20', 
'MM-DD-YYYY HH24:MI:SS'), 'MONTH DD, YYYY HH24:MI-SS') FROM DUAL;
< DECEMBER  23, 1997 15:56-20 >
1 row found.

TO_LOB

The TO_LOB function converts supplied TT_VARCHAR and VARCHAR2 data types to a CLOB and VARBINARY data types to a BLOB.

SQL syntax

TO_LOB ( ValidDataType )

Parameters

TO_LOB has the parameters:

Parameter Description
ValidDataType A value that is of TT_VARCHAR, VARCHAR2, or BINARY data types.

Description

You can use this function only on a TT_VARCHAR, VARCHAR2, or VARBINARY column, and only with the CREATE TABLE AS SELECT or INSERT...SELECT statements on tables with a defined LOB column.

Examples

The following example shows how to use the TO_LOB function within the INSERT...SELECT statement on a table with a LOB column.

Command> CREATE TABLE clb(c CLOB);
Command> CREATE TABLE vc (v VARCHAR2(2000));
Command> INSERT INTO vc(v) 
 > VALUES ('Showing the functionality of the TO_LOB function');
1 row inserted.

Command> INSERT INTO clb 
SELECT TO_LOB(v) FROM vc; 
1 row inserted.

Command> SELECT * FROM clb;
< Showing the functionality of the TO_LOB function >
1 row found.

Because of the restriction mentioned above, you cannot use the TO_LOB function in all cases where you can use the TO_CLOB or TO_BLOB functions. The following example demonstrates the error you receive when you try to use the TO_LOB function in this manner:

Command> SELECT TO_LOB(col1) 
 > FROM bvar;
 2610: Operand data type 'BINARY' invalid for operator 
 'TO_LOB' in expr ( TO_LOB( BVAR.COL1 ))
The command failed. 

TO_NCLOB

The TO_NCLOB function converts one of the following values to a NCLOB:

SQL syntax

TO_NCLOB ( ValidDataType )

Parameters

TO_NCLOB has the parameters:

Parameter Description
ValidDataType A value of one of the valid data types mentioned above.

Examples

The following converts the data in the VARCHAR2 job_title column to be of data type NCLOB.

Command> SELECT TO_NCLOB(job_title) FROM jobs;

< Public Accountant >
< Accounting Manager >
< Administration Assistant >
< President >
< Administration Vice President >
< Accountant >
< Finance Manager >
< Human Resources Representative >
< Programmer >
< Marketing Manager >
< Marketing Representative >
< Public Relations Representative >
< Purchasing Clerk >
< Purchasing Manager >
< Sales Manager >
< Sales Representative >
< Shipping Clerk >
< Stock Clerk >
< Stock Manager >
19 rows found.

TO_NUMBER

Converts an expression to a value of NUMBER type.

SQL syntax

TO_NUMBER (Expression[, Format]) 

Parameters

TO_NUMBER has the parameters:

Parameter Description
Expression The expression to be converted, where the value can be of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT, BINARY_DOUBLE, CLOB, or NCLOB.
Format If specified, the format is used to convert Expression to a value of NUMBER type. The format string identifies the number format model. The format and can be either a constant or a parameter.

Description

You can use a number format model with the TO_NUMBER function. For more information on number format models, see "Number format models".

Examples

Command> SELECT TO_NUMBER ('100.00', '999D99') FROM dual;
< 100 >
1 row found.

Command> SELECT TO_NUMBER ('1210.73', '9999.99') FROM dual;
< 1210.73 >
1 row found.

TRIM

The TRIM function trims leading or trailing characters (or both) from a character string.

SQL syntax

There are four syntax options for TRIM:

Parameters

TRIM has the parameters:

Parameter Description
LEADING | TRAILING | BOTH LEADING | TRAILING| BOTH are qualifiers to TRIM function. LEADING removes all leading instances of Trim_character from Expression. TRAILING removes all trailing instances of Trim_character from Expression. BOTH removes leading and trailing instances of Trim_character from Expression.
[Trim_character] If specified, Trim_character represents the CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column used for trimming Expression. Must be only one character. If you do not specify Trim_character, it defaults to a single blank. If Trim_character is a character literal, enclose it in single quotes.
Expression Expression is the CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB operand or column to be trimmed. If Expression is a character literal, enclose it in single quotes.

Description

Examples

Use TRIM function with qualifier to remove Trim_character '0' from Expression '0000TRIM Example0000':

Command> SELECT TRIM (LEADING '0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example0000 >
1 row found.
Command> SELECT TRIM (TRAILING '0' FROM '0000TRIM Example0000') FROM dual;
< 0000TRIM Example >
1 row found.
Command> SELECT TRIM (BOTH '0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function with qualifier to remove blank spaces. Do not specify a Trim_character. Default value for Trim_character is blank space:

Command> SELECT TRIM (LEADING FROM '    TRIM Example    ') FROM dual;
< TRIM Example     >
1 row found.
Command> SELECT TRIM (TRAILING FROM '    TRIM Example    ') FROM dual;
<     TRIM Example >
1 row found.
Command> SELECT TRIM (BOTH FROM '    TRIM Example    ') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function with Trim_character '0'. Do not specify a qualifier. Leading and trailing '0's are removed from Expression '0000TRIM Example0000':

Command> SELECT TRIM ('0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function without a qualifier or Trim_character. Leading and trailing spaces are removed.

< TRIM Example >
1 row found.
Command> SELECT TRIM ('    TRIM Example    ') FROM dual;

TRUNC (date)

Returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is of type DATE. If you do not specify fmt, then date is truncated to the nearest day.

SQL syntax

TRUNC (date [,fmt]) 

Parameters

TRUNC (date) has the parameters:

Parameter Description
date The date that is truncated. Specify the DATE data type for date. The function returns data type DATE with the time portion of the day truncated to the unit specified by the format model. If you do not specify fmt, the date is truncated to the nearest day. An error is returned if you do not specify the DATE data type.
[,fmt] The format model truncating unit. Specify either a constant or a parameter for fmt.

Description

For the permitted format models to use in fmt, see "Format model for ROUND and TRUNC date functions".

Examples

Command> SELECT TRUNC (TO_DATE ('27-OCT-92','DD-MON-YY'),'YEAR') FROM dual;
< 2092-01-01 00:00:00 >
1 row found.

TRUNC (expression)

Returns a number truncated to a certain number of decimal places.

SQL syntax

TRUNC (Expression [,m]) 

Parameters

TRUNC has the parameters:

Parameter Description
Expression The Expression to truncate. Operands must be of type NUMBER. An error is returned if operands are not of type NUMBER. The value returned is of type NUMBER.
[,m] The number of decimal places to truncate to. If m is omitted, then the number is truncated to 0 places. The value of m can be negative to truncate (make zero) m digits left of the decimal point.

Examples

SELECT TRUNC (15.79,1) FROM dual;
< 15.7 >
1 row found.

SELECT TRUNC (15.79,-1) FROM dual;
< 10 >
1 row found.

TT_HASH

The TT_HASH function returns the hash value of an expression or list of expressions. This value is the value that is used by a hash index.

SQL syntax

TT_HASH(Expression [,...])

Parameters

TT_HASH has the parameter:

Parameter Description
Expression [,...] One or more expressions to be used to determine the hash value of the expression or list of expressions.

Description

Examples

The following query finds the set of rows whose primary key columns hash to a given hash value:

SELECT * FROM t1 
  WHERE TT_HASH(pkey_col1, pkey_col2, pkey_col3) = 12345678; 

TTGRIDMEMBERID

When executed within a global query, gives the member ID in the cache grid of the owning member for each returned row.

SQL syntax

TTGRIDMEMBERID()

Parameters

TTGRIDMEMBERID() has no parameters.

Description

TTGRIDMEMBERID(), when executed within a global query, returns a TT_INTEGER with the member ID of the member where each returned row is located in the cache grid. If not executed within a global query, the current member ID is returned. NULL is returned if the member's node is not attached to a grid.

Autocommit should be set to OFF.

See "Obtaining information about the location of data in the cache grid" in Oracle In-Memory Database Cache User's Guide for more information on this function.

Examples

The following example shows autocommit set to OFF and sets the GlobalProcessing optimizer hint to 1, which makes the statements following part of a global query. The SELECT statement retrieves the employee number and member ID of the member where the employee data resides. The following output shows that the employee rows exist on members 2 and 4.

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, TTGRIDMEMBERID() FROM employees;
< 7900, 2 >
< 7902, 4 >
2 rows found.

The following example uses TTGRIDMEMBERID() in an ORDER BY clause.

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Command> SELECT employee_id, TTGRIDMEMBERID() FROM employees 
 ORDER BY TTGRIDMEMBERID();
< 7900, 2 >
< 7902, 4 >
2 rows found.
 

The following example uses TTGRIDMEMBERID() in a WHERE clause:

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Command> SELECT employee_id, TTGRIDMEMBERID() FROM employees 
 WHERE TTGRIDMEMBERID()=2;
< 7900, 2 >
1 row found.

The following example executes TTGRIDMEMBERID() as a local query to retrieve the local member ID, which is 1.

Command> SELECT TTGRIDMEMBERID() FROM dual;
< 1 >
1 row found.

TTGRIDNODENAME

When executed within a global query, returns the name of the node in a cache grid on which the data is located.

SQL syntax

TTGRIDNODENAME()

Parameters

TTGRIDNODENAME() has no parameters.

Description

TTGRIDNODENAME() returns a TT_VARCHAR(64) with the node name of the node on which each returned row is located, which shows the location of data in a cache grid. NULL is returned if the node is not attached to a grid.

Autocommit should be set to OFF.

See "Obtaining information about the location of data in the cache grid" in Oracle In-Memory Database Cache User's Guide for more information.

Examples

The following example shows autocommit set to OFF and sets the GlobalProcessing optimizer hint to 1, which makes the statements following part of a global query. The SELECT statement retrieves the employee number and name of the node where the employee data resides. The following output shows that the employee rows exist on members 2 and 4.

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, TTGRIDNODENAME() FROM employees;
< 7900, MYGRID_member2 >
< 7902, MYGRID_member4 >
2 rows found.

TTGRIDUSERASSIGNEDNAME

Within a global query, returns the user-assigned name of the node in a cache grid on which the data is located.

SQL syntax

TTGRIDUSERASSIGNEDNAME()

Parameters

TTGRIDUSERASSIGNEDNAME() has no parameters.

Description

TTGRIDUSERASSIGNEDNAME() returns a TT_VARCHAR(30) with the node name assigned by the user to the node of the grid on which the data is located. NULL is returned if the node is not attached to a grid.

Autocommit should be set to OFF.

See "Obtaining information about the location of data in the cache grid" in Oracle In-Memory Database Cache User's Guide for more information.

Examples

The following example includes a cache grid whose members have user-assigned names alone1, alone2, and an active standby pair on nodes cacheact and cachestand. The standby database has the same data as the active database, but the query does not return data from the standby database.

The following example retrieves employee_id and the user-assigned node name with TTGRIDUSERASSIGNEDNAME() from the employees table from the grid members. The returned rows show which grid node owns each row of the cache instance.

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, TTGRIDUSERASSIGNEDNAME() FROM employees;
Commmand> COMMIT;
< 100, alone1>
< 101, alone2>
< 102, cacheact>
< 103, alone1>
< 104, cacheact>
...

Subsequent queries can access the appropriate node without changing the ownership of the data. For example, execute this query on grid node cacheact, including TTGRIDUSERASSIGNEDNAME() to verify that cacheact is the node where the data is located:

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, last_name, hire_date, TTGRIDUSERASSIGNEDNAME() 
 FROM employees WHERE employee_id=104;
< 104, Ernst, cacheact, 1991-05-21 00:00:00 >

The following example retrieves the employee number and user-assigned name for the node on which the employee data exists and orders the returned data by the user-assigned name:

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, TTGRIDUSERASSIGNEDNAME() FROM employees ORDER BY
 TTGRIDUSERASSIGNEDNAME() ASC;
< 7900, member2 >
< 7902, member4 >
2 rows found.

UID

This function returns an integer (TT_INTEGER) that uniquely identifies the session user.

Examples

SELECT UID FROM dual;
< 10 >
1 row found.

UNISTR

The UNISTR function takes as its argument a string that resolves to data of type NVARCHAR2 and returns the value in UTF-16 format. Unicode escapes are supported. You can specify the Unicode encoding value of the characters in the string.

SQL syntax

UNISTR ('String')

Parameters

UNISTR has the parameter:

Parameter Description
'String' The string passed to the UNISTR function. The string resolves to type NVARCHAR2. TimesTen returns the value in UTF-16 format. You can specify Unicode escapes as part of the string.

Examples

The following example invokes the UNISTR function passing as an argument the string 'A\00E4a'. The value returned is the value of the string in UTF-16 format:

Command> SELECT UNISTR ('A\00E4a') FROM dual;
<Aäa> 1 row found.

USER

Returns the name of the TimesTen user who is currently connected to the database.

SQL syntax

USER

Parameters

USER has no parameters.

Examples

To return the name of the user who is currently connected to the database:

SELECT USER FROM dual;