Selecting from the DUAL Table

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Please refer to "SQL Functions" for many examples of selecting a constant value from DUAL.

Datatypes

Each value manipulated by Oracle Database has a datatype. The datatype of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one datatype differently from values of another. For example, you can add values of NUMBER datatype, but not values of RAW datatype.
When you create a table or cluster, you must specify a datatype for each of its columns. When you create a procedure or stored function, you must specify a datatype for each of its arguments. These datatypes define the domain of values that each column can contain or each argument can have. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'. Each value subsequently placed in a column assumes the datatype of the column. For example, if you insert '01-JAN-98' into a DATE column, then Oracle treats the '01-JAN-98' character string as a DATE value after verifying that it translates to a valid date.
Oracle Database provides a number of built-in datatypes as well as several categories for user-defined types that can be used as datatypes. The syntax of Oracle datatypes appears in the diagrams that follow.

A datatype is either scalar or nonscalar. A scalar type contains an atomic value, whereas a nonscalar (sometimes called a "collection") contains a set of values. A large object (LOB) is a special form of scalar datatype representing a large scalar value of binary or character data. LOBs are subject to some restrictions that do not affect other scalar types because of their size. Those restrictions are documented in the context of the relevant SQL syntax.
The Oracle precompilers recognize other datatypes in embedded SQL programs. These datatypes are called external datatypes and are associated with host variables. Do not confuse built-in datatypes and user-defined types with external datatypes. For information on external datatypes, including how Oracle converts between them and built-in datatypes or user-defined types.

datatypes::=

Oracle_built_in_datatypes::=

character_datatypes::=

number_datatypes::=

long_and_raw_datatypes::=

datetime_datatypes::=

large_object_datatypes::=

rowid_datatypes::=

The ANSI-supported datatypes appear in the figure that follows. "ANSI, DB2, and SQL/DS Datatypes" discusses the mapping of ANSI-supported datatypes to Oracle built-in datatypes.

ANSI_supported_datatypes::=

Oracle_supplied_types::=

Oracle Built-in Datatypes

The table that follows summarizes Oracle built-in datatypes. Please refer to the syntax in the preceding sections for the syntactic elements. The codes listed for the datatypes are used internally by Oracle Database. The datatype code of a column or object attribute is returned by the DUMP function. First Number is code of each datatype.

1
VARCHAR2(size [BYTE CHAR])
Variable-length character string having maximum length size bytes or characters. Maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. You must specify size for VARCHAR2.
BYTE indicates that the column will have byte length semantics; CHAR indicates that the column will have character semantics.

1
NVARCHAR2(size)
Variable-length Unicode character string having maximum length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. You must specify size for NVARCHAR2.

2
NUMBER[(precision [, scale]])
Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.

8
LONG
Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility.

12
DATE
Valid date range from January 1, 4712 BC to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone.

21
BINARY_FLOAT
32-bit floating point number. This datatype requires 5 bytes, including the length byte.

22
BINARY_DOUBLE
64-bit floating point number. This datatype requires 9 bytes, including the length byte.

180
TIMESTAMP [(fractional_seconds)]
Year, month, and day values of date, as well as hour, minute, and second values of time, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values of fractional_seconds_precision are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It contains fractional seconds but does not have a time zone.

181
TIMESTAMP [(fractional_seconds)] WITH TIME ZONE
All values of TIMESTAMP as well as time zone displacement value, where fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 13 bytes. This datatype contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, and TIMEZONE_MINUTE. It has fractional seconds and an explicit time zone.

231
TIMESTAMP [(fractional_seconds)] WITH LOCAL TIME ZONE
All values of TIMESTAMP WITH TIME ZONE, with the following exceptions:
Data is normalized to the database time zone when it is stored in the database.
When the data is retrieved, users see the data in the session time zone.
The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The sizes varies from 7 to 11 bytes, depending on the precision.

182
INTERVAL YEAR [(year_precision)] TO MONTH
Stores a period of time in years and months, where year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default is 2. The size is fixed at 5 bytes.

