• Home   /  
  • Archive by category "1"

Clusters In Pl/Sql What Is The Assignment Operator

Predefined PL/SQL datatypes are grouped into composite, , reference, and scalar type categories.

PL/SQL Number Types

Number types let you store numeric data (integers, real numbers, and floating-point numbers), represent quantities, and do calculations.

BINARY_INTEGER Datatype

The datatype is identical to . subtypes can be considered as subtypes. See "Change to the BINARY_INTEGER Datatype". To simplify the documentation, is primarily used throughout the book. See "PLS_INTEGER Datatype".

BINARY_INTEGER Subtypes

A base type is the datatype from which a subtype is derived. A subtype associates a base type with a constraint and so defines a subset of values. For your convenience, PL/SQL predefines the following subtypes:







The subtypes and let you restrict an integer variable to non-negative or positive values, respectively. and prevent the assigning of nulls to an integer variable. lets you restrict an integer variable to the values -1, 0, and 1, which is useful in programming tri-state logic.

BINARY_FLOAT and BINARY_DOUBLE Datatypes

Single-precision and double-precision IEEE 754-format single-precision floating-point numbers. These types are used primarily for high-speed scientific computation. For usage information, see "Writing Computation-Intensive Programs in PL/SQL". For information about writing math libraries that accept different numeric types, see "Guidelines for Overloading with Numeric Types".

Literals of these types end with (for ) or (for ). For example, or .

Computations involving these types produce special values that you need to check for, rather than raising exceptions. To help deal with overflow, underflow, and other conditions that can occur with these numbers, you can use several special predefined constants: , , , , , , and corresponding names starting with . The constants for (not a number) and infinity are also defined by SQL; the others are PL/SQL-only.

NUMBER Datatype

The datatype reliably stores fixed-point or floating-point numbers with absolute values in the range up to (but not including) . A variable can also represent . See Example 2-1.

Oracle recommends only using the value of a literal or result of a computation that falls within the specified range.

  • If the value of the literal or a computation is smaller than the range, the value is rounded to zero.

  • If the value of the literal exceeds the upper limit, a compilation error is raised.

  • If the value of a computation exceeds the upper limit, the result is undefined and leads to unreliable results and errors.

The syntax of a datatype is:

Precision is the total number of digits and scale is the number of digits to the right of the decimal point. You cannot use constants or variables to specify precision and scale; you must use integer literals.

To declare fixed-point numbers, for which you must specify scale, use the following form that includes both precision and scale:

To declare floating-point numbers, for which you cannot specify precision or scale because the decimal point can float to any position, use the following form without precision and scale:

To declare integers, which have no decimal point, use this form with precision only:

-- same as

The maximum precision that can be specified for a value is 38 decimal digits. If you do not specify precision, it defaults to 39 or 40, or the maximum supported by your system, whichever is less.

Scale, which can range from -84 to 127, determines where rounding occurs. For instance, a scale of 2 rounds to the nearest hundredth (3.4562 becomes 3.46). A negative scale rounds to the left of the decimal point. For example, a scale of -3 rounds to the nearest thousand (34562 becomes 34000). A scale of 0 rounds to the nearest whole number (3.4562 becomes 3). If you do not specify scale, it defaults to 0, as shown in the following example.

DECLARE x NUMBER(3); BEGIN x := 123.89; DBMS_OUTPUT.PUT_LINE('The value of x is ' || TO_CHAR(x)); END; /

The output is:

For more information on the datatype, see Oracle Database SQL Reference.

NUMBER Subtypes

You can use the following subtypes for compatibility with ANSI/ISO and IBM types or when you want a more descriptive name:











Use the subtypes , , and to declare fixed-point numbers with a maximum precision of 38 decimal digits.

Use the subtypes and to declare floating-point numbers with a maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits. Or, use the subtype REAL to declare floating-point numbers with a maximum precision of 63 binary digits, which is roughly equivalent to 18 decimal digits.

Use the subtypes , , and to declare integers with a maximum precision of 38 decimal digits.

PLS_INTEGER Datatype

You use the datatype to store signed integers. Its magnitude range is -2147483648 to 2147483647, represented in 32 bits. values require less storage than values and subtypes. Also, operations use hardware arithmetic, so they are faster than operations, which use library arithmetic. For efficiency, use for all calculations that fall within its magnitude range. For calculations outside the range of , you can use the datatype.

Note:

  • The and datatypes are identical. See "Change to the BINARY_INTEGER Datatype".

  • When a calculation with two PLS_INTEGER datatypes overflows the magnitude range of , an overflow exception is raised even if the result is assigned to a datatype.

PL/SQL Character and String Types

Character types let you store alphanumeric data, represent words and text, and manipulate character strings.

CHAR Datatype

You use the datatype to store fixed-length character data. How the data is represented internally depends on the database character set. The datatype takes an optional parameter that lets you specify a maximum size up to 32767 bytes. You can specify the size in terms of bytes or characters, where each character contains one or more bytes, depending on the character set encoding. The syntax follows:

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

If you do not specify a maximum size, it defaults to 1. If you specify the maximum size in bytes rather than characters, a variable might be too small to hold multibyte characters. To avoid this possibility, use the notation so that the variable can hold characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.

Although PL/SQL character variables can be relatively long, you cannot insert values longer than 2000 bytes into a database column.

You can insert any value into a database column because the maximum width of a column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a column into a variable. Note that the datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

When you do not use the or qualifiers, the default is determined by the setting of the initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.

For information on semantic differences between the and base types, see "Differences between the CHAR and VARCHAR2 Datatypes".

CHAR Subtype

The subtype has the same range of values as its base type. That is, is just another name for . You can use this subtype for compatibility with ANSI/ISO and IBM types or when you want an identifier more descriptive than .

LONG and LONG RAW Datatypes

Note:

The and datatypes are supported only for backward compatibility with existing applications. For new applications, use or in place of , and or in place of .

Oracle also recommends that you replace existing and datatypes with datatypes. datatypes are subject to far fewer restrictions than or datatypes. Further, functionality is enhanced in every release, whereas and functionality has been static for several releases. See "PL/SQL LOB Types".

You use the datatype to store variable-length character strings. The datatype is like the datatype, except that the maximum size of a value is 32760 bytes.

You use the datatype to store binary data or byte strings. data is like data, except that data is not interpreted by PL/SQL. The maximum size of a value is 32760 bytes.

