16 ADRCI: ADR Command Interpreter

Creating a Logical Incident Package

You use variants of the IPS CREATE PACKAGE command to create a logical package (package).

To create a package based on an incident:

1. Start ADRCI in interactive mode, and ensure that the homepath points to the correct directory within the ADR base directory hierarchy.

   See "Starting ADRCI and Getting Help" and "Homepath" for details.

2. At the ADRCI prompt, enter the following command:

   IPS CREATE PACKAGE INCIDENT incident_number
   

   For example, the following command creates a package based on incident 3:

   IPS CREATE PACKAGE INCIDENT 3
   

   ADRCI generates output similar to the following:

   Created package 10 based on incident id 3, correlation level typical
   

   The package number assigned to this logical package is 10.

The following are variations on the IPS CREATE PACKAGE command:

IPS CREATE PACKAGE

This creates an empty package. You must use the IPS ADD INCIDENT or IPS ADD FILE commands to add diagnostic data to the package before generating it.

IPS CREATE PACKAGE PROBLEM problem_ID

This creates a package and includes diagnostic information for incidents that reference the specified problem ID. (Problem IDs are integers.) You can obtain the problem ID for an incident from the report displayed by the SHOW INCIDENT -MODE BRIEF command. Because there can be many incidents with the same problem ID, ADRCI adds to the package the diagnostic information for the first three incidents (early incidents) that occurred and last three incidents (late incidents) that occurred with this problem ID, excluding any incidents that are older than 90 days.

ADRCI may also add other incidents that correlate closely in time or in other criteria with the already added incidents.

IPS CREATE PACKAGE PROBLEMKEY "problem_key"

This creates a package and includes diagnostic information for incidents that reference the specified problem key. You can obtain problem keys from the report displayed by the SHOW INCIDENT command. Because there can be many incidents with the same problem key, ADRCI adds to the package only the diagnostic information for the first three early incidents and last three late incidents with this problem key, excluding incidents that are older than 90 days.

ADRCI may also add other incidents that correlate closely in time or in other criteria with the already added incidents.