183
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds)]
Stores a period of time in days, hours, minutes, and seconds, where
day_precision is the maximum number of digits in the DAY datetime 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 SECOND field. Accepted values are 0 to 9. The default is 6.
The size is fixed at 11 bytes.

23
RAW(size)
Raw binary data of length size bytes. Maximum size is 2000 bytes. You must specify size for a RAW value.

24
LONG RAW
Raw binary data of variable length up to 2 gigabytes.

69
ROWID
Base 64 string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.

208
UROWID [(size)]
Base 64 string representing the logical address of a row of an index-organized table. The optional size is the size of a column of type UROWID. The maximum size and default is 4000 bytes.

96
CHAR [(size [BYTE CHAR])]
Fixed-length character data of length size bytes. Maximum size is 2000 bytes or characters. Default and minimum size is 1 byte.
BYTE and CHAR have the same semantics as for VARCHAR2.

96
NCHAR[(size)]
Fixed-length character data of length size characters. The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding. Maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. Default and minimum size is 1 character.

112
CLOB
A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).

112
NCLOB
A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data.

113
BLOB
A binary large object. Maximum size is (4 gigabytes - 1) * (database block size).

114
BFILE
Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes.

_____________________________________________

The sections that follow describe the Oracle datatypes as they are stored in Oracle Database. For information on specifying these datatypes as literals, please refer to "Literals".

Character Datatypes
Character datatypes store character (alphanumeric) data, which are words and free-form text, in the database character set or national character set. They are less restrictive than other datatypes and consequently have fewer properties. For example, character columns can store all alphanumeric values, but NUMBER columns can store only numeric values.
Character data is stored in strings with byte values corresponding to one of the character sets, such as 7-bit ASCII or EBCDIC, specified when the database was created. Oracle Database supports both single-byte and multibyte character sets.
These datatypes are used for character data:

CHAR Datatype
NCHAR Datatype
NVARCHAR2 Datatype
VARCHAR2 Datatype

For information on specifying character datatypes as literals, please refer to "Text Literals".

CHAR Datatype
The CHAR datatype specifies a fixed-length character string. Oracle ensures that all values stored in a CHAR column have the length specified by size. If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. If you try to insert a value that is too long for the column, then Oracle returns an error.
The default length for a CHAR column is 1 byte and the maximum allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10) column, but the string is blank-padded to 10 bytes before it is stored.
When you create a table with a CHAR column, by default you supply the column length in bytes. The BYTE qualifier is the same as the default. If you use the CHAR qualifier, for example CHAR(10 CHAR), then you supply the column length in characters. A character is technically a code point of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. The BYTE and CHAR qualifiers override the semantics specified by the NLS_LENGTH_SEMANTICS parameter, which has a default of byte semantics. For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter.
To ensure proper data conversion between databases with different character sets, you must ensure that CHAR data consists of well-formed strings.

NCHAR Datatype
The NCHAR datatype is a Unicode-only datatype. When you create a table with an NCHAR column, you define the column length in characters. You define the national character set when you create your database.
The maximum length of a column is determined by the national character set definition. Width specifications of character datatype NCHAR refer to the number of characters. The maximum column size allowed is 2000 bytes.
If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length. You cannot insert a CHAR value into an NCHAR column, nor can you insert an NCHAR value into a CHAR column.
The following example compares the translated_description column of the pm.product_descriptions table with a national character set string:SELECT translated_description FROM product_descriptions
WHERE translated_name = N'LCD Monitor 11/PM';
Please refer to Oracle Database Globalization Support Guide for information on Unicode datatype support.

NVARCHAR2 Datatype
The NVARCHAR2 datatype is a Unicode-only datatype. When you create a table with an NVARCHAR2 column, you supply the maximum number of characters it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the maximum length of the column.
The maximum length of the column is determined by the national character set definition. Width specifications of character datatype NVARCHAR2 refer to the number of characters. The maximum column size allowed is 4000 bytes. Please refer to Oracle Database Globalization Support Guide for information on Unicode datatype support.

