The terms literal and consistent value are synonymous and refer to a fixed data value. For example, "JACK", "BLUE ISLAND", and also "101" room all personality literals; 5001 is a numeric literal. Character literals room enclosed in single quotation point out so that can differentiate them indigenous schema object names.

You are watching: Both character literals and string literals can be assigned to a char variable.

This section has these topics:

Many SQL statements and also functions require you to specify character and also numeric literal meaning values. Friend can additionally specify literals as component of expressions and conditions. You deserve to specify personality literals v the "text" notation, nationwide character literals v the N"text" notation, and numeric literals with the integer, or number notation, relying on the paper definition of the literal. The syntactic forms of this notations show up in the sections the follow.

To point out a datetime or interval datatype as a literal, you should take right into account any type of optional precisions contained in the datatypes. Examples of clues datetime and interval datatypes together literals are provided in the relevant sections the "Datatypes".

Text Literals

Use the text literal notation come specify worths whenever "string" or shows up in the syntax the expressions, conditions, SQL functions, and also SQL declaration in other parts the this reference. This reference supplies the terms message literal, personality literal, and also string interchangeably. Text, character, and string literals are always surrounded by solitary quotation marks. If the syntax uses the term char, you can specify one of two people a message literal or an additional expression the resolves to personality data — because that example, the last_name tower of the hr.employees table. When char appears in the syntax, the single quotation marks are not used.

The syntax of text literals is together follows:


Description of the illustration text.gif

where N or n states the literal using the national character collection (NCHAR or NVARCHAR2 data). By default, text entered using this notation is interpreted into the nationwide character collection by means of the database character set when offered by the server. To protect against potential loss of data throughout the text literal conversion to the database character set, collection the setting variable ORA_NCHAR_LITERAL_REPLACE come TRUE. Doing so transparently replaces the n" internally and also preserves the text literal for SQL processing.

See Also: Database Globalization Support overview for much more information about N-quoted literals

In the height branch that the syntax:

c is any type of member the the user"s character set. A single quotation mark (") in ~ the literal should be came before by an escape character. To represent one solitary quotation note within a literal, go into two solitary quotation marks.

" " room two single quotation marks the begin and end message literals.

In the bottom branch of the syntax:

Q or q shows that the different quoting system will it is in used. This mechanism permits a wide selection of delimiters for the text string.

The outermost " " are two solitary quotation marks the precede and also follow, respectively, the opening and also closing quote_delimiter.

c is any member the the user"s personality set. Friend can incorporate quotation clues (") in the message literal made up of c characters. You can additionally include the quote_delimiter, as lengthy as that is not immediately followed by a single quotation mark.

quote_delimiter is any kind of single- or multibyte character other than space, tab, and also return. The quote_delimiter can be a single quotation mark. However, if the quote_delimiter appears in the text literal itself, ensure that it is not instantly followed by a single quotation mark.

If the opening quote_delimiter is one of <, , , or (, climate the closeup of the door quote_delimiter must be the corresponding >, , >, or ). In all various other cases, the opening and closing quote_delimiter need to be the same character.

Text literals have properties that both the CHAR and also VARCHAR2 datatypes:

Within expressions and also conditions, treats message literals together though they have actually the datatype CHAR by comparing them utilizing blank-padded comparison semantics.

A message literal have the right to have a maximum size of 4000 bytes.

Here are some valid message literals:

"Hello""""Jackie""s raincoat""09-MAR-98"N"nchar literal"Here room some valid text literals utilizing the different quoting mechanism:

q"!name like "%DBMS_%%"!"q""q"SELECT * from employees wherein last_name = "Smith";"nq"ï Ÿ1234 ï"q""name favor "<"""
See Also:

"Blank-Padded and Nonpadded to compare Semantics"

Numeric Literals

Use numeric literal meaning notation to specify fixed and floating-point numbers.

Integer Literals

You must use the essence notation to specify an creature whenever integer appears in expressions, conditions, SQL functions, and SQL statements defined in other parts of this reference.