The problem key must be enclosed in single quotation marks (') or double quotation marks (") if it contains spaces or quotation marks.

IPS CREATE PACKAGE SECONDS sec

This creates a package and includes diagnostic information for all incidents that occurred from sec seconds ago until now. sec must be an integer.

IPS CREATE PACKAGE TIME 'start_time' TO 'end_time'

This creates a package and includes diagnostic information for all incidents that occurred within the specified time range. start_time and end_time must be in the format 'YYYY-MM-DD HH24:MI:SS.FF TZR'. This is a valid format string for the NLS_TIMESTAMP_TZ_FORMAT initialization parameter. The fraction (FF) portion of the time is optional, and the HH24:MI:SS delimiters can be colons or periods.

For example, the following command creates a package with incidents that occurred between July 24th and July 30th of 2010:

IPS CREATE PACKAGE TIME '2010-07-24 00:00:00 -07:00' to '2010-07-30 23.59.59 -07:00'

Adding Diagnostic Information to a Logical Incident Package

You can add the following diagnostic information to an existing logical package (package):

• All diagnostic information for a particular incident

• A named file within the ADR

To add an incident to an existing package:

1. Start ADRCI in interactive mode, and ensure that the homepath points to the correct directory within the ADR base directory hierarchy.

   See "Starting ADRCI and Getting Help" and "Homepath" for details.

2. At the ADRCI prompt, enter the following command:

   IPS ADD INCIDENT incident_number PACKAGE package_number
   

To add a file in the ADR to an existing package:

• At the ADRCI prompt, enter the following command:

  IPS ADD FILE filespec PACKAGE package_number
  

  filespec must be a fully qualified file name (with path). Only files that are within the ADR base directory hierarchy may be added.

Generating a Physical Incident Package

When you generate a package, you create a physical package (a zip file) for an existing logical package.

To generate a physical incident package:

1. Start ADRCI in interactive mode, and ensure that the homepath points to the correct directory within the ADR base directory hierarchy.

   See "Starting ADRCI and Getting Help" and "Homepath" for details.

2. At the ADRCI prompt, enter the following command:

   IPS GENERATE PACKAGE package_number IN path
   

   This generates a complete physical package (zip file) in the designated path. For example, the following command creates a complete physical package in the directory /home/steve/diagnostics from logical package number 2:

   IPS GENERATE PACKAGE 2 IN /home/steve/diagnostics
   

You can also generate an incremental package containing only the incidents that have occurred since the last package generation.

To generate an incremental physical incident package:

• At the ADRCI prompt, enter the following command:

  IPS GENERATE PACKAGE package_number IN path INCREMENTAL
  

Using the <ADR_HOME> and <ADR_BASE> Variables in IPS Commands

The IPS command set provides shortcuts for referencing the current ADR home and ADR base directories. To access the current ADR home directory, use the <ADR_HOME> variable as follows:

ips add file <ADR_HOME>/trace/orcl_ora_13579.trc package 12

Use the <ADR_BASE> variable to access the ADR base directory as follows:

ips add file <ADR_BASE>/diag/rdbms/orcl/orcl/trace/orcl_ora_13579.trc package 12

IPS ADD

Purpose

Adds incidents to a package.

Syntax and Description

ips add {incident first [n] | incident inc_id | incident last [n] | 
     problem first [n] | problem prob_id | problem last [n] |
     problemkey pr_key | seconds secs | time start_time to end_time} 
     package package_id

Table 16-4 describes the arguments of IPS ADD.

Example

This example adds incident 22 to package 12:

ips add incident 22 package 12

This example adds the first three early incidents and the last three late incidents with problem ID 6 to package 2, exuding any incidents older than 90 days:

ips add problem 6 package 2

This example adds all incidents taking place during the last minute to package 5:

ips add seconds 60 package 5

This example adds all incidents taking place between 10:00 a.m. and 11:00 p.m. on May 1, 2010:

ips add  time '2010-05-01 10:00:00.00 -07:00' to '2010-05-01 23:00:00.00 -07:00'

IPS ADD FILE

Purpose

Adds a file to an existing package.

Syntax and Description

ips add file file_name package package_id

file_name is the full path name of the file. You can use the <ADR_HOME> and <ADR_BASE> variables if desired. The file must be under the same ADR base as the package.

package_id is the package ID.

Example

This example adds a trace file to package 12:

ips add file <ADR_HOME>/trace/orcl_ora_13579.trc package 12

IPS ADD NEW INCIDENTS

Purpose

Find and add new incidents for all of the problems in the specified package.

Syntax and Description

ips add new incidents package package_id

package_id is the ID of the package to update. Only new incidents of the problems in the package are added.

Example

This example adds up to three of the new late incidents for the problems in package 12:

ips add new incidents package 12

IPS COPY IN FILE

Purpose

Copies a file into the ADR from the external file system.

To edit a file in a package, you must copy the file out to a designated directory, edit the file, and copy it back into the package. You may want to do this to delete sensitive data in the file before sending the package to Oracle Support.

Syntax and Description

ips copy in file filename [to new_name][overwrite] package package_id
     [incident incid]

Copies an external file, filename (specified with full path name) into the ADR, associating it with an existing package, package_id, and optionally an incident, incid. Use the to new_name option to give the copied file a new file name within the ADR. Use the overwrite option to overwrite a file that exists already.

Example

This example copies a trace file from the file system into the ADR, associating it with package 2 and incident 4:

ips copy in file /home/nick/trace/orcl_ora_13579.trc to <ADR_HOME>/trace/orcl_ora_13579.trc package 2 incident 4

IPS COPY OUT FILE

Purpose

Copies a file from the ADR to the external file system.

To edit a file in a package, you must copy the file out to a designated directory, edit the file, and copy it back into the package. You may want to do this to delete sensitive data in the file before sending the package to Oracle Support.

Syntax and Description

ips copy out file source to target [overwrite]

Copies a file, source, to a location outside the ADR, target (specified with full path name). Use the overwrite option to overwrite the file that exists already.

Example

This example copies the file orcl_ora_13579.trc, in the trace subdirectory of the current ADR home, to a local folder.

ips copy out file <ADR_HOME>/trace/orcl_ora_13579.trc to /home/nick/trace/orcl_ora_13579.trc

IPS CREATE PACKAGE

Purpose

Creates a new package. ADRCI automatically assigns the package number for the new package.

Syntax and Description

ips create package {incident first [n] | incident inc_id | 
     incident last [n] | problem first [n] | problem prob_id |
     problem last [n] | problemkey prob_key | seconds secs | 
     time start_time to end_time} [correlate {basic |typical | all}]

Optionally, you can add incidents to the new package using the provided options.

Table 16-5 describes the arguments for IPS CREATE PACKAGE.

Examples

This example creates a package with no incidents:

ips create package

Output:

Created package 5 without any contents, correlation level typical

This example creates a package containing all incidents between 10 AM and 11 PM on the given day:

ips create package time '2010-05-01 10:00:00.00 -07:00' to '2010-05-01 23:00:00.00 -07:00'

Output:

Created package 6 based on time range 2010-05-01 10:00:00.00 -07:00 to 2010-05-01 23:00:00.00 -07:00, correlation level typical

This example creates a package and adds the first three early incidents and the last three late incidents with problem ID 3, excluding incidents that are older than 90 days:

ips create package problem 3

Output:

Created package 7 based on problem id 3, correlation level typical

IPS DELETE PACKAGE

Purpose

Drops a package and its contents from the ADR.

Syntax and Description

ips delete package package_id

package_id is the package to delete.

Example

ips delete package 12

IPS FINALIZE

Purpose

Finalizes a package before uploading.

Syntax and Description

ips finalize package package_id

package_id is the package ID to finalize.

Example

ips finalize package 12

IPS GENERATE PACKAGE

Purpose

Creates a physical package (a zip file) in target directory.

Syntax and Description

ips generate package package_id [in path] [complete | incremental]

package_id is the ID of the package to generate. Optionally, you can save the file in the directory path. Otherwise, the package is generated in the current working directory.

The complete option means the package forces ADRCI to include all package files. This is the default behavior.

The incremental option includes only files that have been added or changed since the last time that this package was generated. With the incremental option, the command finishes more quickly.

Example

This example generates a physical package file in path /home/steve:

ips generate package 12 in /home/steve

This example generates a physical package from files added or changed since the last generation:

ips generate package 14 incremental

IPS GET MANIFEST

Purpose

Extracts the manifest from a package zip file and displays it.

Syntax and Description

ips get manifest from file filename

filename is a package zip file. The manifest is an XML-formatted set of metadata for the package file, including information about ADR configuration, correlated files, incidents, and how the package was generated.

This command does not require an ADR home to be set before you can use it.

Example

ips get manifest from file /home/steve/ORA603_20060906165316_COM_1.zip

IPS GET METADATA

Purpose

Extracts ADR-related metadata from a package file and displays it.

Syntax and Description

ips get metadata {from file filename | from adr}

filename is a package zip file. The metadata in a package file (stored in the file metadata.xml) contains information about the ADR home, ADR base, and product.

Use the from adr option to get the metadata from a package zip file that has been unpacked into an ADR home using IPS UNPACK.

The from adr option requires an ADR home to be set.

Example

This example displays metadata from a package file:

ips get metadata from file /home/steve/ORA603_20060906165316_COM_1.zip

This next example displays metadata from a package file that was unpacked into the directory /scratch/oracle/package1:

set base /scratch/oracle/package1
ips get metadata from adr

In this previous example, upon receiving the SET BASE command, ADRCI automatically adds to the homepath the ADR home that was created in /scratch/oracle/package1 by the IPS UNPACK FILE command.

IPS PACK

Purpose

Creates a package and generates the physical package immediately.

Syntax and Description

ips pack [incident first [n] | incident inc_id | incident last [n] | 
     problem first [n] | problem prob_id | problem last [n] | 
     problemkey prob_key | seconds secs | time start_time to end_time] 
     [correlate {basic |typical | all}] [in path]

ADRCI automatically generates the package number for the new package. IPS PACK creates an empty package if no package contents are specified.

Table 16-6 describes the arguments for IPS PACK.

Example

This example creates an empty package:

ips pack

This example creates a physical package containing all information for incident 861:

ips pack incident 861

This example creates a physical package for all incidents in the last minute, fully correlated:

ips pack seconds 60 correlate all

IPS REMOVE

Purpose

Removes incidents from an existing package.

Syntax and Description

ips remove {incident inc_id | problem prob_id | problemkey prob_key} 
     package package_id

After removing incidents from a package, the incidents continue to be tracked within the package metadata to prevent ADRCI from automatically including them later (such as with ADD NEW INCIDENTS).

Table 16-7 describes the arguments of IPS REMOVE.

Example

This example removes incident 22 from package 12:

ips remove incident 22 package 12

IPS REMOVE FILE

Purpose

Removes a file from an existing package.

Syntax and Description

ips remove file file_name package package_id

file_name is the file to remove from package package_id. The complete path of the file must be specified. (You can use the <ADR_HOME> and <ADR_BASE> variables if desired.)

After removal, the file continues to be tracked within the package metadata to prevent ADRCI from automatically including it later (such as with ADD NEW INCIDENTS). Removing a file, therefore, only sets the EXCLUDE flag for the file to Explicitly excluded.

Example

This example removes a trace file from package 12:

ips remove file <ADR_HOME>/trace/orcl_ora_13579.trc package 12
Removed file <ADR_HOME>/trace/orcl_ora_13579.trc from package 12
ips show files package 12

.
.
.
FILE_ID                4
FILE_LOCATION          <ADR_HOME>/trace
FILE_NAME              orcl_ora_13579.trc
LAST_SEQUENCE          0
EXCLUDE                Explicitly excluded
.
.
.

IPS SET CONFIGURATION

Purpose

Changes the value of an IPS configuration parameter.

Syntax and Description

ips set configuration {parameter_id | parameter_name} value

parameter_id is the ID of the parameter to change, and parameter_name is the name of the parameter to change. value is the new value. For a list of the configuration parameters and their IDs, use "IPS SHOW CONFIGURATION".

Example

ips set configuration 3 10

IPS SHOW CONFIGURATION

Purpose

Displays a list of IPS configuration parameters and their values. These parameters control various thresholds for IPS data, such as timeouts and incident inclusion intervals.

Syntax and Description

ips show configuration {parameter_id | parameter_name}]