VARCHAR2 Datatype
The VARCHAR2 datatype specifies a variable-length character string. When you create a VARCHAR2 column, you supply the maximum number of bytes or characters of data that it can hold. Oracle subsequently stores each value in the column exactly as you specify it, provided the value does not exceed the column's maximum length of the column. If you try to insert a value that exceeds the specified length, then Oracle returns an error.
You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (''). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a code point of the database character set. CHAR and BYTE qualifiers override the setting of the NLS_LENGTH_SEMANTICS parameter, which has a default of bytes. For performance reasons, Oracle recommends that you use the NLS_LENGTH_SEMANTICS parameter to set length semantics and that you use the BYTE and CHAR qualifiers only when necessary to override the parameter. The maximum length of VARCHAR2 data is 4000 bytes. Oracle compares VARCHAR2 values using nonpadded comparison semantics.
To ensure proper data conversion between databases with different character sets, you must ensure that VARCHAR2 data consists of well-formed strings.

VARCHAR Datatype
Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.
Numeric Datatypes
The Oracle Database numeric datatypes store positive and negative fixed and floating-point numbers, zero, infinity, and values that are the undefined result of an operation (that is, is "not a number" or NAN). For information on specifying numeric datatypes as literals, please refer to "Numeric Literals".

NUMBER Datatype
The NUMBER datatype stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.
Specify a fixed-point number using the following form:

NUMBER(p,s)

where:

• p is the precision, or the total number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.
• s is the scale, or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.
• Positive scale is the number of significant digits to the right of the decimal point to and including the least significant digit.
• Negative scale is the number of significant digits to the left of the decimal point, to but not including the least significant digit. For negative scale the least significant digit is on the left side of the decimal point, because the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.

Scale can be greater than precision, most commonly when e notation is used. When scale is greater than precision, the precision specifies the maximum number of significant digits to the right of the decimal point. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point.
It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If a value exceeds the scale, then Oracle rounds it.
Specify an integer using the following form:

NUMBER(p)

This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

Specify a floating-point number using the following form:

NUMBER

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

Storage of Scale and Precision

Actual Data Specified As Stored As

123.89 NUMBER 123.89
123.89 NUMBER(3) 124
123.89 NUMBER(6,2) 123.89
123.89 NUMBER(6,1) 123.9
123.89 NUMBER(3) exceeds precision
123.89 NUMBER(4,2) exceeds precision
123.89 NUMBER(6,-2) 100
.01234 NUMBER(4,5) .01234
.00012 NUMBER(4,5) .00012
.000127 NUMBER(4,5) .00013
.0000012 NUMBER(2,7) .0000012
.00000123 NUMBER(2,7) .0000012
1.2e-4 NUMBER(2,5) 0.00012
1.2e-5 NUMBER(2,5) 0.00001

Floating-Point Numbers
Floating-point numbers can have a decimal point anywhere from the first to the last digit or can have no decimal point at all. An exponent may optionally be used following the number to increase the range (for example, 1.777 e-20). A scale value is not applicable to floating-point numbers, because the number of digits that can appear after the decimal point is not restricted.
Binary floating-point numbers differ from NUMBER in the way the values are stored internally by Oracle Database. Values are stored using decimal precision for NUMBER. All literals that are within the range and precision supported by NUMBER are stored exactly as NUMBER. Literals are stored exactly because literals are expressed using decimal precision (the digits 0 through 9). Binary floating-point numbers are stored using binary precision (the digits 0 and 1). Such a storage scheme cannot represent all values using decimal precision exactly. Frequently, the error that occurs when converting a value from decimal to binary precision is undone when the value is converted back from binary to decimal precision. The literal 0.1 is such an example.

Oracle Database provides two numeric datatypes exclusively for floating-point numbers:

BINARY_FLOAT