The syntax of creature is as follows:


Description the the illustration integer.gif

where digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

An integer deserve to store a preferably of 38 number of precision.

Here are some valid integers:


NUMBER and Floating-Point Literals

You should use the number or floating-point notation to specify worths whenever number or n shows up in expressions, conditions, SQL functions, and SQL statements in other parts of this reference.

The syntax the number is together follows:


Description the the illustration number.gif


+ or - indicates a hopeful or negative value. If you omit the sign, then a positive value is the default.

digit is among 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.

e or E indicates that the number is stated in clinical notation. The number after the E clues the exponent. The exponent can selection from -130 come 125.

f or F indicates that the number is a 32-bit binary floating allude number (of form BINARY_FLOAT).

d or D shows that the number is a 64-bit binary floating point number (of form BINARY_DOUBLE)

If you omit f or F and d or D, then the number is of type NUMBER.

The suffix f (F) and also d (D) room supported just in floating-point number literals, not in character strings that room to be convert to NUMBER. That is, if is expecting a NUMBER and it encounters the wire "9", then it switch the string come the number 9. However, if encounters the cable "9f", climate conversion fails and an error is returned.

A number of type NUMBER have the right to store a best of 38 number of precision. If the literal meaning requires much more precision than provided by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then truncates the value. If the variety of the literal meaning exceeds the range supported by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, climate raises an error.

If friend have created a decimal character various other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, climate you must specify numeric literals v "text" notation. In this cases, instantly converts the message literal come a numeric value.


You cannot usage this notation because that floating-point number literals.

For example, if the NLS_NUMERIC_CHARACTERS parameter mentions a decimal character of comma, point out the number 5.123 together follows:

See Also:

ALTER SESSION and Database Reference

Here room some valid NUMBER literals:

25+6.340.525e-03-1Here are some precious floating-point number literals:

25f+6.34F0.5d-1DYou can additionally use the following supplied floating-point literals in cases where a worth cannot be expressed as a numeric literal:

binary_float_nanA value of form BINARY_FLOAT for which the condition IS NAN is true

SELECT COUNT(*) from employees wherein TO_BINARY_FLOAT(commission_pct)!= BINARY_FLOAT_NAN;

binary_float_infinitySingle-precision hopeful infinitySELECT COUNT(*) from employees whereby salary
binary_double_nanA worth of type BINARY_DOUBLE because that which the problem IS NAN is trueSELECT COUNT(*) from employees wherein TO_BINARY_FLOAT(commission_pct)!= BINARY_FLOAT_NAN;
binary_double_infinityDouble-precision optimistic infinitySELECT COUNT(*) indigenous employees wherein salary

Datetime Literals Database supports 4 datetime datatypes: DATE, TIMESTAMP, TIMESTAMP v TIME ZONE, and TIMESTAMP WITH neighborhood TIME ZONE.

Date LiteralsYou have the right to specify a date value together a wire literal, or girlfriend can transform a character or numeric worth to a date value through the TO_DATE function. Date literals space the only situation in i beg your pardon Database accepts a TO_DATE expression in ar of a cable literal.

To specify a date value as a literal, you need to use the Gregorian calendar. You have the right to specify one ANSI literal, as presented in this example:

TO_DATE("98-DEC-25 17:30","YY-MON-DD HH24:MI")The default date format for an date value is mentioned by the initialization parameter NLS_DATE_FORMAT. This example day format has a two-digit number because that the work of the month, an abbreviation the the month name, the last 2 digits of the year, and also a 24-hour time designation. immediately converts character values that room in the default date format into day values when they are supplied in date expressions.