IPS SHOW CONFIGURATION lists the following information for each configuration parameter:

• Parameter ID

• Name

• Description

• Unit used by parameter (such as days or hours)

• Value

• Default value

• Minimum Value

• Maximum Value

• Flags

Optionally, you can get information about a specific parameter by supplying a parameter_id or a parameter_name.

Example

This command describes all IPS configuration parameters:

ips show configuration

Output:

PARAMETER INFORMATION:
   PARAMETER_ID           1
   NAME                   CUTOFF_TIME
   DESCRIPTION            Maximum age for an incident to be considered for 
                          inclusion
   UNIT                   Days
   VALUE                  90
   DEFAULT_VALUE          90
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           2
   NAME                   NUM_EARLY_INCIDENTS
   DESCRIPTION            How many incidents to get in the early part of the range
   UNIT                   Number
   VALUE                  3
   DEFAULT_VALUE          3
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           3
   NAME                   NUM_LATE_INCIDENTS
   DESCRIPTION            How many incidents to get in the late part of the range
   UNIT                   Number
   VALUE                  3
   DEFAULT_VALUE          3
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           4
   NAME                   INCIDENT_TIME_WINDOW
   DESCRIPTION            Incidents this close to each other are considered 
                          correlated
   UNIT                   Minutes
   VALUE                  5
   DEFAULT_VALUE          5
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           5
   NAME                   PACKAGE_TIME_WINDOW
   DESCRIPTION            Time window for content inclusion is from x hours 
                          before first included incident to x hours after last 
                          incident
   UNIT                   Hours
   VALUE                  24
   DEFAULT_VALUE          24
   MINIMUM                1
   MAXIMUM                4294967295
   FLAGS                  0
 