BINARY_FLOAT is a 32-bit, single-precision floating-point number datatype. Each BINARY_FLOAT value requires 5 bytes, including a length byte.

BINARY_DOUBLE

BINARY_DOUBLE is a 64-bit, double-precision floating-point number datatype. Each BINARY_DOUBLE value requires 9 bytes, including a length byte.

In a NUMBER column, floating point numbers have decimal precision. In a BINARY_FLOAT or BINARY_DOUBLE column, floating-point numbers have binary precision. The binary floating-point numbers support the special values infinity and NaN (not a number).
You can specify floating-point numbers within the limits listed in Table. The format for specifying floating-point numbers is defined in "Numeric Literals".

Table Floating Point Number Limits

Value Binary-Float Binary-Double

Maximum positive finite value 3.40282E+38F 1.79769313486231E+308
Minimum positive finite value 1.17549E-38F 2.22507485850720E-308

Oracle Database also supports the ANSI datatype FLOAT. You can specify this datatype using one of these syntactic forms:

FLOAT
FLOAT(n)

The number n indicates the number of bits of precision that the value can store. The value for n can range from 1 to 126. To convert from binary to decimal precision, multiply n by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

Numeric Precedence
Numeric precedence determines, for operations that support numeric datatypes, the datatype Oracle uses if the arguments to the operation have different datatypes. BINARY_DOUBLE has the highest numeric precedence, followed by BINARY_FLOAT, and finally by NUMBER. Therefore, in any operation on multiple numeric values:

• If any of the operands is BINARY_DOUBLE, then Oracle attempts to convert all the operands implicitly to BINARY_DOUBLE before performing the operation.
• If none of the operands is BINARY_DOUBLE but any of the operands is BINARY_FLOAT, then Oracle attempts to convert all the operands implicitly to BINARY_FLOAT before performing the operation.
• Otherwise, Oracle attempts to convert all the operands to NUMBER before performing the operation.

If any implicit conversion is needed and fails, then the operation fails.

In the context of other datatypes, numeric datatypes have lower precedence than the datetime/interval datatypes and higher precedence than character and all other datatypes.

LONG Datatype
LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer. LONG literals are formed as described for "Text Literals".

Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility.

Oracle also recommends that you convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG functionality has been static for several releases. See the modify_col_properties clause of ALTER TABLE and TO_LOB for more information on converting LONG columns to LOB.

You can reference LONG columns in SQL statements in these places:

• SELECT lists
• SET clauses of UPDATE statements
• VALUES clauses of INSERT statements

The use of LONG values is subject to these restrictions:

• A table can contain only one LONG column.
• You cannot create an object type with a LONG attribute.
• LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
• LONG columns cannot be indexed.
• LONG data cannot be specified in regular expressions.
• A stored function cannot return a LONG value.
• You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.
• Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
• LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
• If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

In addition, LONG columns cannot appear in these parts of SQL statements:

• GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
• The UNIQUE operator of a SELECT statement
• The column list of a CREATE CLUSTER statement
• The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
• SQL built-in functions, expressions, or conditions
• SELECT lists of queries containing GROUP BY clauses
• SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
• SELECT lists of CREATE TABLE ... AS SELECT statements
• ALTER TABLE ... MOVE statements
• SELECT lists in subqueries in INSERT statements

Triggers can use the LONG datatype in the following manner:

• A SQL statement within a trigger can insert data into a LONG column.
• If data from a LONG column can be converted to a constrained datatype (such as CHAR and VARCHAR2), then a LONG column can be referenced in a SQL statement within a trigger.
• Variables in triggers cannot be declared using the LONG datatype.
• :NEW and :OLD cannot be used with LONG columns.

You can use Oracle Call Interface functions to retrieve a portion of a LONG value from the database.

Datetime and Interval Datatypes
The datetime datatypes are DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. Values of datetime datatypes are sometimes called datetimes. The interval datatypes are INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Values of interval datatypes are sometimes called intervals. For information on expressing datetime and interval values as literals, please refer to "Datetime Literals" and "Interval Literals".
Both datetimes and intervals are made up of fields. The values of these fields determine the value of the datatype.