You can insert any value into a database column because the maximum width of a column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32760 bytes from a column into a variable.

Likewise, you can insert any value into a database column because the maximum width of a column is 2147483648 bytes. However, you cannot retrieve a value longer than 32760 bytes from a column into a variable.

columns can store text, arrays of characters, or even short documents. You can reference columns in , , and (most) statements, but not in expressions, SQL function calls, or certain SQL clauses such as , , and . For more information, see Oracle Database SQL Reference.

In SQL statements, PL/SQL binds values as , not as . However, if the length of the bound exceeds the maximum width of a column (4000 bytes), Oracle converts the bind type to automatically, then issues an error message because you cannot pass values to a SQL function.

RAW Datatype

You use the datatype to store binary data or byte strings. For example, a variable might store a sequence of graphics characters or a digitized picture. Raw data is like data, except that PL/SQL does not interpret raw data. Likewise, Oracle Net does no character set conversions when you transmit raw data from one system to another.

The datatype takes a required parameter that lets you specify a maximum size up to 32767 bytes. The syntax follows:

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

You cannot insert values longer than 2000 bytes into a column. You can insert any value into a database column because the maximum width of a column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a column into a variable. Note that the datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

ROWID and UROWID Datatype

Internally, every database table has a pseudocolumn, which stores binary values called rowids. Each rowid represents the storage address of a row. A physical rowid identifies a row in an ordinary table. A logical rowid identifies a row in an index-organized table. The datatype can store only physical rowids. However, the (universal rowid) datatype can store physical, logical, or foreign (non-Oracle) rowids.

Note:

Use the datatype only for backward compatibility with old applications. For new applications, use the datatype.

When you select or fetch a rowid into a variable, you can use the built-in function , which converts the binary value into an 18-byte character string. Conversely, the function converts a character string into a rowid. If the conversion fails because the character string does not represent a valid rowid, PL/SQL raises the predefined exception . This also applies to implicit conversions.

To convert between variables and character strings, use regular assignment statements without any function call. The values are implicitly converted between and character types.

Physical Rowids

Physical rowids provide fast access to particular rows. As long as the row exists, its physical rowid does not change. Efficient and stable, physical rowids are useful for selecting a set of rows, operating on the whole set, and then updating a subset. For example, you can compare a variable with the pseudocolumn in the clause of an or statement to identify the latest row fetched from a cursor. See "Fetching Across Commits".

A physical rowid can have either of two formats. The 10-byte extended rowid format supports tablespace-relative block addresses and can identify rows in partitioned and non-partitioned tables. The 6-byte restricted rowid format is provided for backward compatibility.

Extended rowids use a base-64 encoding of the physical address for each row selected. For example, in SQL*Plus (which implicitly converts rowids into character strings), the query

SELECT rowid, last_name FROM employees WHERE employee_id = 120;

might return the following row:





The format, , has four parts:

  • : The data object number ( in the preceding example) identifies the database segment. Schema objects in the same segment, such as a cluster of tables, have the same data object number.

  • : The file number ( in the example) identifies the data file that contains the row. File numbers are unique within a database.

  • : The block number ( in the example) identifies the data block that contains the row. Because block numbers are relative to their data file, not their tablespace, two rows in the same tablespace but in different data files can have the same block number.

  • : The row number ( in the example) identifies the row in the block.

Logical Rowids

Logical rowids provide the fastest access to particular rows. Oracle uses them to construct secondary indexes on index-organized tables. Having no permanent physical address, a logical rowid can move across data blocks when new rows are inserted. However, if the physical location of a row changes, its logical rowid remains valid.

A logical rowid can include a guess, which identifies the block location of a row at the time the guess is made. Instead of doing a full key search, Oracle uses the guess to search the block directly. However, as new rows are inserted, guesses can become stale and slow down access to rows. To obtain fresh guesses, you can rebuild the secondary index.

You can use the pseudocolumn to select logical rowids (which are opaque values) from an index-organized table. Also, you can insert logical rowids into a column of type , which has a maximum size of 4000 bytes.

The statement helps you track the staleness of guesses. This is useful for applications that store rowids with guesses in a column, then use the rowids to fetch rows.

To manipulate rowids, you can use the supplied package . For more information, see Oracle Database PL/SQL Packages and Types Reference.

VARCHAR2 Datatype

You use the datatype to store variable-length character data. How the data is represented internally depends on the database character set. The datatype takes a required parameter that specifies a maximum size up to 32767 bytes. The syntax follows:

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.

Small variables are optimized for performance, and larger ones are optimized for efficient memory use. The cutoff point is 2000 bytes. For a that is bytes or longer, PL/SQL dynamically allocates only enough memory to hold the actual value. For a variable that is shorter than 2000 bytes, PL/SQL preallocates the full declared length of the variable. For example, if you assign the same 500-byte value to a variable and to a variable, the former takes up 500 bytes and the latter takes up 1999 bytes.

If you specify the maximum size in bytes rather than characters, a variable might be too small to hold multibyte characters. To avoid this possibility, use the notation so that the variable can hold characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1/2 or 1/3 as many characters as with a single-byte character set.

Although PL/SQL character variables can be relatively long, you cannot insert values longer than 4000 bytes into a database column.

You can insert any value into a database column because the maximum width of a column is 2147483648 bytes or two gigabytes. However, you cannot retrieve a value longer than 32767 bytes from a column into a variable. Note that the datatype is supported only for backward compatibility; see "LONG and LONG RAW Datatypes" more information.

When you do not use the or qualifiers, the default is determined by the setting of the initialization parameter. When a PL/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.

VARCHAR2 Subtypes

The subtypes and have the same range of values as their base type. For example, is just another name for .

You can use the subtypes for compatibility with ANSI/ISO and IBM types.

Currently, is synonymous with . However, in future releases of PL/SQL, to accommodate emerging SQL standards, might become a separate datatype with different comparison semantics. It is a good idea to use rather than .

PL/SQL National Character Types

The widely used one-byte ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, but some Asian languages, such as Japanese, contain thousands of characters. These languages require two or three bytes to represent each character. To deal with such languages, Oracle provides globalization support, which lets you process single-byte and multi-byte character data and convert between character sets. It also lets your applications run in different language environments.

With globalization support, number and date formats adapt automatically to the language conventions specified for a user session. Thus, users around the world can interact with Oracle in their native languages.

PL/SQL supports two character sets called the database character set, which is used for identifiers and source code, and the national character set, which is used for national language data. The datatypes and store character strings formed from the national character set.