PARAMETER INFORMATION:
   PARAMETER_ID           6
   NAME                   DEFAULT_CORRELATION_LEVEL
   DESCRIPTION            Default correlation level for packages
   UNIT                   Number
   VALUE                  2
   DEFAULT_VALUE          2
   MINIMUM                1
   MAXIMUM                4
   FLAGS                  0

Examples

This command describes configuration parameter NUM_EARLY_INCIDENTS:

ips show configuration num_early_incidents

This command describes configuration parameter 3:

ips show configuration 3

Configuration Parameter Descriptions

Table 16-8 describes the IPS configuration parameters in detail.

IPS SHOW FILES

Purpose

Lists files included in the specified package.

Syntax and Description

ips show files package package_id

package_id is the package ID to display.

Example

This example shows all files associated with package 1:

ips show files package 1

Output:

FILE_ID                1
   FILE_LOCATION          <ADR_HOME>/alert
   FILE_NAME              log.xml
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                2
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              alert_adcdb.log
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                27
   FILE_LOCATION          <ADR_HOME>/incident/incdir_4937
   FILE_NAME              adcdb_ora_692_i4937.trm
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                28
   FILE_LOCATION          <ADR_HOME>/incident/incdir_4937
   FILE_NAME              adcdb_ora_692_i4937.trc
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                29
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              adcdb_ora_692.trc
   LAST_SEQUENCE          1
   EXCLUDE                Included
 
   FILE_ID                30
   FILE_LOCATION          <ADR_HOME>/trace
   FILE_NAME              adcdb_ora_692.trm
   LAST_SEQUENCE          1
   EXCLUDE                Included