To avoid unexpected results in your DML operations on datetime data, you can verify the database and session time zones by querying the built-in SQL functions DBTIMEZONE and SESSIONTIMEZONE. If the time zones have not been set manually, Oracle Database uses the operating system time zone by default. If the operating system time zone is not a valid Oracle time zone, then Oracle uses UTC as the default value.

Datetime Fields and Values

YEAR -4712 to 9999 (excluding year 0)

MONTH 01 to 12

DAY 01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the current NLS calendar parameter)

HOUR 00 to 23

MINUTE 00 to 59

SECOND 00 to 59.9(n), where 9(n) is the precision of time fractional seconds. The 9(n) portion is not applicable for DATE.

TIMEZONE_HOUR -12 to 14 (This range accommodates daylight saving time changes.) Not applicable for DATE or TIMESTAMP.

TIMEZONE_MINUTE
(See note at end of table) 00 to 59. Not applicable for DATE or TIMESTAMP.

TIMEZONE_REGION Query the TZNAME column of the V$TIMEZONE_NAMES data dictionary view.

TIMEZONE_ABBR Query the TZABBREV column of the V$TIMEZONE_NAMES data dictionary view.
___________________________________________________________________________
Note: TIMEZONE_HOUR and TIMEZONE_MINUTE are specified together and interpreted as an entity in the format +- hh:mm, with values ranging from -12:59 to +14:00.

DATE Datatype
The DATE datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the DATE datatype has special associated properties. For each DATE value, Oracle stores the following information: century, year, month, date, hour, minute, and second.
You can specify a DATE value as a literal, or you can convert a character or numeric value to a date value with the TO_DATE function. For examples of expressing DATE values in both these ways, please refer to "Datetime Literals".

Using Julian Days
A Julian day number is the number of days since January 1, 4712 BC. Julian days allow continuous dating from a common reference. You can use the date format model "J" with date functions TO_DATE and TO_CHAR to convert between Oracle DATE values and their Julian equivalents.

Note:Oracle Database uses the astronomical system of calculating Julian days, in which the year 4713 BC is specified as -4712. The historical system of calculating Julian days, in contrast, specifies 4713 BC as -4713. If you are comparing Oracle Julian days with values calculated using the historical system, then take care to allow for the 365-day difference in BC dates.

The default date values are determined as follows:

• The year is the current year, as returned by SYSDATE.
• The month is the current month, as returned by SYSDATE.
• The day is 01 (the first day of the month).
• The hour, minute, and second are all 0.

These default values are used in a query that requests date values where the date itself is not specified, as in the following example, which is issued in the month of May:

SELECT TO_DATE('2005', 'YYYY') FROM DUAL;