When converting or data between databases with different character sets, make sure the data consists of well-formed strings. For more information or data, see Oracle Database Globalization Support Guide.

Comparing UTF8 and AL16UTF16 Encodings

The national character set represents data as Unicode, using either the or encoding.

Each character in the encoding takes up 2 bytes. This makes it simple to calculate string lengths to avoid truncation errors when mixing different programming languages, but requires extra storage overhead to store strings made up mostly of ASCII characters.

Each character in the encoding takes up 1, 2, or 3 bytes. This lets you fit more characters into a variable or table column, but only if most characters can be represented in a single byte. It introduces the possibility of truncation errors when transferring the data to a buffer measured in bytes.

Oracle recommends that you use the default encoding wherever practical, for maximum runtime reliability. If you need to determine how many bytes are required to hold a Unicode string, use the function rather than .

NCHAR Datatype

You use the datatype to store fixed-length (blank-padded if necessary) national character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding () or a fixed-width encoding (). Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data.

The datatype takes an optional parameter that lets you specify a maximum size in characters. The syntax follows:

Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the encoding, and 32767/3 in the encoding.

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.

If you do not specify a maximum size, it defaults to 1. The value always represents the number of characters, unlike which can be specified in either characters or bytes.

-- maximum size is 100 characters

You cannot insert values longer than 2000 bytes into an column.

If the value is shorter than the defined width of the column, Oracle blank-pads the value to the defined width.

You can interchange and values in statements and expressions. It is always safe to turn a value into an value, but turning an value into a value might cause data loss if the character set for the value cannot represent all the characters in the value. Such data loss can result in characters that usually look like question marks (?).

NVARCHAR2 Datatype

You use the datatype to store variable-length Unicode character data. How the data is represented internally depends on the national character set specified when the database was created, which might use a variable-width encoding () or a fixed-width encoding (). Because this type can always accommodate multibyte characters, you can use it to hold any Unicode character data.

The datatype takes a required parameter that specifies a maximum size in characters. The syntax follows:

Because the physical limit is 32767 bytes, the maximum value you can specify for the length is 32767/2 in the encoding, and 32767/3 in the encoding.

You cannot use a symbolic constant or variable to specify the maximum size; you must use an integer literal.

The maximum size always represents the number of characters, unlike which can be specified in either characters or bytes.

-- maximum size is 200 characters

The maximum width of a database column is 4000 bytes. Therefore, you cannot insert values longer than 4000 bytes into a column.

You can interchange and values in statements and expressions. It is always safe to turn a value into an value, but turning an value into a value might cause data loss if the character set for the value cannot represent all the characters in the value. Such data loss can result in characters that usually look like question marks (?).

PL/SQL LOB Types

The (large object) datatypes , , , and let you store blocks of unstructured data, such as text, graphic images, video clips, and sound waveforms. LOBs allow efficient, random, piece-wise access to the data. , , and are from 8 to 128 terabytes in size. The size of a is system dependent, but cannot exceed four gigabytes (4GB - 1 bytes).

The types differ from the and types in several ways. For example, s (except ) can be attributes of an object type, but s cannot. The maximum size of a , , or is 8 to 128 terabytes, but the maximum size of a is two gigabytes. Also, s support random access to data, but s support only sequential access. Note that the and datatypes are supported only for backward compatibility; see "LONG and LONG RAW Datatypes" for more information.

types store lob locators, which point to large objects stored in an external file, in-line (inside the row) or out-of-line (outside the row). Database columns of type , , , or store the locators. , , and data is stored in the database, in or outside the row. data is stored in operating system files outside the database.

PL/SQL operates on s through the locators. For example, when you select a column value, only a locator is returned. If you got it during a transaction, the locator includes a transaction ID, so you cannot use it to update that in another transaction. Likewise, you cannot save a locator during one session, then use it in another session.

You can also convert s to and types and vice versa, or s to and vice versa, which lets you use types in most SQL and PL/SQL statements and functions. To read, write, and do piecewise operations on s, you can use the supplied package .

For more information on LOBs, see Oracle Database Application Developer's Guide - Large Objects.

BFILE Datatype

You use the datatype to store large binary objects in operating system files outside the database. Every variable stores a file locator, which points to a large binary file on the server. The locator includes a directory alias, which specifies a full path name. Logical path names are not supported.

s are read-only, so you cannot modify them. Your DBA makes sure that a given exists and that Oracle has read permissions on it. The underlying operating system maintains file integrity.

s do not participate in transactions, are not recoverable, and cannot be replicated. The maximum number of open s is set by the Oracle initialization parameter , which is system dependent.

BLOB Datatype

You use the datatype to store large binary objects in the database, in-line or out-of-line. Every variable stores a locator, which points to a large binary object.

s participate fully in transactions, are recoverable, and can be replicated. Changes made by package can be committed or rolled back. locators can span transactions (for reads only), but they cannot span sessions.

CLOB Datatype

You use the datatype to store large blocks of character data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every variable stores a locator, which points to a large block of character data.

s participate fully in transactions, are recoverable, and can be replicated. Changes made by package can be committed or rolled back. locators can span transactions (for reads only), but they cannot span sessions.

NCLOB Datatype

You use the datatype to store large blocks of data in the database, in-line or out-of-line. Both fixed-width and variable-width character sets are supported. Every variable stores a locator, which points to a large block of data.

s participate fully in transactions, are recoverable, and can be replicated. Changes made by package can be committed or rolled back. locators can span transactions (for reads only), but they cannot span sessions.

PL/SQL Boolean Types

PL/SQL has a type for representing Boolean values (true and false). Because SQL does not have an equivalent type, you can use variables and parameters in PL/SQL contexts but not inside SQL statements or queries.

BOOLEAN Datatype

You use the datatype to store the logical values , , and (which stands for a missing, unknown, or inapplicable value). Only logic operations are allowed on variables.

The datatype takes no parameters. Only the values , , and can be assigned to a variable.

You cannot insert the values and into a database column. You cannot select or fetch column values into a variable. Functions called from a SQL query cannot take any parameters. Neither can built-in SQL functions such as ; to represent values in output, you must use or constructs to translate values into some other type, such as 0 or 1, 'Y' or 'N', 'true' or 'false', and so on.

PL/SQL Date, Time, and Interval Types