If you specify a date value without a time component, then the default time is midnight (00:00:00 or 12:00:00 because that 24-hour and 12-hour clock time, respectively). If girlfriend specify a date value there is no a date, then the default date is the first day that the present month. day columns constantly contain both the date and also time fields. Therefore, if you query a day column, climate you must either specify the time field in your query or ensure that the time fields in the date column are collection to midnight. Otherwise, may not return the query outcomes you expect. You have the right to use the TRUNC (date) duty to collection the time ar to midnight, or you can incorporate a greater-than or less-than problem in the query instead of an equality or inequality condition.

Here are some instances that i think a table my_table v a number shaft row_num and a date column datecol:

INSERT right into my_table worths (1, SYSDATE);INSERT right into my_table worths (2, TRUNC(SYSDATE));SELECT * from my_table; ROW_NUM DATECOL---------- --------- 1 03-OCT-02 2 03-OCT-02SELECT * indigenous my_table wherein datecol = TO_DATE("03-OCT-02","DD-MON-YY"); ROW_NUM DATECOL---------- --------- 2 03-OCT-02SELECT * from my_table whereby datecol > TO_DATE("02-OCT-02", "DD-MON-YY"); ROW_NUM DATECOL---------- --------- 1 03-OCT-02 2 03-OCT-02If you know that the time fields of your day column are set to midnight, climate you deserve to query your date column as presented in the immediately preceding example, or by making use of the date literal:

Use the TO_DATE duty to mask out the time fields:

INSERT into my_table values (3, TO_DATE("3-OCT-2002","DD-MON-YYYY"));Use the date literal:

INSERT right into my_table worths (4, "03-OCT-02");Use the TRUNC function:

INSERT right into my_table values (5, TRUNC(SYSDATE));The date role SYSDATE returns the existing system date and time. The duty CURRENT_DATE return the existing session date. For info on SYSDATE, the TO_* datetime functions, and the default day format, watch "Datetime Functions".

TIMESTAMP LiteralsThe TIMESTAMP datatype stores year, month, day, hour, minute, and also second, and fractional 2nd values. As soon as you clues TIMESTAMP together a literal, the fractional_seconds_precision value deserve to be any number of digits as much as 9, together follows:

You can replace the UTC offset with the TZR (time zone region) layout element. Because that example, the following instance has the exact same value as the coming before example:

See Also:

"Datetime Expressions" for much more information

If you do not include the TZD layout element, and the datetime worth is ambiguous, then returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If that parameter is set to FALSE, then interprets the ambiguous datetime as traditional time in the stated region.

TIMESTAMP WITH regional TIME region LiteralsThe TIMESTAMP WITH local TIME region datatype differs from TIMESTAMP through TIME region in the data save in the database is normalized come the database time zone. The moment zone offset is not stored as part of the shaft data. There is no literal for TIMESTAMP WITH regional TIME ZONE. Rather, you stand for values the this datatype using any type of of the various other valid datetime literals. The table that complies with shows few of the layouts you deserve to use to insert a value into a TIMESTAMP WITH local TIME ar column, in addition to the equivalent value reverted by a query.