TO_DATE('
---------
01-MAY-05

Example This statement returns the Julian equivalent of January 1, 1997:

SELECT TO_CHAR(TO_DATE('01-01-1997', 'MM-DD-YYYY'),'J')
FROM DUAL;

TO_CHAR
--------
2450450

See "Selecting from the DUAL Table" for a description of the DUAL table.

Thread for Problems......

I am creating this thread for problems..... If you have any problem just comment in this blog.

Literals

The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'JACK', 'BLUE ISLAND', and '101' are all character literals; 5001 is a numeric literal. Character literals are enclosed in single quotation marks so that Oracle can distinguish them from schema object names.

This section contains these topics:

Text Literals

Numeric Literals

Datetime Literals

Interval Literals

Many SQL statements and functions require you to specify character and numeric literal values. You can also specify literals as part of expressions and conditions. You can specify character literals with the 'text' notation, national character literals with the N'text' notation, and numeric literals with the integer, or number notation, depending on the context of the literal. The syntactic forms of these notations appear in the sections that follow.

To specify a datetime or interval datatype as a literal, you must take into account any optional precisions included in the datatypes. Examples of specifying datetime and interval datatypes as literals are provided in the relevant sections of "Datatypes".

Text Literals

Use the text literal notation to specify values whenever 'string' or appears in the syntax of expressions, conditions, SQL functions, and SQL statements in other parts of this reference. This reference uses the terms text literal, character literal, and string interchangeably. Text, character, and string literals are always surrounded by single quotation marks. If the syntax uses the term char, you can specify either a text literal or another expression that resolves to character data — for example, the last_name column of the hr.employees table. When char appears in the syntax, the single quotation marks are not used.

The syntax of text literals is as follows:

text::=

where N or n specifies the literal using the national character set (NCHAR or NVARCHAR2 data). By default, text entered using this notation is translated into the national character set by way of the database character set when used by the server. To avoid potential loss of data during the text literal conversion to the database character set, set the environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE. Doing so transparently replaces the n' internally and preserves the text literal for SQL processing.

In the top branch of the syntax:

• c is any member of the user's character set. A single quotation mark (') within the literal must be preceded by an escape character. To represent one single quotation mark within a literal, enter two single quotation marks.
• ' ' are two single quotation marks that begin and end text literals.

In the bottom branch of the syntax:

• Q or q indicates that the alternative quoting mechanism will be used. This mechanism allows a wide range of delimiters for the text string.
• The outermost ' ' are two single quotation marks that precede and follow, respectively, the opening and closing quote_delimiter.
• c is any member of the user's character set. You can include quotation marks (") in the text literal made up of c characters. You can also include the quote_delimiter, as long as it is not immediately followed by a single quotation mark.
• quote_delimiter is any single- or multibyte character except space, tab, and 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 immediately followed by a single quotation mark.
• If the opening quote_delimiter is one of [, {, <, or (, then the closing quote_delimiter must be the corresponding ], }, >, or ). In all other cases, the opening and closing quote_delimiter must be the same character.

Text literals have properties of both the CHAR and VARCHAR2 datatypes:

• Within expressions and conditions, Oracle treats text literals as though they have the datatype CHAR by comparing them using blank-padded comparison semantics.
• A text literal can have a maximum length of 4000 bytes.

Here are some valid text literals:


'Hello'
'ORACLE.dbs'
'Jackie''s raincoat'
'09-MAR-98'
N'nchar literal'


Here are some valid text literals using the alternative quoting mechanism:


q'!name LIKE '%DBMS_%%'!'
q'<'So,' she said, 'It's finished.'>'
q'{SELECT * FROM employees WHERE last_name = 'Smith';}'
nq'ï Ÿ1234 ï'
q'"name like '['"'

Numeric Literals


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


Integer Literals

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


The syntax of integer is as follows:

integer::=







where digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.
An integer can store a maximum of 38 digits of precision.
Here are some valid integers:
7
+255

NUMBER and Floating-Point Literals

You must use the number or floating-point notation to specify values whenever number or n appears in expressions, conditions, SQL functions, and SQL statements in other parts of this reference.
The syntax of number is as follows:

number::=

where

• + or - indicates a positive or negative value. If you omit the sign, then a positive value is the default.
  digit is one of 0, 1, 2, 3, 4, 5, 6, 7, 8 or 9.
• e or E indicates that the number is specified in scientific notation. The digits after the E specify the exponent. The exponent can range from -130 to 125.
• f or F indicates that the number is a 32-bit binary floating point number (of type BINARY_FLOAT).
• d or D indicates that the number is a 64-bit binary floating point number (of type BINARY_DOUBLE)

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

The suffixes f (F) and d (D) are supported only in floating-point number literals, not in character strings that are to be converted to NUMBER. That is, if Oracle is expecting a NUMBER and it encounters the string '9', then it converts the string to the number 9. However, if Oracle encounters the string '9f', then conversion fails and an error is returned.

A number of type NUMBER can store a maximum of 38 digits of precision. If the literal requires more precision than provided by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then Oracle truncates the value. If the range of the literal exceeds the range supported by NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, then Oracle raises an error.

If you have established a decimal character other than a period (.) with the initialization parameter NLS_NUMERIC_CHARACTERS, then you must specify numeric literals with 'text' notation. In these cases, Oracle automatically converts the text literal to a numeric value.

Note: You cannot use this notation for floating-point number literals.

For example, if the NLS_NUMERIC_CHARACTERS parameter specifies a decimal character of comma, specify the number 5.123 as follows:

'5,123'

Here are some valid NUMBER literals:

25
+6.34
0.5
25e-03
-1

Here are some valid floating-point number literals:

25f
+6.34F
0.5d
-1D

Datetime Literals

Oracle Database supports four datetime datatypes: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.

Date Literals You can specify a DATE value as a string literal, or you can convert a character or numeric value to a date value with the TO_DATE function. DATE literals are the only case in which Oracle Database accepts a TO_DATE expression in place of a string literal.

To specify a DATE value as a literal, you must use the Gregorian calendar. You can specify an ANSI literal, as shown in this example:

DATE '1998-12-25'

The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD'). Alternatively you can specify an Oracle date value, as in the following example:

TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI')

The default date format for an Oracle DATE value is specified by the initialization parameter NLS_DATE_FORMAT. This example date format includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation.
Oracle automatically converts character values that are in the default date format into date values when they are used 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 for 24-hour and 12-hour clock time, respectively). If you specify a date value without a date, then the default date is the first day of the current month.