The datatypes in this section let you store and manipulate dates, times, and intervals (periods of time). A variable that has a date and time datatype holds values called datetimes. A variable that has an interval datatype holds values called intervals. A datetime or interval consists of fields, which determine its value. The following list shows the valid values for each field:

Field NameValid Datetime ValuesValid Interval Values
-4712 to 9999 (excluding year 0)Any nonzero integer
01 to 120 to 11
01 to 31 (limited by the values of and , according to the rules of the calendar for the locale)Any nonzero integer
00 to 230 to 23
00 to 590 to 59
00 to 59.9(n), where 9(n) is the precision of time fractional seconds0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
-12 to 14 (range accommodates daylight savings time changes)Not applicable
00 to 59Not applicable
Found in the view Not applicable
Found in the view Not applicable

Except for , these types are all part of the SQL92 standard. For information about datetime and interval format models, literals, time-zone names, and SQL functions, see Oracle Database SQL Reference.

DATE Datatype

You use the datatype to store fixed-length datetimes, which include the time of day in seconds since midnight. The date portion defaults to the first day of the current month; the time portion defaults to midnight. The date function returns the current date and time.

  • To compare dates for equality, regardless of the time portion of each date, use the function result in comparisons, operations, and so on.

  • To find just the time portion of a DATE variable, subtract the date portion: .

Valid dates range from January 1, 4712 BC to December 31, 9999 AD. A Julian date is the number of days since January 1, 4712 BC. Julian dates allow continuous dating from a common reference. You can use the date format model with the date functions and to convert between values and their Julian equivalents.

In date expressions, PL/SQL automatically converts character values in the default date format to values. The default date format is set by the Oracle initialization parameter . For example, the default might be , which includes a two-digit number for the day of the month, an abbreviation of the month name, and the last two digits of the year.

You can add and subtract dates. In arithmetic expressions, PL/SQL interprets integer literals as days. For instance, + 1 signifies the same time tomorrow.

TIMESTAMP Datatype

The datatype , which extends the datatype , stores the year, month, day, hour, minute, and second. The syntax is:

where the optional parameter specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The default is 6.

The default timestamp format is set by the Oracle initialization parameter .

In Example 3-1, you declare a variable of type , then assign a literal value to it. In the example, the fractional part of the seconds field is .

Example 3-1 Assigning a Literal Value to a TIMESTAMP Variable

DECLARE checkout TIMESTAMP(3); BEGIN checkout := '22-JUN-2004 07:48:53.275'; DBMS_OUTPUT.PUT_LINE( TO_CHAR(checkout)); END; /

In Example 3-2, the and functions are used to manipulate s.

Example 3-2 Using the SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN Functions

DECLARE right_now TIMESTAMP; yesterday TIMESTAMP; sometime TIMESTAMP; scn1 INTEGER; scn2 INTEGER; scn3 INTEGER; BEGIN right_now := SYSTIMESTAMP; -- Get the current SCN scn1 := TIMESTAMP_TO_SCN(right_now); DBMS_OUTPUT.PUT_LINE('Current SCN is ' || scn1); yesterday := right_now - 1; -- Get the SCN from exactly 1 day ago scn2 := TIMESTAMP_TO_SCN(yesterday); DBMS_OUTPUT.PUT_LINE('SCN from yesterday is ' || scn2); -- Find an arbitrary SCN somewhere between yesterday and today -- In a real program we would have stored the SCN at some significant moment scn3 := (scn1 + scn2) / 2; sometime := SCN_TO_TIMESTAMP(scn3); -- What time was that SCN was in effect? DBMS_OUTPUT.PUT_LINE('SCN ' || scn3 || ' was in effect at ' || TO_CHAR(sometime)); END; /

TIMESTAMP WITH TIME ZONE Datatype

The datatype , which extends the datatype , includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC)—formerly Greenwich Mean Time. The syntax is:

where the optional parameter specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The default is 6.

The default timestamp with time zone format is set by the Oracle initialization parameter .

In Example 3-3, you declare a variable of type , then assign a literal value to it:

Example 3-3 Assigning a Literal to a TIMESTAMP WITH TIME ZONE Variable

DECLARE logoff TIMESTAMP(3) WITH TIME ZONE; BEGIN logoff := '10-OCT-2004 09:42:37.114 AM +02:00'; DBMS_OUTPUT.PUT_LINE( TO_CHAR(logoff)); END; /

In this example, the time-zone displacement is +.

You can also specify the time zone by using a symbolic name. The specification can include a long form such as , an abbreviation such as , or a combination. For example, the following literals all represent the same time. The third form is most reliable because it specifies the rules to follow at the point when switching to daylight savings time.





You can find the available names for time zones in the and columns of the data dictionary view.

Two values are considered identical if they represent the same instant in UTC, regardless of their time-zone displacements. For example, the following two values are considered identical because, in UTC, 8:00 AM Pacific Standard Time is the same as 11:00 AM Eastern Standard Time:



TIMESTAMP WITH LOCAL TIME ZONE Datatype

The datatype , which extends the datatype , includes a time-zone displacement. The time-zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC)—formerly Greenwich Mean Time. You can also use named time zones, as with .

The syntax is

where the optional parameter specifies the number of digits in the fractional part of the seconds field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The default is 6.

This datatype differs from in that when you insert a value into a database column, the value is normalized to the database time zone, and the time-zone displacement is not stored in the column. When you retrieve the value, Oracle returns it in your local session time zone.

In Example 3-4, you declare a variable of type :

Example 3-4 Assigning a Literal Value to a TIMESTAMP WITH LOCAL TIME ZONE

DECLARE logoff TIMESTAMP(3) WITH LOCAL TIME ZONE; BEGIN -- logoff := '10-OCT-2004 09:42:37.114 AM +02:00'; raises an error logoff := '10-OCT-2004 09:42:37.114 AM '; -- okay without displacement DBMS_OUTPUT.PUT_LINE( TO_CHAR(logoff)); END; /

INTERVAL YEAR TO MONTH Datatype

You use the datatype to store and manipulate intervals of years and months. The syntax is:

where precision specifies the number of digits in the years field. You cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 4. The default is 2.

In Example 3-5, you declare a variable of type , then assign a value of 101 years and 3 months to it:

Example 3-5 Assigning Literals to an INTERVAL YEAR TO MONTH Variable