.
.
.

IPS SHOW INCIDENTS

Purpose

Lists incidents included in the specified package.

Syntax and Description

ips show incidents package package_id

package_id is the package ID to display.

Example

This example lists the incidents in package 1:

ips show incidents package 1

Output:

MAIN INCIDENTS FOR PACKAGE 1:
   INCIDENT_ID            4985
   PROBLEM_ID             1
   EXCLUDE                Included
 
CORRELATED INCIDENTS FOR PACKAGE 1:

IPS SHOW PACKAGE

Purpose

Displays information about the specified package.

Syntax and Description

ips show package package_id {basic | brief | detail}

package_id is the ID of the package to display.

Use the basic option to display a minimal amount of information. It is the default when no package_id is specified.

Use the brief option to display more information about the package than the basic option. It is the default when a package_id is specified.

Use the detail option to show the information displayed by the brief option, as well as some package history and information about the included incidents and files.

Example

ips show package 12

ips show package 12 brief

IPS UNPACK FILE

Purpose

Unpackages a physical package file into the specified path.

Syntax and Description

ips unpack file file_name [into path]

file_name is the full path name of the physical package (zip file) to unpack. Optionally, you can unpack the file into directory path, which must exist and be writable. If you omit the path, the current working directory is used. The destination directory is treated as an ADR base, and the entire ADR base directory hierarchy is created, including a valid ADR home.

This command does not require an ADR home to be set before you can use it.

Example

ips unpack file /tmp/ORA603_20060906165316_COM_1.zip into /tmp/newadr

AVG

Returns the average value of an expression.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the AVG function in the SELECT command:

• The expression must be a numeric column or a positive numeric constant.

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

CONCAT

Returns a concatenation of two character strings. The character data can be of the data types CHAR and VARCHAR2. The return value is the same data type as the character data.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the CONCAT function in the SELECT command:

• The function does not support LOB data types, including BLOB, CLOB, NCLOB, and BFILE data types.

• The function does not support national character set data types, including NCHAR, NVARCHAR2, and NCLOB data types.

COUNT

Returns the number of rows returned by the query.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the COUNT function in the SELECT command:

• The expression must be a column, a numeric constant, or a string constant.

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

• The function always counts all rows for the query, including duplicates and nulls.

Examples

This example returns the number of incidents for which flood_controlled is 0 (zero):

select count(*) from incident where flood_controlled = 0

This example returns the number of problems for which problem_key includes ORA-600:

select count(*) from problem where problem_key like '%ORA-600%'

DECODE

Compares an expression to each search value one by one. If the expression is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle Database returns the specified default value.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the DECODE function in the SELECT command:

• The search arguments must be character data.

• A default value must be specified.

Example

This example shows each incident_id and whether or not the incident is flood-controlled. The example uses the DECODE function to display text instead of numbers for the flood_controlled field.

select incident_id, decode(flood_controlled, 0, \
  "Not flood-controlled", "Flood-controlled") from incident

LENGTH

Returns the length of a character string as defined by the input character set.