Oracle DATE columns always contain both the date and time fields. Therefore, if you query a DATE column, then you must either specify the time field in your query or ensure that the time fields in the DATE column are set to midnight. Otherwise, Oracle may not return the query results you expect. You can use the TRUNC (date) function to set the time field to midnight, or you can include a greater-than or less-than condition in the query instead of an equality or inequality condition.

Here are some examples that assume a table my_table with a number column row_num and a DATE column datecol:

INSERT INTO my_table VALUES (1, SYSDATE);
INSERT INTO my_table VALUES (2, TRUNC(SYSDATE));

SELECT * FROM my_table;

ROW_NUM DATECOL
---------- ---------
1 03-OCT-02
2 03-OCT-02

SELECT * FROM my_table
WHERE datecol = TO_DATE('03-OCT-02','DD-MON-YY');

ROW_NUM DATECOL
---------- ---------
2 03-OCT-02

SELECT * FROM my_table
WHERE datecol > TO_DATE('02-OCT-02', 'DD-MON-YY');

ROW_NUM DATECOL
---------- ---------
1 03-OCT-02
2 03-OCT-02

If you know that the time fields of your DATE column are set to midnight, then you can query your DATE column as shown in the immediately preceding example, or by using the DATE literal:

SELECT * FROM my_table WHERE datecol = DATE '2002-10-03';

However, if the DATE column contains values other than midnight, then you must filter out the time fields in the query to get the correct result. For example:

SELECT * FROM my_table WHERE TRUNC(datecol) = DATE '2002-10-03';

Oracle applies the TRUNC function to each row in the query, so performance is better if you ensure the midnight value of the time fields in your data. To ensure that the time fields are set to midnight, use one of the following methods during inserts and updates:

• Use the TO_DATE function 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 INTO my_table VALUES (4, '03-OCT-02');

• Use the TRUNC function:

INSERT INTO my_table VALUES (5, TRUNC(SYSDATE));

The date function SYSDATE returns the current system date and time. The function CURRENT_DATE returns the current session date. For information on SYSDATE, the TO_* datetime functions, and the default date format, see "Datetime Functions".

TIMESTAMP Literals The TIMESTAMP datatype stores year, month, day, hour, minute, and second, and fractional second values. When you specify TIMESTAMP as a literal, the fractional_seconds_precision value can be any number of digits up to 9, as follows:

TIMESTAMP '1997-01-31 09:26:50.124'