DECLARE lifetime INTERVAL YEAR(3) TO MONTH; BEGIN lifetime := INTERVAL '101-3' YEAR TO MONTH; -- interval literal lifetime := '101-3'; -- implicit conversion from character type lifetime := INTERVAL '101' YEAR; -- Can specify just the years lifetime := INTERVAL '3' MONTH; -- Can specify just the months END; /

INTERVAL DAY TO SECOND Datatype

You use the datatype to store and manipulate intervals of days, hours, minutes, and seconds. The syntax is:


)]

where and specify the number of digits in the days field and seconds field, respectively. In both cases, you cannot use a symbolic constant or variable to specify the precision; you must use an integer literal in the range 0 .. 9. The defaults are 2 and 6, respectively.

In Example 3-6, you declare a variable of type :

Example 3-6 Assigning Literals to an INTERVAL DAY TO SECOND Variable

DECLARE lag_time INTERVAL DAY(3) TO SECOND(3); BEGIN lag_time := '7 09:24:30'; IF lag_time > INTERVAL '6' DAY THEN DBMS_OUTPUT.PUT_LINE ( 'Greater than 6 days'); ELSE DBMS_OUTPUT.PUT_LINE ( 'Less than 6 days'); END IF; END; /

Datetime and Interval Arithmetic

PL/SQL lets you construct datetime and interval expressions. The following list shows the operators that you can use in such expressions:

Operand 1OperatorOperand 2Result Type
datetime
intervaldatetime
datetimeintervaldatetime
interval
datetimedatetime
datetimedatetimeinterval
interval
intervalinterval
intervalintervalinterval
interval
numericinterval
numeric
intervalinterval
interval
numericinterval

You can also manipulate datetime values using various functions, such as . For a list of such functions, see Table 2-4, "Built-In Functions".

For further information and examples of datetime arithmetic, see Oracle Database SQL Reference and Oracle Database Application Developer's Guide - Fundamentals.

Avoiding Truncation Problems Using Date and Time Subtypes

The default precisions for some of the date and time types are less than the maximum precision. For example, the default for is , while the highest precision is . To avoid truncation when assigning variables and passing procedure parameters of these types, you can declare variables and procedure parameters of the following subtypes, which use the maximum values for precision:






In the OLAP DML, as in other languages, a data type is a collection of values and the definition of one or more operations on those values.

Frequently, these data types are thought of as belonging to the following categories:

Numeric Data Types

The numeric data types described in Table 2-2, "OLAP DML Numeric Data Types" are supported.

Table 2-2 OLAP DML Numeric Data Types

Data TypeData Value

A whole number in the range of (-2**31) to (2**31)-1.

A whole number in the range of (-2**15) to (2**15)-1.

A whole number in the range of (-2**63) to (2**63)-1.

A decimal number with up to 15 significant digits in the range of -(10**308) to +(10**308).

A decimal number with up to 7 significant digits in the range of -(10**38) to +(10**38).

A decimal number with up to 38 significant digits in the range of -(10**125) to +(10**125).


For data entry, a value for any of these data types can begin with a plus (+) or minus (-) sign; it cannot contain commas. Note, however, that a comma is required before a negative number that follows another numeric expression, or the minus sign is interpreted as a subtraction operator. Additionally, a decimal value can contain a decimal point. For data display, thousands and decimal markers are controlled by the NLS_NUMERIC_CHARACTERS option.

Using LONGINTEGER Values

Most of the numeric data types return when a value is outside its range. However, the data type does not have overflow protection and returns an incorrect value when, for example, a calculation produces a number that exceeds its range. Use the data type instead of when this is likely to be a problem.

Using NUMBER Values

When you define a variable, you can specify its precision (p) and scale (s) so that it is sufficiently, but not unnecessarily, large. Precision is the number of significant digits. Scale can be positive or negative. Positive scale identifies the number of digits to the right of the decimal point; negative scale identifies the number of digits to the left of the decimal point that can be rounded up or down.

The data type is supported by Oracle Database standard libraries and operates the same way as it does in SQL. It is used for dimensions and surrogates when a text or data type is not appropriate. It is typically assigned to variables that are not used for calculations (like forecasts and aggregations), and it is used for variables that must match the rounding behavior of the database or require a high degree of precision. When deciding whether to assign the data type to a variable, keep the following facts in mind to maximize performance:

  • Analytic workspace calculations on variables is slower than other numeric data types because values are calculated in software (for accuracy) rather than in hardware (for speed).

  • When data is fetched from an analytic workspace to a relational column that has the data type, performance is best when the data has the data type in the analytic workspace because a conversion step is not required.

Text Data Types

The text data types described in Table 2-3, "OLAP DML Text Data Types" are supported by Oracle OLAP.

Table 2-3 OLAP DML Text Data Types

Data TypeData Value

Up to 4,000 bytes for each line in the database character set. This data type is equivalent to the and data types in the database.

Up to 4,000 bytes for each line in UTF-8 character encoding. This data type is equivalent to the and data types in the database.

Up to 8 single-byte characters for each line in the database character set. ( is valid only for values of simple dimensions, see DEFINE DIMENSION (simple).)


Text Literals

Enclose text literals in single quotes. Oracle OLAP recognizes unquoted alpha-numeric values as object names and double quotes as the beginning of a comment.

You can embed quoted strings within a quoted string, which is necessary when you want to specify the base dimension value of a composite or conjoint dimension or when a value includes an apostrophe. Because a single quotation mark is used in Oracle OLAP to indicate a text string, it is considered a special character when used within such a string. Consequently, to specify the literal value of a single quotation mark within a text string, precede the quotation mark with a backslash.

For example, suppose you want to find out if and are a valid combination of base dimension values in the conjoint dimension. The following statement produces the answer YES or .

SHOW ISVALUE(markprod, '<\'New York\' \'Apple Sauce\'>')

When embedded quoted strings have a further level of embedding, you must use backslashes before each special character, such as the apostrophe and the backslash that must precede it in "Joe's Deli," as shown in the following statement.

SHOW ISVALUE(markprod, '<\'Joe\\\'s Deli\' \'Apple Sauce\'>')

Escape Sequences

Table 2-4, "Recognized Escape Sequences" shows escape sequences that are recognized by Oracle OLAP.

Table 2-4 Recognized Escape Sequences

SequenceMeaning

Backspace

Form feed

Line feed

Carriage return

Horizontal tab

Double quote

Single quote

Backslash

nnn

Character with ASCII code nnn decimal, where \ indicates a decimal escape and nnn is the decimal value for the character

nn