Notice the if the value mentioned does not encompass a time ingredient (either explicitly or implicitly, then the value went back defaults to midnight.

Interval Literals

An interval literal specifies a period of time. You can specify these differences in terms of years and months, or in terms of days, hours, minutes, and also seconds. Database support two species of term literals, YEAR to MONTH and DAY to SECOND. Each kind contains a leading field and may save a trailing field. The top field specifies the an easy unit of date or time being measured. The trailing field specifies the smallest increment the the straightforward unit gift considered. Because that example, a YEAR to MONTH expression considers an interval of year to the nearest month. A DAY to MINUTE expression considers an interval that days to the nearest minute.

If friend have day data in numeric form, climate you have the right to use the NUMTOYMINTERVAL or NUMTODSINTERVAL conversion function to convert the numeric data into interval values.

Interval literals space used mostly with analysis functions.

See Also:

"Analytic Functions", NUMTODSINTERVAL, NUMTOYMINTERVAL, and also Data Warehousing Guide


Specify YEAR to MONTH interval literals making use of the adhering to syntax:


Description the the illustration interval_year_to_month.gif


"integer <-integer>" mentions integer values for the leading and also optional trailing ar of the literal. If the leading ar is YEAR and also the trailing ar is MONTH, climate the selection of integer worths for the month ar is 0 come 11.

precision is the maximum number of digits in the leading field. The valid range of the leading ar precision is 0 come 9 and also its default value is 2.

Restriction ~ above the leading FieldIf friend specify a trailing field, it need to be less far-ranging than the top field. For example, expression "0-1" MONTH come YEAR is no valid.

The adhering to INTERVAL YEAR come MONTH literal suggests an interval of 123 years, 2 months:

INTERVAL "123-2" YEAR(3) to MONTHExamples that the other creates of the literal follow, consisting of some abbreviated versions:

Form of expression LiteralInterpretation
INTERVAL "123-2" YEAR(3) to MONTHAn expression of 123 years, 2 months. You must specify the leading field precision if that is better than the default that 2 digits.
INTERVAL "123" YEAR(3)An interval of 123 year 0 months.
INTERVAL "300" MONTH(3)An interval of 300 months.
INTERVAL "4" YEARMaps come INTERVAL "4-0" YEAR to MONTH and indicates 4 years.
INTERVAL "50" MONTHMaps to INTERVAL "4-2" YEAR to MONTH and also indicates 50 month or 4 years 2 months.
INTERVAL "123" YEARReturns one error, due to the fact that the default precision is 2, and also "123" has 3 digits.

You can add or subtract one expression YEAR to MONTH literal to or from one more to yield an additional INTERVAL YEAR come MONTH literal. Because that example:

INTERVAL "5-3" YEAR come MONTH + INTERVAL"20" MONTH = interval "6-11" YEAR to MONTH


Specify day TO 2nd interval literals using the following syntax:


Description that the illustration interval_day_to_second.gif


integer specifies the number of days. If this value contains much more digits than the number stated by the top precision, then return an error.

leading_precision is the number of digits in the top field. Accepted values are 0 to 9. The default is 2.

fractional_seconds_precision is the number of digits in the fractional part of the 2nd datetime field. Accepted values are 1 come 9. The default is 6.

Restriction top top the leading Field:If you specify a rolling field, it should be less far-ranging than the top field. Because that example, expression MINUTE to DAY is no valid. Together a an outcome of this restriction, if 2nd is the top field, the interval literal can not have any type of trailing field.

The valid range of worths for the trailing field are as follows:

HOUR: 0 to 23

MINUTE: 0 to 59

SECOND: 0 come 59.999999999

Examples of the various develops of INTERVAL work TO second literals follow, consisting of some abbreviation versions:

Form of expression LiteralInterpretation
INTERVAL "4 5:12:10.222" DAY come SECOND(3)4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths the a second.
INTERVAL "4 5:12" DAY to MINUTE4 days, 5 hours and 12 minutes.
INTERVAL "400 5" DAY(3) come HOUR400 job 5 hours.
INTERVAL "400" DAY(3)400 days.
INTERVAL "11:12:10.2222222" HOUR to SECOND(7)11 hours, 12 minutes, and also 10.2222222 seconds.
INTERVAL "11:20" HOUR to MINUTE11 hours and also 20 minutes.
INTERVAL "10" HOUR10 hours.
INTERVAL "10:22" MINUTE come SECOND10 minutes 22 seconds.
INTERVAL "10" MINUTE10 minutes.
INTERVAL "4" DAY4 days.
INTERVAL "25" HOUR25 hours.
INTERVAL "40" MINUTE40 minutes.
INTERVAL "120" HOUR(3)120 hours.
INTERVAL "30.12345" SECOND(2,4)30.1235 seconds. The fractional second "12345" is rounded to "1235" because the precision is 4.

See more: A Song For You Leon Russell Lyrics : Leon Russell, Leon Russell

You can include or subtract at some point TO 2nd interval literal meaning from an additional DAY TO 2nd literal. For example.