TIMESTAMP WITH TIME ZONE Literals The TIMESTAMP WITH TIME ZONE datatype is a variant of TIMESTAMP that includes a time zone offset. When you specify TIMESTAMP WITH TIME ZONE as a literal, the fractional_seconds_precision value can be any number of digits up to 9. For example:

TIMESTAMP '1997-01-31 09:26:56.66 +02:00'

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the data. For example,

TIMESTAMP '1999-04-15 8:00:00 -8:00'

is the same as

TIMESTAMP '1999-04-15 11:00:00 -5:00'

That is, 8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern Standard Time.

You can replace the UTC offset with the TZR (time zone region) format element. For example, the following example has the same value as the preceding example:

TIMESTAMP '1999-04-15 8:00:00 US/Pacific'

To eliminate the ambiguity of boundary cases when the daylight saving time switches, use both the TZR and a corresponding TZD format element. The following example ensures that the preceding example will return a daylight saving time value:

TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'

You can also express the time zone offset using a datetime expression:

SELECT TIMESTAMP '1999-10-29 01:30:00' AT TIME ZONE 'US/Pacific' FROM DUAL;

If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If that parameter is set to FALSE, then Oracle interprets the ambiguous datetime as standard time in the specified region.

Introduction to Oracle SQL

Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications in turn must use SQL when executing the user's request.

How SQL Works

The strengths of SQL provide benefits for all types of users, including application programmers, database administrators, managers, and end users. Technically speaking, SQL is a data sublanguage. The purpose of SQL is to provide an interface to a relational database such as Oracle Database, and all SQL statements are instructions to the database. In this SQL differs from general-purpose programming languages like C and BASIC. Among the features of SQL are the following:

• It processes sets of data as groups rather than as individual units.
• It provides automatic navigation to the data.
• It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-control statements were not part of SQL originally, but they are found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as "persistent stored modules" (PSM), and the PL/SQL extension to Oracle SQL is similar to PSM.

SQL lets you work with data at the logical level. You need to be concerned with the implementation details only when you want to manipulate the data. For example, to retrieve a set of rows from a table, you define a condition used to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You need not deal with the rows one by one, nor do you have to worry about how they are physically stored or retrieved. All SQL statements use the optimizer, a part of Oracle Database that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.

SQL provides statements for a variety of tasks, including:

• Querying data
• Inserting, updating, and deleting rows in a table
• Creating, replacing, altering, and dropping objects
• Controlling access to the database and its objects
• Guaranteeing database consistency and integrity

SQL unifies all of the preceding tasks in one consistent language.

Lexical Conventions

The following lexical conventions for issuing SQL statements apply specifically to the Oracle Database implementation of SQL, but are generally acceptable in other SQL implementations.

When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the statement. Thus, Oracle Database evaluates the following two statements in the same manner:

SELECT last_name,salary*12,MONTHS_BETWEEN(hire_date, SYSDATE)
FROM employees
WHERE department_id = 30
ORDER BY last_name;

SELECT last_name,
salary * 12,
MONTHS_BETWEEN( hire_date, SYSDATE )
FROM employees
ORDER BY last_name;

Case is insignificant in reserved words, keywords, identifiers and parameters. However, case is significant in text literals and quoted names. Please refer to "Text Literals" for a syntax description of text literals.

My First Post

An oracle is a person or agency considered to be a source of wise counsel or prophetic opinion; an infallible authority, usually spiritual in nature. It can also be a prediction of the future, from the gods, that is spoken through another object or life-form. In the ancient world many sites gained a reputation for the dispensing of oracular wisdom: they too became known as "oracles", as did the oracular utterances themselves, whose very name is derived from the Latin verb orare, to speak.

Above paragraphs explains the sole purpose of this blog. Here I will try to give the answers of all the queries regarding the oracle, which most application developers face. I encourage people to give their solutions too, b'coz it is said somewhere that there is no wrong logic only strong or weak logic. So feel free to ask problems and in giving solutions.