Character with ASCII code nn hexadecimal, where indicates a hexadecimal escape and nn is the hexadecimal value for the character

nnnn

Character with Unicode nnnn, where indicates a Unicode escape and nnnn is a four-digit hexadecimal that represents the Unicode codepoint with the value +nnnn. The must be a capital letter.


Date-only Data Type

The Oracle OLAP DML DATE data type does not correspond to the SQL data type of the same name. It is, instead, is an older data type that is unique to the OLAP DML. The OLAP DML data type is a valid data type for variables and for dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR as discussed in the DEFINE DIMENSION (DWMQY) command topic. It is used to store day, month, and year data (but not hour and minute data) between January 1, 1000 A.D. and December 31, 9999 A.D. Because the OLAP DML data type does not include hour and minute data, it is often referred to as the DATE-only data type.

Tip:

The Oracle OLAP DML data type that corresponds to the SQL DATE data type is named . See DATETIME Data Type for more information.

Date-only Input Values

A valid input literal value of type DATE must conform to one of three styles: numeric, packed numeric, or month name. You can mix these styles throughout a session.

Tip:

To determine whether a text expression (such as an expression with a data type of TEXT or ID) represents a valid DATE-only value, use the ISDATE program

Numeric style

Specify the day, month, and year as three values with one or more separators between them, using these rules:

  • The day and month components can have one digit or two digits.

  • For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).

  • To separate the components, you can use a space, dash (), slash (), colon (), or comma ().

Examples: or

Packed numeric style

Specify the day, month, and year as three values with no separators between them, using these rules:

  • The day and month components must have two digits. When the day or month is less than 10, it must be preceded by a zero.

  • For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).

  • You cannot use any separators between the date components.

Examples: or

Month name style

Specify the day and year as values and the month as text, using these rules:

  • The month component must match one name listed in the MONTHNAMES option. You can abbreviate the month name to one letter or more, when you supply enough letters to uniquely match the beginning of a name in MONTHNAMES. The case of the letters in the month component (uppercase or lowercase) does not need to match the case in MONTHNAMES.

  • The day component can have one digit or two digits.

  • For any year, the year component can have four digits (for example, 1997). For years in the range 1950 to 2049, the year component can, alternatively, have two digits (50 represents 1950, and so on).

  • When the day and year components are adjacent, they must have at least one separator between them. As separators, you can use a space, dash (), slash (), colon (), or comma (). When you want, you can place one or more separators between the day and month or between the year and month.

Examples: or or

Date-only Dimension Values

The format of a -only value of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR is determined by the value name format (VNF) associated with the object. A VNF is a template that controls the input and display format for -only values. The template can include format specifications for any of the components that identify a time period (day, month, calendar year, fiscal year, and period within a fiscal year). You associate a VNF with an object by adding a VNF statement to its definition. When you do not add a VNF to the definition of an object, the object uses the default VNF shown in Table 2-5, "Default VNFs for DWMQY Dimensions".

Table 2-5 Default VNFs for DWMQY Dimensions

Type of DimensionDefault VNFExample

DAY

<DD><MTXT><YY>

01JAN95

WEEK

W<P>.<FF>

W1.95

Multiple WEEK

<NAME><P>.<FF>

MYWEEK1.95

MONTH

<MTXT><YY>

JAN95

Multiple MONTH

<NAME><P>.<FF>

MYMONTH1.95

QUARTER

Q<P>.<FF>

Q1.95

YEAR

YR<YY> 

YR95


-only values have independent input and output formats. You can enter -only values in one style and report them in a different style.

DATE-only Variable Display Styles

When you show a -only variable value in output, the format depends on the DATEFORMAT option. The default format is a 2-digit day, a 3-letter month, and a 2-digit year; for example, . The text for the month names depends on the MONTHNAMES option. To change the order of the month, day, and year components, see the DATEORDER option.

Datetime and Interval Data Types

The OLAP DML has data types that correspond to SQL datetime and interval data types. As outlined in Table 2-6, "OLAP DML Datetime and Interval Data Types and the Corresponding SQL Data Types", the names of the data types are different in OLAP DML than they are in SQL.

Table 2-6 OLAP DML Datetime and Interval Data Types and the Corresponding SQL Data Types

OLAP DML Data TypeCorresponding SQL Data Type

In the OLAP DML, the datetime data types are , , _TZ, and _LTZ. The interval data types are and . Both datetimes and intervals are made up of fields as discussed in "Datetime and Interval Fields".

Note:

The Oracle OLAP DML has a date data type named that does not correspond to the SQL data type of that name. (The OLAP DML DATE data type was implement before the SQL datetime and interval data types were implemented in the OLAP DML.) The OLAP DML DATE data type stores only date values (no time values) and is therefore sometimes referred to as the DATE-only data type.

Datetime and Interval Fields

Both datetimes and intervals are made up of fields. The values of these fields determine the value of the data type. Table 2-7, "Datetime Fields and Values" lists the datetime fields and their possible values for datetimes and intervals.

Tip:

To avoid unexpected results in your operations on datetime data, you can verify the database and session time zones using 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.

Table 2-7 Datetime Fields and Values

Datetime FieldValid Values for Datetime Data TypesValid Values for Interval Data Types

-4712 to 9999 (excluding year 0)

Any positive or negative integer

01 to 12

0 to 11

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

Any positive or negative integer

00 to 23

0 to 23

00 to 59

0 to 59

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

0 to 59.9(n), where 9(n) is the precision of interval fractional seconds

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

Not applicable

(See note at end of table)

00 to 59. Not applicable for or .

Not applicable

Query the column of the data dictionary view. Not applicable for or .

For a complete listing of all time zone regions, refer to Oracle Database Globalization Support Guide.

Not applicable

Query the column of the data dictionary view. Not applicable for or .

Not applicable


Note: and are specified together and interpreted as an entity in the format +|- hh:mm, with values ranging from -12:59 to +14:00.

Datetime Format Templates

A datetime format template is a template that describes the format of datetime data stored in a character string. A format model does not change the internal representation of the value in the database. When you convert a character string into a date, a format model determines how Oracle Database interprets the string. In OLAP DML statements, you can use a format model as an argument of the and functions to specify:

  • The format for Oracle to use to return a value from the database

  • The format for a value you have specified for Oracle to store in the database

You can use datetime format templates in the following functions:

  • In the datetime functions to translate a character value that is in a format other than the default format into a datetime value. (The * datetime functions are , , , , , and .)

  • In the function to translate a datetime value that is in a format other than the default format into a string (for example, to print the date from an application)