The character string can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The return value is of data type NUMBER. If the character sting has data type CHAR, then the length includes all trailing blanks. If the character string is null, then this function returns 0 (zero).

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The SELECT command does not support the following functions: LENGTHB, LENGTHC, LENGTH2, and LENGTH4.

Example

This example shows the problem_id and the length of the problem_key for each problem.

select problem_id, length(problem_key) from problem

MAX

Returns the maximum value of an expression.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the MAX function in the SELECT command:

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

Example

This example shows the maximum last_incident value for all of the recorded problems.

select max(last_incident) from problem

MIN

Returns the minimum value of an expression.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the MIN function in the SELECT command:

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

Example

This example shows the minimum first_incident value for all of the recorded problems.

select min(first_incident) from problem

NVL

Replaces null (returned as a blank) with character data in the results of a query. If the first expression specified is null, then NVL returns the second expression specified. If the first expression specified is not null, then NVL returns the value of the first expression.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the NVL function in the SELECT command:

• The replacement value (second expression) must be specified as character data.

• The function does not support data conversions.

Example

This example replaces NULL in the output for singalling_component with the text "No component."

select nvl(signalling_component, 'No component') from incident

REGEXP_LIKE

Returns rows that match a specified pattern in a specified regular expression.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the REGEXP_LIKE function in the SELECT command:

• The pattern match is always case-sensitive.

• The function does not support the match_param argument.

Example

This example shows the problem_id and problem_key for all problems where the problem_key ends with a number.

select problem_id, problem_key from problem \
  where regexp_like(problem_key, '[0-9]$') = true

SUBSTR

Returns a portion of character data. The portion of data returned begins at the specified position and is the specified substring length of characters long. SUBSTR calculates lengths using characters as defined by the input character set.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the SUBSTR function in the SELECT command:

• The function supports only positive integers. It does not support negative values or floating-point numbers.

• The SELECT command does not support the following functions: SUBSTRB, SUBSTRC, SUBSTR2, and SUBSTR4.

Example

This example shows each problem_key starting with the fifth character in the key.

select substr(problem_key, 5) from problem

SUM

Returns the sum of values of an expression.

Syntax

See Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the SUM function in the SELECT command:

• The expression must be a numeric column or a numeric constant.

• The function does not support the DISTINCT or ALL keywords.

• The function does not support the OVER clause.

TIMESTAMP_TO_CHAR

Converts a value of TIMESTAMP data type to a value of VARCHAR2 data type in a specified format. If you do not specify a format, then the function converts values to the default timestamp format.

Syntax

See the syntax of the TO_CHAR function in Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the TIMESTAMP_TO_CHAR function in the SELECT command:

• The function converts only TIMESTAMP data type. TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, and other data types are not supported.

• The function does not support the nlsparm argument. The function uses the default language for your session.

Example

This example converts the create_time for each incident from a TIMESTAMP data type to a VARCHAR2 data type in the DD-MON-YYYY format.

select timestamp_to_char(create_time, 'DD-MON-YYYY') from incident

TOLOWER

Returns character data, with all letters lowercase. The character data can be of the data types CHAR and VARCHAR2. The return value is the same data type as the character data. The database sets the case of the characters based on the binary mapping defined for the underlying character set.

Syntax

See the syntax of the LOWER function in Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the TOLOWER function in the SELECT command:

• The function does not support LOB data types, including BLOB, CLOB, NCLOB, and BFILE data types.

• The function does not support national character set data types, including NCHAR, NVARCHAR2, and NCLOB data types.

Example

This example shows each problem_key in all lowercase letters.

select tolower(problem_key) from problem

TOUPPER

Returns character data, with all letters uppercase. The character data can be of the data types CHAR and VARCHAR2. The return value is the same data type as the character data. The database sets the case of the characters based on the binary mapping defined for the underlying character set.

Syntax

See the syntax of the UPPER function in Oracle Database SQL Language Reference.

Restrictions

The following restrictions apply when you use the TOUPPER function in the SELECT command:

• The function does not support LOB data types, including BLOB, CLOB, NCLOB, and BFILE data types.

• The function does not support national character set data types, including NCHAR, NVARCHAR2, and NCLOB data types.

Example

This example shows each problem_key in all uppercase letters.

select toupper(problem_key) from problem