The default datetime formats are specified either explicitly with the initialization parameter or implicitly with the initialization parameter . You can change the default datetime formats for your session with the statement. You can override this default and specify a datetime format for use with a particular OLAP DML object by using the DATE_FORMAT command to add a datetime format to the definition of the object.

String-to-Date Conversion Rules

The following additional formatting rules apply when converting string values to datetime values (unless you have used the or modifiers in the format model to control exact format checking):

  • You can omit punctuation included in the format string from the date string if all the digits of the numeric format elements, including leading zeros, are specified. In other words, specify 02 and not 2 for two-digit format elements such as MM, DD, and YY.

  • You can omit time fields found at the end of a format string from the date string.

  • If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 2-8, "Oracle Format Matching".

Table 2-8 Oracle Format Matching

Original Format ElementAdditional Format Elements to Try instead Of the Original
'MM'

and


DATETIME Data Type

The OLAP DML DATETIME data type corresponds to the SQL data type. As such, the format and language of values are controlled by the settings of the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE options. The data type is supported by Oracle Database standard libraries and operates the same way in the OLAP DML as it does the data type in SQL.

Note:

The Oracle OLAP DML has a date data type named that does not correspond to the SQL data type of that name. The OLAP DML DATE data type stores only date values (no time values) and is therefore sometimes referred to as the DATE-only data type. The DATEORDER, DATEFORMAT, and MONTHNAMES options, which control the formatting of values, have no effect on values. However, and values can be used interchangeably in most DML statements.

You can specify a value as a string literal, or you can convert a character or numeric value to a date value with the function.

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

DATETIME '1998-12-25'

The ANSI date literal contains no time portion, and must be specified in exactly this format ('').

Alternatively you can specify a DATETIME value us the TO_DATE function and include, as in the following example:

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

The default date format template for an Oracle value is specified by the initialization parameter . 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 datetime format into datetime values when they are used in datetime expressions.

If you specify a datetime 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 datetime value without a date, then the default date is the first day of the current month.

Values of always contain both the date and time fields. Therefore, if you use values in an expression, you must either specify the time field in your query or ensure that the time fields in the values are set to midnight. Otherwise, Oracle may not return the results you expect. You can use the (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. However, if the expression contains DATETIME values other than midnight, then you must filter out the time fields in the query to get the correct result.

The date function returns the current system date and time. The function returns the current session date. For information on , the datetime functions, and the default date format, see "Datetime functions" and the DATE_FORMAT command.

TIMESTAMP Data Type

The data type is an extension of the data type. It stores the year, month, and day of the data type, plus hour, minute, and second values. This data type is useful for storing precise time values.

SHOW SYSDATE 26-JUL-06 DEFINE mytimestamp VARIABLE TIMESTAMP mytimestamp = SYSDATE COLWIDTH = 30 REPORT mytimestamp MYTIMESTAMP ------------------------------ 26-JUL-06 10.44.42 AM

The data type stores year, month, day, hour, minute, and second, and fractional second values. When you specify 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_TZ Data Type

corresponds to the SQL data type. It is a variant of that includes a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This data type is useful for collecting and evaluating date information across geographic regions.

Oracle from the public domain information available at . Oracle time zone data may not reflect the most recent data available at this site.

The data type is a variant of that includes a time zone offset. When you specify 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_TZ values are considered identical if they represent the same instant in UTC, regardless of the offsets stored in the data. For example,

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

equals

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

That is, 8:00 a.m. Pacific Standard Time equals 11:00 a.m. Eastern Standard Time.

You can replace the UTC offset with the (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 and a corresponding format element. The following example ensures that the preceding example returns 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.

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

TIMESTAMP_LTZ Data Type

corresponds to the SQL data type. It is another variant of that includes a time zone offset in its value. It differs from in that data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When a user retrieves the data, Oracle returns it in the user's local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time—formerly Greenwich Mean Time). This data type is useful for displaying date information in the time zone of the client system in a two-tier application.

Oracle time zone data is derived from the public domain information available at . Oracle time zone data may not reflect the most recent data available at this site.

The data type differs from in that data stored in the database is normalized to the database time zone. The time zone offset is not stored as part of the column data. There is no literal for . Rather, you represent values of this data type using any of the other valid datetime literals. The table that follows shows some formats you can use to add a value into object, along with the corresponding value returned by an OLAP DML statement such as a SHOW command.

Value Specified When Adding ValueValue Returned
19-FEB-2004.00.00.000000 AM
19-FEB-04 02.54.36.497659 PM
19-FEB-04 12.00.00.000000 AM
19-FEB-04 02.55.29.000000 PM
19-FEB-04 12.00.00.000000 AM
19-FEB-04 08.00.00.000000 AM

Notice that if the value specified does not include a time component (either explicitly or implicitly), then the value returned defaults to midnight.

YMINTERVAL Data Type

corresponds to the SQL data type. It stores a period of time using the and datetime fields. This data type is useful for representing the difference between two datetime values when only the year and month values are significant.

Specify interval literals using the following syntax.

INTERVAL 'integer [- integer ]' YEAR|MONTH [(precision) ] [TO YEAR | MONTH ]

where

  • specifies integer values for the leading and optional trailing field of the literal. If the leading field is and the trailing field is , then the range of integer values for the month field is 0 to 11.

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

If you specify a trailing field, it must be less significant than the leading field. For example, '' is not valid.

The following literal indicates an interval of 123 years, 2 months:

INTERVAL '123-2' YEAR(3) TO MONTH

Examples of the other forms of the literal follow, including some abbreviated versions:

Form of Interval LiteralInterpretation
An interval of 123 years, 2 months. You must specify the leading field precision if it is greater than the default of 2 digits.
An interval of 123 years 0 months.
An interval of 300 months.
Maps to and indicates 4 years.
Maps to and indicates 50 months or 4 years 2 months.
Returns an error, because the default precision is 2, and '123' has 3 digits.

You can add or subtract one literal to or from another to yield another literal. For example:

INTERVAL '5-3' YEAR TO MONTH + INTERVAL'20' MONTH = INTERVAL '6-11' YEAR TO MONTH

DSINTERVAL Data Type

DSINTERVAL corresponds to the SQL data type. It stores a period of time in terms of days, hours, minutes, and seconds. This data type is useful for representing the precise difference between two datetime values.

Specify interval literals using the following syntax.

INTERVAL 'integer|integer time_expr|time_expr

DAY|HOUR|MINUTE [(leading_precision)] | SECOND [leading_precision[, fractional_seconds_precision ])]

[ TO DAY|HOUR|MINUTE|SECOND [(fractional_seconds_precision) ]]

where

  • specifies the number of days. If this value contains more digits than the number specified by the leading precision, then Oracle returns an error.

  • specifies a time in the format or or , where specifies the fractional part of a second. If contains more digits than the number specified by , then is rounded to the number of digits specified by the value. You can specify following an integer and a space only if the leading field is .

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

  • is the number of digits in the fractional part of the datetime field. Accepted values are 1 to 9. The default is 6.

If you specify a trailing field, it must be less significant than the leading field. For example, is not valid. Because of this restriction, if is the leading field, the interval literal cannot have any trailing field.

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

  • : 0 to 23

  • : 0 to 59

  • : 0 to 59.999999999

Examples of the various forms of literals follow, including some abbreviated versions:

Form of Interval LiteralInterpretation
4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.
4 days, 5 hours and 12 minutes.
400 days 5 hours.
400 days.
11 hours, 12 minutes, and 10.2222222 seconds.
11 hours and 20 minutes.
10 hours.
10 minutes 22 seconds.
10 minutes.
4 days.
25 hours.
40 minutes.
120 hours.
30.1235 seconds. The fractional second '12345' is rounded to '1235' because the precision is 4.

You can add or subtract one interval literal from another literal. For example.

INTERVAL'20' DAY - INTERVAL'240' HOUR = INTERVAL'10-0' DAY TO SECOND

Boolean Data Type

A data type enables you to represent logical values. In code, values are represented by values for "no" and "yes" (in any combination of uppercase and lowercase characters). The actual values that are recognized in your version of Oracle OLAP are determined by the language identified by the NLS_LANGUAGE option. You can use the read-only NOSPELL and YESSPELL options to obtain the values represent values. In English language code, you can represent values, using:

  • , ,

  • , ,

Working with expressions is discussed in "Boolean Expressions".

RAW Data Type

The data type stores data that is not to be interpreted (that is, not explicitly converted when moving data between different systems) by Oracle Database. The data type is intended for binary data or byte strings.

The syntax for specifying data is as follows:

RAW (size)

where you must specify a size up to the maximum of 2000 bytes

is a variable-length data type, however Oracle Net (which connects user sessions to the instance) and Oracle Database utilities do not perform character conversion when transmitting data.

When Oracle automatically converts data to and from text data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits is displayed and entered as .

Row Identifier Data Types

The row identifier data types are used to store an address of a row in a relational table. The OLAP DML supports two different data types that you can use to copy this data from a relational table into objects in an analytic workspace:

ROWID Data Type

You can examine a row address of a relational table by querying the pseudocolumn ROWID. Values of this pseudocolumn are strings representing the address of each row. These strings have the data type ROWID.

Note:

Although you can create relational tables and clusters that contain actual columns having the ROWID data type. Oracle Database does not guarantee that the values of such columns are valid rowids

The extended ROWID data type stored in a user column includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. You can retrieve the data object number from the data dictionary views USER_OBJECTS, DBA_OBJECTS, and ALL_OBJECTS. Objects that share the same segment (clustered tables in the same cluster, for example) have the same object number.

Extended rowids are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, and the plus sign (+) and forward slash (/). Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid as well as information specific to extended rowids.

UROWID Data Type

The rows of some relational tables have addresses that are not physical or permanent or were not generated by Oracle Database. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Rowids of foreign tables (such as DB2 tables accessed through a gateway) are not standard Oracle rowids.

Oracle uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids. Both types of urowid are stored in the ROWID pseudocolumn (as are the physical rowids of heap-organized tables).

Oracle creates logical rowids based on the primary key of the table. The logical rowids do not change if the primary key does not change. The ROWID pseudocolumn of an index-organized table has a data type of UROWID. You can access this pseudocolumn as you would the ROWID pseudocolumn of a heap-organized table (that is, using a SELECT ... ROWID statement). To store the rowids of an index-organized table, then define a column of type UROWID for the table and retrieve the value of the ROWID pseudocolumn into that column.

See also:

UROWID in Oracle Database SQL Language Reference.

Converting from One Data Type to Another

In many cases, Oracle OLAP performs automatic data type conversion for you as discussed in "Automatic Conversion of Textual Data Types" and "Automatic Conversion of Numeric Data Types". Additionally there are a number of OLAP DML functions that you can use to convert values from one data type to another.

Automatic Conversion of Textual Data Types

Oracle OLAP automatically converts values to when they are specified as arguments to OLAP DML statements. This can result in data loss when the values cannot be represented in the database character set

Automatic Conversion of Numeric Data Types

Oracle OLAP automatically converts variables and variables (with a fixed width of 1 byte to (with a width of 4 bytes) for calculations. When you calculate a total of variables, then you can obtain and report a result greater than 32,767 or less than -32,768. When you calculate a total of 1-byte variables, then you can obtain and report a result greater than 127 or less than -128. However, when you try to assign the result to a variable or a 1-byte variable respectively, then the variable is set to .

Oracle OLAP automatically converts numeric data types according to the following rules:

  • When you use a value with the or data type in an expression, then the value is converted to its long counterpart before using it. See "Boolean Expressions" for information about problems that can occur when you mix and data types in a comparison expression.

  • When you save the results of a calculation as a value with the data type, then NA is stored when the result is outside the range of a (-32768 to 32767).

  • When you assign the value of a expression to an object with the data type, then the value is rounded before storing or using it.

    Note:

    When a value is outside the range of an , then an NA is stored.
  • When you use a decimal value where a value with the data type is required, then the value is rounded before storing or using it.

    Note:

    When the DECIMAL value is outside the range of an INTEGER, then an NA is stored.
  • When you assign the value of a decimal expression to a variable with the data type, then only the first 7 significant digits are stored.

  • When you combine values with other numeric data types, then all values are converted to .

When these conversion are not what you want, then you can use the CONVERT, TO_CHAR, TO_NCHAR, TO_NUMBER, or TO_DATE functions to get different results.

One thought on “Clusters In Pl/Sql What Is The Assignment Operator

Leave a comment

L'indirizzo email non verrĂ  pubblicato. I campi obbligatori sono contrassegnati *