ÿþ<html> <head> <meta http-equiv="Content-Language" content="en-us"> <meta http-equiv="Content-Type" content="text/html; charset=windows-1252"> <title>Oracle DBA - DataTypes </title> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <link href="style.css" rel="stylesheet" type="text/css"> <META NAME="description" content="This section will Introduce SQL . Read on to know more about oracle dba. "> <META NAME="keywords" content="sql,Datatype,sql database,sql commands,sql statements,tutorial sql,sql queries,sql command,sql tables,sql training,query,subquery,sql tutorial, pl/sql tutorial,oracle dba tutorial,oracle dba "> <script type="text/javascript" language="javascript" src="includes/sniffer.js"></script><script type="text/javascript" language="javascript1.2" src="includes/custom.js"></script><script type="text/javascript" language="javascript1.2" src="includes/style.js"></script></head> <body link = "black" topmargin=0 leftmargin=0><script type="text/javascript" language="javascript1.2" src="includes/menu.js"></script> <script src="http://www.google-analytics.com/urchin.js" type="text/javascript"> </script> <script type="text/javascript"> _uacct = "UA-4671442-1"; urchinTracker(); </script> <table align=left border="1" width="100%" id="table1" height="503" cellspacing="1" cellpadding="0"> <tr> <td height="60" bgcolor="E7EFE7" colspan="3" align="Left" valign="top"> <IMG SRC="learn-oracle-header/learn-oracle.jpg" WIDTH="1000" HEIGHT="91" BORDER="0" ALT="learn-oracle"> </tr> <tr> <td width="160" align="left" valign="top" bgcolor="E7EFE7" > <font face="arial" size='2'><a href="oracle_dba_step_1.html">free <B>Oracle DBA</B> tutorial</a> <font size="2" align="right"> <a href="http://oracleonline.info/oracle_jobs.html">Oracle Jobs </a><BR> <a href="http://faq.sparklit.com/main.spark?faqID=1665">Ask A Question </a><BR> <a href="http://oracleonline.info/sql_statement_tuning.html">SQL Statement Tuning </a><br> <a href="http://oracleonline.info/backup_recovery_dba.html">Backup and Recovery Concepts </a><br> <a href="http://oracleonline.info/oracle_11g_new_features.html">Oracle 11g New Features </a><br> <a href="http://oracleonline.info/oracle_e_suite_others.html">Oracle E Suite & Others </a><br> <a href="http://oracleonline.info/Oracle_data_guard_index.html">Oracle Data Guard </a><br> <a href="http://oracleonline.info/faq_for_oracle_dba.html">Oracle DBA FAQ </a><br> <script type="text/javascript"><!-- google_ad_client = "pub-4228419744604469"; google_ad_width = 160; google_ad_height = 600; google_ad_format = "160x600_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "E7EFE7"; google_color_bg = "E7EFE7"; google_color_link = "0000ff"; google_color_url = "000000"; google_color_text = "000000"; //--></script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script> <BR><BR> <B> <!--%2Chttp%3A%2F%2Fwww.sdparanormal.com%2Ff%2FParanormal_News_Feed.xml --> <!-- RSS News End --> </td> <td align="left" valign="top" bgcolor="E7EFE7" bordercolor="#99CCFF"style="border-style: solid; border-width: 1px; padding-left: 8px; padding-right: 4px; padding-top: 1px; padding-bottom: 1px> <div id="wrapper"> <div id="body"> <div> <div> <div> <div class="inner"> <div class="indent"> <p ></B> <font face="tahoma" size="2" size="2" color="#435C7D"> <BR><BR><BR> <h1 align="center"> DataTypes </h1><BR> <!-- <table> <tr> <td> --> <a href="introduction_sql.html">Previous Chapter </a> | <a href="literals_sql.html">Next Chapter </a> <BR><BR> </font > <!-- <IMG SRC="character-set.gif" WIDTH="599" HEIGHT="410" BORDER="0" ALT=""><BR><BR> --> <!-- </td> <td> --> <script type="text/javascript"><!-- google_ad_client = "pub-4228419744604469"; google_ad_width = 728; google_ad_height = 90; google_ad_format = "728x90_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "E7EFE7"; google_color_bg = "E7EFE7"; google_color_link = "000000"; google_color_url = "000000"; google_color_text = "000000"; //--></script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script><p> <!-- </td> </tr> </table> --> <font face="tahoma" size="2" size="2"> <div class="IND"> <!-- End Header --> <a name="45443"></a> <!--TOC=h1-"45443"--> <!--/TOC=h1--> <a name="45445"></a> <p class="BP"> Each value manipulated by Oracle has a <strong class="Bold">datatype</strong>. A value's datatype 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 <code>NUMBER</code> datatype, but not values of <code>RAW</code> datatype. </p> <a name="45446"></a> <p class="BP"> 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, <code>DATE</code> 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 column's datatype. For example, if you insert '01-JAN-98' into a <code>DATE</code> column, Oracle treats the '01-JAN-98' character string as a <code>DATE</code> value after verifying that it translates to a valid date. </p> <a name="45447"></a> <p class="BP"> Oracle provides a number of built-in datatypes as well as several categories for user-defined types. The syntax of Oracle datatypes appears in the diagrams that follow. The text of this section is divided into the following sections: </p> <a name="54201"></a> <!--TOC=h2-"54201"--> <h3 class="H2"><font color="#000000" face="tahoma" size="2"> Oracle Built-in Datatypes</font></h3> <!--/TOC=h2--> <a name="54202"></a> <p class="BP"> Table&nbsp;2-1 summarizes Oracle built-in datatypes. </p> <a name="49199"> </a><h5 class="TT"><a name="49199"><font face="Helvetica,Arial,sans-serif"><em> </em></font></a><font face="Helvetica,Arial,sans-serif"><em><a name="45504"></a> <strong><font face="tahoma" size="2"><em>Table 2-1 <em class="Italic">&nbsp;</em>Built-In Datatype Summary </em></font></strong> </em></font></h5> <table class="Formal" dir="ltr" border="1" cellpadding="3" cellspacing="0" frame="hsides" rules="groups" width="100%"> <thead> <tr class="Formal"><th class="Formal" scope="col" align="left" valign="bottom"> <a name="45510"></a> <font face="tahoma" size="2"><strong>Code<sup>a</sup></strong></font>&nbsp;</th> <th class="Formal" scope="col" align="left" valign="bottom"> <a name="45512"></a> <font face="tahoma" size="2"><strong>Built-In Datatype</strong></font>&nbsp;</th> <th class="Formal" scope="col" align="left" valign="bottom"> <a name="45514"></a> <font face="tahoma" size="2"><strong>Description</strong></font>&nbsp;</th> </tr> </thead><tbody> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45516"></a> <p class="TB"><font face="tahoma" size="2"><font face="tahoma" size="2"> 1&nbsp;</p></td> <td class="Formal" size="2"> <a name="49570"></a> <p class="TB"><font face="tahoma" size="2"><font face="tahoma" size="2"> <code>VARCHAR2(</code><em><code>size</code></em><code>) [BYTE | CHAR]</code>&nbsp;</p></td> <td class="Formal" size="2"> <a name="45520"></a> <p class="TB"><font face="tahoma" size="2"><font face="tahoma" size="2"> Variable-length character string having maximum length <em><code>size</code></em> bytes or characters. Maximum <em><code>size</code></em> is 4000 bytes, and minimum is 1 byte or 1 character. You must specify <em><code>size</code></em> for <code>VARCHAR2</code>. <a name="49578"></a> </p><p class="TB"><font face="tahoma" size="2"><font face="tahoma" size="2"> <code>BYTE</code> indicates that the column will have byte length semantics; <code>CHAR</code> indicates that the column will have character semantics.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45522"></a> <p class="TB"><font face="tahoma" size="2"><font face="tahoma" size="2"> 1&nbsp;</p></td> <td class="Formal"> <a name="45524"></a> <p class="TB"><font face="tahoma" size="2"> <code>NVARCHAR2(</code><em><code>size</code></em><code>)</code>&nbsp;</p></td> <td class="Formal"> <a name="45526"></a> <p class="TB"><font face="tahoma" size="2"> Variable-length character string having maximum length <em><code>size</code></em> characters or bytes, depending on the choice of national character set. Maximum <em><code>size</code></em> is determined by the number of bytes required to store each character, with an upper limit of 4000 bytes. You must specify <em><code>size</code></em> for <code>NVARCHAR2</code>.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45529"></a> <p class="TB"><font face="tahoma" size="2"> 2&nbsp;</p></td> <td class="Formal"> <a name="45531"></a> <p class="TB"><font face="tahoma" size="2"> <code>NUMBER(</code><em><code>p,s</code></em><code>)</code>&nbsp;</p></td> <td class="Formal"> <a name="45533"></a> <p class="TB"><font face="tahoma" size="2"> Number having precision <em><code>p</code></em> and scale <em><code>s</code></em>. The precision <em><code>p</code></em> can range from 1 to 38. The scale <em><code>s</code></em> can range from -84 to 127.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45536"></a> <p class="TB"><font face="tahoma" size="2"> 8&nbsp;</p></td> <td class="Formal"> <a name="45538"></a> <p class="TB"><font face="tahoma" size="2"> <code>LONG</code>&nbsp;</p></td> <td class="Formal"> <a name="45540"></a> <p class="TB"><font face="tahoma" size="2"> Character data of variable length up to 2 gigabytes, or 2<sup>31</sup> -1 bytes.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45543"></a> <p class="TB"><font face="tahoma" size="2"> 12&nbsp;</p></td> <td class="Formal"> <a name="45545"></a> <p class="TB"><font face="tahoma" size="2"> <code>DATE</code>&nbsp;</p></td> <td class="Formal"> <a name="45547"></a> <p class="TB"><font face="tahoma" size="2"> Valid date range from January 1, 4712 BC to December 31, 9999 AD.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="47685"></a> <p class="TB"><font face="tahoma" size="2"> 180&nbsp;</p></td> <td class="Formal"> <a name="47687"></a> <p class="TB"><font face="tahoma" size="2"> <code>TIMESTAMP</code> (<em><code>fractional_seconds_precision</code></em>)&nbsp;</p></td> <td class="Formal"> <a name="47689"></a> <p class="TB"><font face="tahoma" size="2"> Year, month, and day values of date, as well as hour, minute, and second values of time, where <em><code>fractional_seconds_precision</code></em> is the number of digits in the fractional part of the <code>SECOND</code> datetime field. Accepted values of <em><code>fractional_seconds_precision</code></em> are 0 to 9. The default is 6.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="47679"></a> <p class="TB"><font face="tahoma" size="2"> 181&nbsp;</p></td> <td class="Formal"> <a name="47681"></a> <p class="TB"><font face="tahoma" size="2"> <code>TIMESTAMP</code> (<em><code>fractional_seconds_precision</code></em>) <code>WITH</code> <code>TIME</code> <code>ZONE</code>&nbsp;</p></td> <td class="Formal"> <a name="47683"></a> <p class="TB"><font face="tahoma" size="2"> All values of <code>TIMESTAMP</code> as well as time zone displacement value, where <em><code>fractional_seconds_precision</code></em> is the number of digits in the fractional part of the <code>SECOND</code> datetime field. Accepted values are 0 to 9. The default is 6.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="47671"></a> <p class="TB"><font face="tahoma" size="2"> 231&nbsp;</p></td> <td class="Formal"> <a name="47673"></a> <p class="TB"><font face="tahoma" size="2"> <code>TIMESTAMP</code> (<em><code>fractional_seconds_precision</code></em>) <code>WITH</code> <code>LOCAL</code> <code>TIME</code> <code>ZONE</code>&nbsp;</p></td> <td class="Formal"> <a name="47705"></a> <p class="TB"><font face="tahoma" size="2"> All values of <code>TIMESTAMP</code> <code>WITH</code> <code>TIME</code> <code>ZONE</code>, with the following exceptions: </p><ul class="TLB"> <li class="TLB" type="disc"><a name="47706"></a>Data is normalized to the database time zone when it is stored in the database. <p> </p></li><li class="TLB" type="disc"><a name="47677"></a>When the data is retrieved, users see the data in the session time zone. <p> </p></li></ul>&nbsp;</td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="47645"></a> <p class="TB"><font face="tahoma" size="2"> 182&nbsp;</p></td> <td class="Formal"> <a name="47647"></a> <p class="TB"><font face="tahoma" size="2"> <code>INTERVAL</code> <code>YEAR</code> (<em><code>year_precision</code></em>) <code>TO</code> <code>MONTH</code>&nbsp;</p></td> <td class="Formal"> <a name="47649"></a> <p class="TB"><font face="tahoma" size="2"> Stores a period of time in years and months, where <em><code>year_precision</code></em> is the number of digits in the <code>YEAR</code> datetime field. Accepted values are 0 to 9. The default is 2.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="47639"></a> <p class="TB"><font face="tahoma" size="2"> 183&nbsp;</p></td> <td class="Formal"> <a name="47641"></a> <p class="TB"><font face="tahoma" size="2"> <code>INTERVAL</code> <code>DAY</code> (day_<em><code>precision</code></em>) <code>TO</code> <code>SECOND</code> (<em><code>fractional_seconds_precision</code></em>)&nbsp;</p></td> <td class="Formal"> <a name="47643"></a> <p class="TB"><font face="tahoma" size="2"> Stores a period of time in days, hours, minutes, and seconds, where </p><ul class="TLB"> <li class="TLB" type="disc"><a name="55478"></a><em><code>day_precision</code></em> is the maximum number of digits in the <code>DAY</code> datetime field. Accepted values are 0 to 9. The default is 2. <p> </p></li><li class="TLB" type="disc"><a name="55479"></a><em><code>fractional_seconds_precision</code></em> is the number of digits in the fractional part of the <code>SECOND</code> field. Accepted values are 0 to 9. The default is 6. <p> </p></li></ul>&nbsp;</td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45550"></a> <p class="TB"><font face="tahoma" size="2"> 23&nbsp;</p></td> <td class="Formal"> <a name="45552"></a> <p class="TB"><font face="tahoma" size="2"> <code>RAW(</code><em><code>size</code></em><code>)</code>&nbsp;</p></td> <td class="Formal"> <a name="45554"></a> <p class="TB"><font face="tahoma" size="2"> Raw binary data of length <em><code>size</code></em> bytes. Maximum <em><code>size</code></em> is 2000 bytes. You must specify <em><code>size </code></em>for a <code>RAW</code> value.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45557"></a> <p class="TB"><font face="tahoma" size="2"> 24&nbsp;</p></td> <td class="Formal"> <a name="45559"></a> <p class="TB"><font face="tahoma" size="2"> <code>LONG RAW</code>&nbsp;</p></td> <td class="Formal"> <a name="45561"></a> <p class="TB"><font face="tahoma" size="2"> Raw binary data of variable length up to 2 gigabytes.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45564"></a> <p class="TB"><font face="tahoma" size="2"> 69&nbsp;</p></td> <td class="Formal"> <a name="45566"></a> <p class="TB"><font face="tahoma" size="2"> <code>ROWID</code>&nbsp;</p></td> <td class="Formal"> <a name="45568"></a> <p class="TB"><font face="tahoma" size="2"> Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the <code>ROWID</code> pseudocolumn.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45571"></a> <p class="TB"><font face="tahoma" size="2"> 208&nbsp;</p></td> <td class="Formal"> <a name="45573"></a> <p class="TB"><font face="tahoma" size="2"> <code>UROWID [(</code><em><code>size</code></em><code>)]</code>&nbsp;</p></td> <td class="Formal"> <a name="45575"></a> <p class="TB"><font face="tahoma" size="2"> Hexadecimal string representing the logical address of a row of an index-organized table. The optional <em><code>size</code></em> is the size of a column of type <code>UROWID</code>. The maximum size and default is 4000 bytes.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45578"></a> <p class="TB"><font face="tahoma" size="2"> 96&nbsp;</p></td> <td class="Formal"> <a name="45580"></a> <p class="TB"><font face="tahoma" size="2"> <code>CHAR(</code><em><code>size</code></em><code>)[BYTE | CHAR]</code>&nbsp;</p></td> <td class="Formal"> <a name="45582"></a> <p class="TB"><font face="tahoma" size="2"> Fixed-length character data of length <em><code>size</code></em> bytes. Maximum <em><code>size</code></em> is 2000 bytes. Default and minimum <em><code>size</code></em> is 1 byte. <a name="49537"></a> </p><p class="TB"><font face="tahoma" size="2"> <code>BYTE</code> and <code>CHAR</code> have the same semantics as for <code>VARCHAR2</code>.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45585"></a> <p class="TB"><font face="tahoma" size="2"> 96&nbsp;</p></td> <td class="Formal"> <a name="45587"></a> <p class="TB"><font face="tahoma" size="2"> <code>NCHAR(</code><em><code>size</code></em><code>)</code>&nbsp;</p></td> <td class="Formal"> <a name="45589"></a> <p class="TB"><font face="tahoma" size="2"> Fixed-length character data of length <em><code>size</code></em> characters or bytes, depending on the choice of national character set. Maximum <em><code>size</code></em> is determined by the number of bytes required to store each character, with an upper limit of 2000 bytes. Default and minimum <em><code>size</code></em> is 1 character or 1 byte, depending on the character set.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45592"></a> <p class="TB"><font face="tahoma" size="2"> 112&nbsp;</p></td> <td class="Formal"> <a name="45594"></a> <p class="TB"><font face="tahoma" size="2"> <code>CLOB</code>&nbsp;</p></td> <td class="Formal"> <a name="45596"></a> <p class="TB"><font face="tahoma" size="2"> A character large object containing single-byte characters. Both fixed-width and variable-width character sets are supported, both using the <code>CHAR</code> database character set. Maximum size is 4 gigabytes. &nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45599"></a> <p class="TB"><font face="tahoma" size="2"> 112&nbsp;</p></td> <td class="Formal"> <a name="45601"></a> <p class="TB"><font face="tahoma" size="2"> <code>NCLOB</code>&nbsp;</p></td> <td class="Formal"> <a name="45603"></a> <p class="TB"><font face="tahoma" size="2"> A character large object containing unicode characters. Both fixed-width and variable-width character sets are supported, both using the <code>NCHAR</code> database character set. Maximum size is 4 gigabytes. Stores national character set data.&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45606"></a> <p class="TB"><font face="tahoma" size="2"> 113&nbsp;</p></td> <td class="Formal"> <a name="45608"></a> <p class="TB"><font face="tahoma" size="2"> <code>BLOB</code>&nbsp;</p></td> <td class="Formal"> <a name="45610"></a> <p class="TB"><font face="tahoma" size="2"> A binary large object. Maximum size is 4 gigabytes. &nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="45613"></a> <p class="TB"><font face="tahoma" size="2"> 114&nbsp;</p></td> <td class="Formal"> <a name="45615"></a> <p class="TB"><font face="tahoma" size="2"> <code>BFILE</code>&nbsp;</p></td> <td class="Formal"> <a name="45617"></a> <p class="TB"><font face="tahoma" size="2"> 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.&nbsp;</p></td> </tr> </tbody><tfoot> <tr class="Footer" align="left" valign="top"><td class="Footer" colspan="3" rowspan="1"><a name="45619"></a> <font size="-1"><sup>a </sup>The codes listed for the datatypes are used internally by Oracle. The datatype code of a column or object attribute is returned by the <code>DUMP</code> function.</font><br> &nbsp;</td> </tr> </tfoot></table> <table class="TableNote" dir="ltr" cellpadding="3" cellspacing="0" width="100%"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> <a name="45627"></a> <h5 class="SH2W"><font face="tahoma" size="2">Character Datatypes </font></h5> <a name="45628"></a> <p class="BP"> 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 <code>NUMBER</code> columns can store only numeric values. </p> <a name="45629"></a> <p class="BP"> 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 supports both single-byte and multibyte character sets. </p> <a name="45630"></a> <p class="BP"> These datatypes are used for character data: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="45634"></a>CHAR Datatype <p> </p></li><li class="LB1" type="disc"><a name="45638"></a>NCHAR Datatype <p> </p></li><li class="LB1" type="disc"><a name="45642"></a>NVARCHAR2 Datatype <p> </p></li><li class="LB1" type="disc"><a name="45646"></a>VARCHAR2 Datatype <p> </p></li></ul> <a name="45649"></a> <!--TOC=h3-"45649"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> CHAR Datatype </font></h4> <!--/TOC=h3--> <a name="45650"></a> <p class="BP"> The <code>CHAR</code> datatype specifies a fixed-length character string. Oracle subsequently ensures that all values stored in that column have the length specified by <em><code>size</code></em>. If you insert a value that is shorter than the column length, Oracle blank-pads the value to column length. If you try to insert a value that is too long for the column, Oracle returns an error. </p> <a name="45652"></a> <p class="BP"> The default length for a <code>CHAR</code> column is 1 byte and the maximum allowed is 2000 bytes. A 1-byte string can be inserted into a <code>CHAR(10)</code> column, but the string is blank-padded to 10 bytes before it is stored. </p> <a name="55487"></a> <p class="BP"> When you create a table with a <code>CHAR</code> column, by default you supply the column length in bytes. The <code>BYTE</code> qualifier is the same as the default. If you use the <code>CHAR</code> qualifier, for example <code>CHAR</code>(<code>10</code> <code>CHAR</code>), you supply the column length in characters. A character is technically a codepoint of the database character set. Its size can range from 1 byte to 4 bytes, depending on the database character set. The <code>BYTE</code> and <code>CHAR</code> qualifiers override the semantics specified by the <code>NLS_LENGTH_SEMANTICS</code> parameter, which has a default of byte semantics. </p> <a name="45671"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="72842"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="45674"></a> <!--TOC=h3-"45674"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> NCHAR Datatype </font></h4> <!--/TOC=h3--> <a name="45675"></a> <p class="BP"> Beginning with Oracle9<em class="Italic">i</em>, the <code>NCHAR</code> datatype is redefined to be a Unicode-only datatype. When you create a table with an <code>NCHAR</code> column, you define the column length in characters. You define the national character set when you create your database. </p> <a name="45681"></a> <p class="BP"> The column's maximum length is determined by the national character set definition. Width specifications of character datatype <code>NCHAR</code> refer to the number of characters. The maximum column size allowed is 2000 bytes. </p> <a name="45682"></a> <p class="BP"> If you insert a value that is shorter than the column length, Oracle blank-pads the value to column length. You cannot insert a <code>CHAR</code> value into an <code>NCHAR</code> column, nor can you insert an <code>NCHAR</code> value into a <code>CHAR</code> column. </p> <a name="45683"></a> <p class="BP"> The following example compares the <code>col1</code> column of <code>tab1</code> with national character set string 'NCHAR literal': </p> <pre class="CE"><a name="45684"></a>SELECT translated_description from product_descriptions <a name="58542"></a> WHERE translated_name = N'LCD Monitor 11/PM'; </pre> <a name="55288"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="45687"></a> <!--TOC=h3-"45687"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> NVARCHAR2 Datatype </font></h4> <!--/TOC=h3--> <a name="45688"></a> <p class="BP"> Beginning with Oracle9<em class="Italic">i</em>, the <code>NVARCHAR2</code> datatype is redefined to be a Unicode-only datatype. When you create a table with an <code>NVARCHAR2</code> 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 column's maximum length. </p> <a name="45691"></a> <p class="BP"> The column's maximum length is determined by the national character set definition. Width specifications of character datatype <code>NVARCHAR2</code> refer to the number of characters. The maximum column size allowed is 4000 bytes. </p> <a name="55337"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="45696"></a> <!--TOC=h3-"45696"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> VARCHAR2 Datatype </font></h4> <!--/TOC=h3--> <a name="45697"></a> <p class="BP"> The <code>VARCHAR2</code> datatype specifies a variable-length character string. When you create a <code>VARCHAR2</code> 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. If you try to insert a value that exceeds the specified length, Oracle returns an error. </p> <a name="45698"></a> <p class="BP"> You must specify a maximum length for a <code>VARCHAR2</code> column. This maximum must be at least 1 byte, although the actual length of the string stored is permitted to be zero. Oracle treats zero-length strings as nulls. You can use the <code>CHAR</code> qualifier, for example <code>VARCHAR2</code>(<code>10</code> <code>CHAR</code>), to give the maximum length in characters instead of bytes. A character is technically a codepoint of the database character set. <code>CHAR</code> and <code>BYTE</code> qualifiers override the setting of the <code>NLS_LENGTH_SEMANTICS</code> parameter, which has a default of bytes. The maximum length of <code>VARCHAR2</code> data is 4000 bytes. Oracle compares <code>VARCHAR2</code> values using nonpadded comparison semantics. </p> <a name="45717"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="72858"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="55512"></a> <!--TOC=h3-"55512"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> VARCHAR Datatype </font></h4> <!--/TOC=h3--> <a name="55513"></a> <p class="BP"> The <code>VARCHAR</code> datatype is currently synonymous with the <code>VARCHAR2</code> datatype. Oracle recommends that you use <code>VARCHAR2</code> rather than <code>VARCHAR</code>. In the future, <code>VARCHAR</code> might be defined as a separate datatype used for variable-length character strings compared with different comparison semantics. </p> <a name="45723"></a> <h5 class="SH2W"><font face="tahoma" size="2">NUMBER Datatype </font></h5> <a name="45724"></a> <p class="BP"> The <code>NUMBER</code> datatype stores zero, positive, and negative fixed and floating-point numbers with magnitudes between 1.0 x 10<sup>-130</sup> and 9.9...9 x 10<sup>125</sup> (38 nines followed by 88 zeroes) with 38 digits of precision. If you specify an arithmetic expression whose value has a magnitude greater than or equal to 1.0 x 10<sup>126</sup>, Oracle returns an error. </p> <a name="45726"></a> <p class="BP"> Specify a fixed-point number using the following form: </p> <pre class="CE"><a name="45727"></a>NUMBER(p,s) <a name="45728"></a> </pre> <a name="45729"></a> <p class="BP"> where: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="47211"></a><em><code>p</code></em> is the <strong class="Bold">precision</strong>, or the total number of digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38. <p> </p></li><li class="LB1" type="disc"><a name="47226"></a><em><code>s</code></em> is the <strong class="Bold">scale</strong>, or the number of digits to the right of the decimal point. The scale can range from -84 to 127. <p> </p></li></ul> <a name="45741"></a> <p class="BP"> Specify an integer using the following form: </p> <pre class="CE"><a name="53315"></a>NUMBER(p) <a name="53347"></a> </pre> <a name="53316"></a> <p class="BP"> This represents a fixed-point number with precision p and scale 0 and is equivalent to <code>NUMBER(p,0)</code>. </p> <a name="55524"></a> <p class="BP"> Specify a floating-point number using the following form: </p> <pre class="CE"><a name="53355"></a>NUMBER <a name="53317"></a> </pre> <a name="53318"></a> <p class="BP"> The absence of precision and scale designators specifies the maximum range and precision for an Oracle number. </p> <a name="45764"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="45765"></a> <!--TOC=h3-"45765"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Scale and Precision</font></h4> <!--/TOC=h3--> <a name="45767"></a> <p class="BP"> 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, Oracle returns an error. If a value exceeds the scale, Oracle rounds it. </p> <a name="45768"></a> <p class="BP"> The following examples show how Oracle stores data using different precisions and scales. </p> <a name="45818"> </a><p> </p><table class="Simple" dir="ltr" width="100%"> <tbody><tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45777"></a> <p class="TS"> 7456123.89</p></td> <td class="Simple"> <a name="45779"></a> <p class="TS"> <code>NUMBER</code></p></td> <td class="Simple"> <a name="45781"></a> <p class="TS"> 7456123.89</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45783"></a> <p class="TS"> 7456123.89</p></td> <td class="Simple"> <a name="45785"></a> <p class="TS"> <code>NUMBER(9)</code></p></td> <td class="Simple"> <a name="45787"></a> <p class="TS"> 7456124</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45789"></a> <p class="TS"> 7456123.89</p></td> <td class="Simple"> <a name="45791"></a> <p class="TS"> <code>NUMBER(9,2)</code></p></td> <td class="Simple"> <a name="45793"></a> <p class="TS"> 7456123.89</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45795"></a> <p class="TS"> 7456123.89</p></td> <td class="Simple"> <a name="45797"></a> <p class="TS"> <code>NUMBER(9,1)</code></p></td> <td class="Simple"> <a name="45799"></a> <p class="TS"> 7456123.9</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45801"></a> <p class="TS"> 7456123.89</p></td> <td class="Simple"> <a name="45803"></a> <p class="TS"> <code>NUMBER(6)</code></p></td> <td class="Simple"> <a name="45805"></a> <p class="TS"> exceeds precision</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45807"></a> <p class="TS"> 7456123.89</p></td> <td class="Simple"> <a name="45809"></a> <p class="TS"> <code>NUMBER(7,-2)</code></p></td> <td class="Simple"> <a name="45811"></a> <p class="TS"> 7456100</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45813"></a> <p class="TS"> 7456123.89</p></td> <td class="Simple"> <a name="45815"></a> <p class="TS"> <code>NUMBER(7,2)</code></p></td> <td class="Simple"> <a name="45817"></a> <p class="TS"> exceeds precision</p></td> </tr> </tbody></table> <table class="TableNote" dir="ltr" width="100%"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> <a name="45820"></a> <!--TOC=h3-"45820"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Negative Scale </font></h4> <!--/TOC=h3--> <a name="45821"></a> <p class="BP">If the scale is negative, 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. </p> <a name="45823"></a> <!--TOC=h3-"45823"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Scale Greater than Precision </font></h4> <!--/TOC=h3--> <a name="45824"></a> <p class="BP">You can specify a scale that is greater than precision, although it is uncommon. In this case, the precision specifies the maximum number of digits to the right of the decimal point. As with all number datatypes, if the value exceeds the precision, Oracle returns an error message. If the value exceeds the scale, Oracle rounds the value. For example, a column defined as <code>NUMBER(4,5)</code> requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point. The following examples show the effects of a scale greater than precision: </p> <a name="45862"> </a><p> </p><table class="Simple" dir="ltr" width="100%"> <tbody><tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45827"></a> <font face="tahoma" size="2"><strong>Actual Data</strong></font></td> <td class="Simple"> <a name="45829"></a> <font face="tahoma" size="2"><strong>Specified As</strong></font></td> <td class="Simple"> <a name="45831"></a> <font face="tahoma" size="2"><strong>Stored As</strong></font></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45833"></a> <p class="TS"> .01234</p></td> <td class="Simple"> <a name="45835"></a> <p class="TS"> <code>NUMBER(4,5)</code></p></td> <td class="Simple"> <a name="45837"></a> <p class="TS"> .01234</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45839"></a> <p class="TS"> .00012</p></td> <td class="Simple"> <a name="45841"></a> <p class="TS"> <code>NUMBER(4,5)</code></p></td> <td class="Simple"> <a name="45843"></a> <p class="TS"> .00012</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45845"></a> <p class="TS"> .000127</p></td> <td class="Simple"> <a name="45847"></a> <p class="TS"> <code>NUMBER(4,5)</code></p></td> <td class="Simple"> <a name="45849"></a> <p class="TS"> .00013</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45851"></a> <p class="TS"> .0000012</p></td> <td class="Simple"> <a name="45853"></a> <p class="TS"> <code>NUMBER(2,7)</code></p></td> <td class="Simple"> <a name="45855"></a> <p class="TS"> .0000012</p></td> </tr> <tr class="Simple" align="left" valign="top"><td class="Simple"> <a name="45857"></a> <p class="TS"> .00000123</p></td> <td class="Simple"> <a name="45859"></a> <p class="TS"> <code>NUMBER(2,7)</code></p></td> <td class="Simple"> <a name="45861"></a> <p class="TS"> .0000012</p></td> </tr> </tbody></table> <table class="TableNote" dir="ltr" width="100%"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> <a name="45865"></a> <!--TOC=h3-"45865"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Floating-Point Numbers </font></h4> <!--/TOC=h3--> <a name="45866"></a> <p class="BP">Oracle lets you specify floating-point numbers, which 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 (e.g. 1.777 e<sup>-20</sup>). 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. </p> <a name="45870"></a> <p class="BP"> You can specify floating-point numbers with the range of values discussed in "NUMBER Datatype"<a href="file:///C:/Surbhi/Oracle%20Documenation/DOC/server.901/a90125/sql_elements2.htm#45723"></a>. The format is defined in "Number Literals<a href="file:///C:/Surbhi/Oracle%20Documenation/DOC/server.901/a90125/sql_elements3a.htm#3411"></a>. Oracle also supports the ANSI datatype <code>FLOAT</code>. You can specify this datatype using one of these syntactic forms: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="47277"></a><code>FLOAT</code> specifies a floating-point number with decimal precision 38 or binary precision 126. <p> </p></li><li class="LB1" type="disc"><a name="47290"></a><code>FLOAT(b)</code> specifies a floating-point number with binary precision <em><code>b</code></em>. The precision <em><code>b</code></em> can range from 1 to 126. To convert from binary to decimal precision, multiply <em><code>b</code></em> 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. <p> </p></li></ul> <a name="45887"></a> <!--TOC=h3-"45887"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> LONG Datatype </font></h4> <!--/TOC=h3--> <a name="45888"></a> <p class="BP"> <code>LONG</code> columns store variable-length character strings containing up to 2 gigabytes, or 2<sup>31</sup>-1 bytes. <code>LONG</code> columns have many of the characteristics of <code>VARCHAR2</code> columns. You can use <code>LONG</code> columns to store long text strings. The length of <code>LONG</code> values may be limited by the memory available on your computer. </p> <a name="45898"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="67991"></a> <p class="BP"> <strong class="Bold"></strong>You can reference <code>LONG</code> columns in SQL statements in these places: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="67992"></a><code>SELECT</code> lists <p> </p></li><li class="LB1" type="disc"><a name="67993"></a><code>SET</code> clauses of <code>UPDATE</code> statements <p> </p></li><li class="LB1" type="disc"><a name="67994"></a><code>VALUES</code> clauses of <code>INSERT</code> statements <p> </p></li></ul> <a name="67996"></a> <p class="BP"> The use of <code>LONG</code> values is subject to some restrictions: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="67997"></a>A table can contain only one <code>LONG</code> column. <p> </p></li><li class="LB1" type="disc"><a name="67998"></a>You cannot create an object type with a <code>LONG</code> attribute. <p> </p></li><li class="LB1" type="disc"><a name="67999"></a><code>LONG</code> columns cannot appear in <code>WHERE</code> clauses or in integrity constraints (except that they can appear in <code>NULL</code> and <code>NOT</code> <code>NULL</code> constraints). <p> </p></li><li class="LB1" type="disc"><a name="68000"></a><code>LONG</code> columns cannot be indexed. <p> </p></li><li class="LB1" type="disc"><a name="68001"></a>A stored function cannot return a <code>LONG</code> value. <p> </p></li><li class="LB1" type="disc"><a name="68002"></a>You can declare a variable or argument of a PL/SQL program unit using the <code>LONG</code> datatype. However, you cannot then call the program unit from SQL. <p> </p></li><li class="LB1" type="disc"><a name="68003"></a>Within a single SQL statement, all <code>LONG</code> columns, updated tables, and locked tables must be located on the same database. <p> </p></li><li class="LB1" type="disc"><a name="68004"></a><code>LONG</code> and <code>LONG</code> <code>RAW</code> columns cannot be used in distributed SQL statements and cannot be replicated. <p> </p></li><li class="LB1" type="disc"><a name="68005"></a>If a table has both <code>LONG</code> and LOB columns, you cannot bind more than 4000 bytes of data to both the <code>LONG</code> and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the <code>LONG</code> or the LOB column. <p> </p></li><li class="LB1" type="disc"><a name="68007"></a>A table with <code>LONG</code> columns cannot be stored in a tablespace with automatic segment-space management. <p> </p></li></ul> <a name="68008"></a> <p class="BP"> <code>LONG</code> columns cannot appear in certain parts of SQL statements: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="68009"></a><code>GROUP</code> <code>BY</code> clauses, <code>ORDER</code> <code>BY</code> clauses, or <code>CONNECT</code> <code>BY</code> clauses or with the <code>DISTINCT</code> operator in <code>SELECT</code> statements <p> </p></li><li class="LB1" type="disc"><a name="68010"></a>The <code>UNIQUE</code> operator of a <code>SELECT</code> statement <p> </p></li><li class="LB1" type="disc"><a name="68011"></a>The column list of a <code>CREATE</code> <code>CLUSTER</code> statement <p> </p></li><li class="LB1" type="disc"><a name="68012"></a>The <code>CLUSTER</code> clause of a <code>CREATE</code> <code>MATERIALIZED</code> <code>VIEW</code> statement <p> </p></li><li class="LB1" type="disc"><a name="68013"></a>SQL built-in functions, expressions, or conditions <p> </p></li><li class="LB1" type="disc"><a name="68014"></a><code>SELECT</code> lists of queries containing <code>GROUP</code> <code>BY</code> clauses <p> </p></li><li class="LB1" type="disc"><a name="68015"></a><code>SELECT</code> lists of subqueries or queries combined by the <code>UNION</code>, <code>INTERSECT</code>, or <code>MINUS</code> set operators <p> </p></li><li class="LB1" type="disc"><a name="68016"></a><code>SELECT</code> lists of <code>CREATE</code> <code>TABLE</code> ... <code>AS</code> <code>SELECT</code> statements <p> </p></li><li class="LB1" type="disc"><a name="68017"></a><code>ALTER</code> <code>TABLE</code> ... <code>MOVE</code> statements <p> </p></li><li class="LB1" type="disc"><a name="68018"></a><code>SELECT</code> lists in subqueries in <code>INSERT</code> statements <p> </p></li></ul> <a name="68020"></a> <p class="BP"> Triggers can use the <code>LONG</code> datatype in the following manner: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="68021"></a>A SQL statement within a trigger can insert data into a <code>LONG</code> column. <p> </p></li><li class="LB1" type="disc"><a name="68022"></a>If data from a <code>LONG</code> column can be converted to a constrained datatype (such as <code>CHAR</code> and <code>VARCHAR2</code>), a <code>LONG</code> column can be referenced in a SQL statement within a trigger. <p> </p></li><li class="LB1" type="disc"><a name="68023"></a>Variables in triggers cannot be declared using the <code>LONG</code> datatype. <p> </p></li><li class="LB1" type="disc"><a name="68024"></a>:<code>NEW</code> and :<code>OLD</code> cannot be used with <code>LONG</code> columns. <p> </p></li></ul> <a name="68025"></a> <p class="BP"> You can use the Oracle Call Interface functions to retrieve a portion of a <code>LONG</code> value from the database. </p> <a name="68035"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="47732"></a> <h5 class="SH2W"><font face="tahoma" size="2">Datetime and Interval Datatypes </font></h5> <a name="47733"></a> <p class="BP"> The datetime datatypes are <code>DATE</code>, <code>TIMESTAMP</code>, <code>TIMESTAMP</code> <code>WITH</code> <code>TIME</code> <code>ZONE</code> and <code>TIMESTAMP</code> <code>WITH</code> <code>LOCAL</code> <code>TIME</code> <code>ZONE</code>. Values of datetime datatypes are sometimes called "datetimes". The interval datatypes are <code>INTERVAL</code> <code>YEAR</code> <code>TO</code> <code>MONTH</code> and <code>INTERVAL</code> <code>DAY</code> <code>TO</code> <code>SECOND</code>. Values of interval datatypes are sometimes called "intervals". </p> <a name="47734"></a> <p class="BP">Both datetimes and intervals are made up of fields. The values of these fields determine the value of the datatype. The table that follows lists the datetime fields and their possible values for datetimes and intervals. </p> <a name="47790"> </a><p> </p><table class="Informal" dir="ltr" border="1" cellpadding="3" cellspacing="0" frame="hsides" rules="groups" width="100%"> <thead> <tr class="Informal"><th class="Informal" scope="col" align="left" valign="bottom"> <a name="47737"></a> <font face="tahoma" size="2"><strong>Datetime Field</strong></font>&nbsp;</th> <th class="Informal" scope="col" align="left" valign="bottom"> <a name="47739"></a> <font face="tahoma" size="2"><strong>Valid Values for Datetime</strong></font>&nbsp;</th> <th class="Informal" scope="col" align="left" valign="bottom"> <a name="47741"></a> <font face="tahoma" size="2"><strong>Valid Values for INTERVAL</strong></font>&nbsp;</th> </tr> </thead><tbody> <tr class="Informal" align="left" valign="top"><td class="Informal"> <a name="47743"></a> <p class="TB"><font face="tahoma" size="2"> <code>YEAR</code>&nbsp;</p></td> <td class="Informal"> <a name="47745"></a> <p class="TB"><font face="tahoma" size="2"> -4712 to 9999 (excluding year 0)&nbsp;</p></td> <td class="Informal"> <a name="47747"></a> <p class="TB"><font face="tahoma" size="2"> Any positive or negative integer &nbsp;</p></td> </tr> <tr class="Informal" align="left" valign="top"><td class="Informal"> <a name="47749"></a> <p class="TB"><font face="tahoma" size="2"> <code>MONTH</code>&nbsp;</p></td> <td class="Informal"> <a name="47751"></a> <p class="TB"><font face="tahoma" size="2"> 01 to 12&nbsp;</p></td> <td class="Informal"> <a name="47753"></a> <p class="TB"><font face="tahoma" size="2"> 0 to 11 &nbsp;</p></td> </tr> <tr class="Informal" align="left" valign="top"><td class="Informal"> <a name="47755"></a> <p class="TB"><font face="tahoma" size="2"> <code>DAY</code>&nbsp;</p></td> <td class="Informal"> <a name="47757"></a> <p class="TB"><font face="tahoma" size="2"> 01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the current NLS calendar)&nbsp;</p></td> <td class="Informal"> <a name="47759"></a> <p class="TB"><font face="tahoma" size="2"> Any positive or negative integer&nbsp;</p></td> </tr> <tr class="Informal" align="left" valign="top"><td class="Informal"> <a name="47761"></a> <p class="TB"><font face="tahoma" size="2"> <code>HOUR</code>&nbsp;</p></td> <td class="Informal"> <a name="47763"></a> <p class="TB"><font face="tahoma" size="2"> 00 to 23&nbsp;</p></td> <td class="Informal"> <a name="47765"></a> <p class="TB"><font face="tahoma" size="2"> 0 to 23 &nbsp;</p></td> </tr> <tr class="Informal" align="left" valign="top"><td class="Informal"> <a name="47767"></a> <p class="TB"><font face="tahoma" size="2"> <code>MINUTE</code>&nbsp;</p></td> <td class="Informal"> <a name="47769"></a> <p class="TB"><font face="tahoma" size="2"> 00 to 59&nbsp;</p></td> <td class="Informal"> <a name="47771"></a> <p class="TB"><font face="tahoma" size="2"> 0 to 59 &nbsp;</p></td> </tr> <tr class="Informal" align="left" valign="top"><td class="Informal"> <a name="47773"></a> <p class="TB"><font face="tahoma" size="2"> <code>SECOND</code>&nbsp;</p></td> <td class="Informal"> <a name="47775"></a> <p class="TB"><font face="tahoma" size="2"> 00 to 59.9(n), where "9(n)" is the precision of time fractional seconds&nbsp;</p></td> <td class="Informal"> <a name="47777"></a> <p class="TB"><font face="tahoma" size="2"> 0 to 59.9(n), where "9(n)" is the precision of interval fractional seconds&nbsp;</p></td> </tr> <tr class="Informal" align="left" valign="top"><td class="Informal"> <a name="47779"></a> <p class="TB"><font face="tahoma" size="2"> <code>TIMEZONE_HOUR</code>&nbsp;</p></td> <td class="Informal"> <a name="47781"></a> <p class="TB"><font face="tahoma" size="2"> -12 to 13 (This range accommodates daylight savings time changes.)&nbsp;</p></td> <td class="Informal"> <a name="47783"></a> <p class="TB"><font face="tahoma" size="2"> Not applicable&nbsp;</p></td> </tr> <tr class="Informal" align="left" valign="top"><td class="Informal"> <a name="47785"></a> <p class="TB"><font face="tahoma" size="2"> <code>TIMEZONE_MINUTE</code>&nbsp;</p></td> <td class="Informal"> <a name="47787"></a> <p class="TB"><font face="tahoma" size="2"> 00 to 59&nbsp;</p></td> <td class="Informal"> <a name="47789"></a> <p class="TB"><font face="tahoma" size="2"> Not applicable&nbsp;</p></td> </tr> </tbody></table> <table class="TableNote" dir="ltr" cellpadding="3" cellspacing="0" width="100%"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> <a name="67294"><strong class="Bold"></strong> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="47791"></a> <!--TOC=h3-"47791"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> DATE Datatype </font></h4> <!--/TOC=h3--> <a name="47792"></a> <p class="BP"> The <code>DATE</code> datatype stores date and time information. Although date and time information can be represented in both character and number datatypes, the <code>DATE</code> datatype has special associated properties. For each <code>DATE</code> value, Oracle stores the following information: century, year, month, date, hour, minute, and second. </p> <a name="47793"></a> <p class="BP"> You can specify a date value as a literal, or you can convert a character or numeric value to a date value with the <code>TO_DATE</code> function. To specify a date as a literal, you must use the Gregorian calendar. You can specify an ANSI date literal, as shown in this example: </p> <pre class="CE"><a name="47794"></a>DATE '1998-12-25' <a name="47795"></a> </pre> <a name="47796"></a> <p class="BP">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 literal, as in the following example: </p> <pre class="CE"><a name="47797"></a>TO_DATE('98-DEC-25:17:30','YY-MON-DD:HH24:MI') <a name="47798"></a> </pre> <a name="47799"></a> <p class="BP"> The default date format for an Oracle date literal is specified by the initialization parameter <code>NLS_DATE_FORMAT</code>. 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. </p> <a name="47800"></a> <p class="BP">Oracle automatically converts character values that are in the default date format into date values when they are used in date expressions. </p> <a name="47801"></a> <p class="BP"> If you specify a date value without a time component, the default time is 12:00:00 <em class="SC">am</em> (midnight). If you specify a date value without a date, the default date is the first day of the current month. </p> <a name="53289"></a> <p class="BP"> Oracle <code>DATE</code> columns always contain both the date and time fields. If your queries use a date format without a time portion, you must ensure that the time fields in the <code>DATE</code> column are set to zero (that is, midnight). Otherwise, Oracle may not return the query results you expect. Here are some examples that assume a table <code>my_table</code> with a number column <code>row_num</code> and a <code>DATE</code> column <code>datecol</code>: </p> <pre class="CE"><a name="53534"></a>INSERT INTO my_table VALUES (1, SYSDATE); <a name="53540"></a>INSERT INTO my_table VALUES (2, TRUNC(SYSDATE)); <a name="53544"></a> <a name="53690"></a>SELECT * FROM my_table; <a name="53546"></a> <a name="53682"></a> ROW_NUM DATECOL <a name="53608"></a>---------- --------- <a name="53609"></a> 1 04-OCT-00 <a name="53610"></a> 2 04-OCT-00 <a name="53550"></a> <a name="53551"></a>SELECT * FROM my_table <a name="53552"></a> WHERE datecol = TO_DATE('04-OCT-00','DD-MON-YY'); <a name="53598"></a> <a name="53689"></a> ROW_NUM DATECOL <a name="53615"></a>---------- --------- <a name="53616"></a> 2 04-OCT-00 <a name="53623"></a> </pre> <a name="53645"></a> <p class="BP"> If you know that the time fields of your <code>DATE</code> column are set to zero, then you can query your <code>DATE</code> column as shown in the second example above, or by using the <code>DATE</code> literal: </p> <pre class="CE"><a name="53664"></a>SELECT * FROM my_table WHERE datecol = DATE '2000-10-04'; <a name="53651"></a> </pre> <a name="53652"></a> <p class="BP"> However, if the <code>DATE</code> column contains nonzero time fields, then you must filter out the time fields in the query to get the correct result. For example: </p> <pre class="CE"><a name="53653"></a>SELECT * FROM my_table WHERE TRUNC(datecol) = DATE'2000-10-04'; <a name="53654"></a> </pre> <a name="53655"></a> <p class="BP"> Oracle applies the <code>TRUNC</code> function to each row in the query, so performance is better if you ensure the zero value of the time fields in your data. To ensure that the time fields are set to zero, use one of the following methods during inserts and updates: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="53479"></a>Use the <code>TO_DATE</code> function to mask out the time fields: <p> </p><pre class="CE1"><a name="53480"></a>INSERT INTO my_table VALUES <a name="67498"></a> (3, TO_DATE('4-APR-2000','DD-MON-YYYY')); <a name="53491"></a> </pre> </li><li class="LB1" type="disc"><a name="53481"></a>Use the <code>DATE</code> literal: <p> </p><pre class="CE1"><a name="53488"></a>INSERT INTO my_table VALUES (4, '04-OCT-00'); <a name="53492"></a> </pre> </li><li class="LB1" type="disc"><a name="53489"></a>Use the <code>TRUNC</code> function: <p> </p><pre class="CE1"><a name="53490"></a>INSERT INTO my_table VALUES (5, TRUNC(SYSDATE)); <a name="53494"></a> </pre> </li></ul> <a name="47802"></a> <p class="BP"> The date function <code>SYSDATE</code> returns the current system date and time. The function <code>CURRENT_DATE</code> returns the current session date. </p> <a name="47806"></a> <!--TOC=h4-"47806"--> <h5 class="H4"><font color="#000000" face="tahoma" size="2"> Date Arithmetic</font></h5> <!--/TOC=h4--> <a name="47809"></a> <p class="BP"> You can add and subtract number constants as well as other dates from dates. Oracle interprets number constants in arithmetic date expressions as numbers of days. For example, <code>SYSDATE</code> + 1 is tomorrow. <code>SYSDATE</code> - 7 is one week ago. <code>SYSDATE</code> + (10/1440) is ten minutes from now. Subtracting the <code>hiredate</code> column of the sample table <code>employees</code> from <code>SYSDATE</code> returns the number of days since each employee was hired. You cannot multiply or divide <code>DATE</code> values. </p> <a name="47810"></a> <p class="BP"> Oracle provides functions for many common date operations. For example, the <code>ADD_MONTHS</code> function lets you add or subtract months from a date. The <code>MONTHS_BETWEEN</code> function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month. </p> <a name="47811"></a> <p class="BP">Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours. </p> <a name="47828"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="47829"></a> <!--TOC=h4-"47829"--> <h5 class="H4"><font color="#000000" face="tahoma" size="2"> Using Julian Dates</font></h5> <!--/TOC=h4--> <a name="47832"></a> <p class="BP"> A Julian date is the number of days since January 1, 4712 <em class="SC">bc</em>. Julian dates allow continuous dating from a common reference. You can use the date format model "J" with date functions <code>TO_DATE</code> and <code>TO_CHAR</code> to convert between Oracle <code>DATE</code> values and their Julian equivalents. </p> <a name="47833"></a> <h5 class="SH3"><font face="tahoma" size="2">Example</font></h5> <a name="58659"></a> <p class="BP"> This statement returns the Julian equivalent of January 1, 1997: </p> <pre class="CE"><a name="58660"></a>SELECT TO_CHAR(TO_DATE('01-01-1997', 'MM-DD-YYYY'),'J') <a name="58661"></a> FROM DUAL; <a name="58662"></a> <a name="58663"></a>TO_CHAR <a name="47839"></a>-------- <a name="47840"></a>2450450 <a name="47841"></a> </pre> <a name="47847"></a> <p class="BP"> </p> <a name="47861"></a> <!--TOC=h3-"47861"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> TIMESTAMP Datatype </font></h4> <!--/TOC=h3--> <a name="47862"></a> <p class="BP"> The <code>TIMESTAMP</code> datatype is an extension of the <code>DATE</code> datatype. It stores the year, month, and day of the <code>DATE</code> datatype, plus hour, minute, and second values. Specify the <code>TIMESTAMP</code> datatype as follows: </p> <pre class="CE"><a name="47863"></a>TIMESTAMP [ (fractional_seconds_precision)] <a name="47864"></a> </pre> <a name="47865"></a> <p class="BP"> where <em><code>fractional_seconds_precision</code></em> optionally specifies the number of digits in the fractional part of the <code>SECOND</code> datetime field and can be a number in the range 0 to 9. The default is 6. For example, you specify <code>TIMESTAMP</code> as a literal as follows: </p> <pre class="CE"><a name="47866"></a>TIMESTAMP'1997-01-31 09:26:50.124' </pre> <a name="47867"></a> <!--TOC=h3-"47867"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> TIMESTAMP WITH TIME ZONE Datatype </font></h4> <!--/TOC=h3--> <a name="47868"></a> <p class="BP"> <code>TIMESTAMP</code> <code>WITH</code> <code>TIME</code> <code>ZONE</code> is a variant of <code>TIMESTAMP</code> that includes a <strong class="Bold">time zone displacement</strong> in its value. The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). Specify the <code>TIMESTAMP</code> <code>WITH</code> <code>TIME</code> <code>ZONE</code> datatype as follows: </p> <pre class="CE"><a name="47869"></a>TIMESTAMP [ (fractional_seconds_precision) ] WITH TIME ZONE <a name="47870"></a> </pre> <a name="47871"></a> <p class="BP"> where <em><code>fractional_seconds_precision</code></em> optionally specifies the number of digits in the fractional part of the <code>SECOND</code> datetime field and can be a number in the range 0 to 9. The default is 6. For example, you specify <code>TIMESTAMP</code> <code>WITH</code> <code>TIME</code> <code>ZONE</code> as a literal as follows: </p> <pre class="CE"><a name="47872"></a>TIMESTAMP '1997-01-31 09:26:56.66 +02:00' <a name="47873"></a> </pre> <a name="47874"></a> <p class="BP"> Two <code>TIMESTAMP</code> <code>WITH</code> <code>TIME</code> <code>ZONE</code> values are considered identical if they represent the same instant in UTC, regardless of the <code>TIME</code> <code>ZONE</code> offsets stored in the data. For example, </p> <pre class="CE"><a name="47875"></a>TIMESTAMP '1999-04-15 8:00:00 -8:00' <a name="47876"></a> </pre> <a name="47877"></a> <p class="BP"> is the same as </p> <pre class="CE"><a name="47878"></a>TIMESTAMP '1999-04-15 11:00:00 -5:00' <a name="47879"></a> </pre> <a name="47880"></a> <p class="BP"> That is, 8:00 a.m. Pacific Standard Time is the same as 11:00 a.m. Eastern Standard Time. </p> <a name="51608"></a> <p class="BP"> You can replace the UTC offset with the <code>TZR</code> (time zone region) format element. For example, the following example has the same value as the preceding example: </p> <pre class="CE"><a name="51609"></a>TIMESTAMP '1999-04-15 8:00:00 US/Pacific' <a name="51610"></a> </pre> <a name="51611"></a> <p class="BP"> To eliminate the ambiguity of boundary cases when the daylight savings time switches, use both the <code>TZR</code> and a corresponding <code>TZD</code> format element. The following example ensures that the preceding example will return a daylight savings time value: </p> <pre class="CE"><a name="51616"></a>TIMESTAMP '1999-10-31 01:30:00 US/Pacific PDT' <a name="51642"></a> </pre> <a name="51643"></a> <p class="BP"> If you do not add the <code>TZD</code> format element, and the datetime value is ambiguous, then Oracle returns an error if you have the <code>ERROR_ON_OVERLAP_TIME</code> session parameter set to <code>TRUE</code>. If that parameter is set to <code>FALSE</code>, then Oracle interprets the ambiguous datetime as standard time. </p> <a name="51617"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="47886"></a> <!--TOC=h3-"47886"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> TIMESTAMP WITH LOCAL TIME ZONE Datatype </font></h4> <!--/TOC=h3--> <a name="47887"></a> <p class="BP"> <code>TIMESTAMP</code> <code>WITH</code> <code>LOCAL</code> <code>TIME</code> <code>ZONE</code> is another variant of <code>TIMESTAMP</code> that includes a <strong class="Bold">time zone displacement</strong> in its value. It differs from <code>TIMESTAMP</code> <code>WITH</code> <code>TIME</code> <code>ZONE</code> in that data stored in the database is normalized to the database time zone, and the time zone displacement is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone. The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). Specify the <code>TIMESTAMP</code> <code>WITH</code> <code>LOCAL</code> <code>TIME</code> <code>ZONE</code> datatype as follows: </p> <pre class="CE"><a name="47888"></a>TIMESTAMP [ (fractional_seconds_precision) ] WITH LOCAL TIME ZONE <a name="47889"></a> </pre> <a name="47890"></a> <p class="BP"> where <em><code>fractional_seconds_precision</code></em> optionally specifies the number of digits in the fractional part of the <code>SECOND</code> datetime field and can be a number in the range 0 to 9. The default is 6. </p> <a name="47893"></a> <p class="BP"> There is no literal for <code>TIMESTAMP</code> <code>WITH</code> <code>LOCAL</code> <code>TIME</code> <code>ZONE</code>. </p> <a name="49029"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="47895"></a> <!--TOC=h3-"47895"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> INTERVAL YEAR TO MONTH Datatype </font></h4> <!--/TOC=h3--> <a name="47896"></a> <p class="BP"> <code>INTERVAL</code> <code>YEAR</code> <code>TO</code> <code>MONTH</code> stores a period of time using the <code>YEAR</code> and <code>MONTH</code> datetime fields. Specify <code>INTERVAL</code> <code>YEAR</code> <code>TO</code> <code>MONTH</code> as follows: </p> <pre class="CE"><a name="47897"></a>INTERVAL YEAR [(year_precision)] TO MONTH <a name="47898"></a> </pre> <a name="47899"></a> <p class="BP"> where <em><code>year_precision</code></em> is the number of digits in the <code>YEAR</code> datetime field. The default value of <em><code>year_precision</code></em> is 2. </p> <a name="47944"></a> <!--TOC=h3-"47944"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> INTERVAL DAY TO SECOND Datatype </font></h4> <!--/TOC=h3--> <a name="47945"></a> <p class="BP"> <code>INTERVAL</code> <code>DAY</code> <code>TO</code> <code>SECOND</code> stores a period of time in terms of days, hours, minutes, and seconds. Specify this datatype as follows: </p> <pre class="CE"><a name="47946"></a>INTERVAL DAY [(day_precision)] <a name="47947"></a> TO SECOND [(fractional_seconds_precision)] <a name="47948"></a> </pre> <a name="47949"></a> <p class="BP"> where </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="47950"></a><em><code>day_precision</code></em> is the number of digits in the <code>DAY</code> datetime field. Accepted values are 0 to 9. The default is 2. <p> </p></li><li class="LB1" type="disc"><a name="47951"></a><em><code>fractional_seconds_precision</code></em> is the number of digits in the fractional part of the <code>SECOND</code> datetime field. Accepted values are 0 to 9. The default is 6. <p> </p></li></ul> <a name="48043"></a> <!--TOC=h3-"48043"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Datetime/Interval Arithmetic</font></h4> <!--/TOC=h3--> <a name="48044"></a> <p class="BP"> Oracle lets you derive datetime and interval value expressions. Datetime value expressions yield values of datetime datatype. Interval value expressions yield values of interval datatype. Table&nbsp;2-2 lists the operators that you can use in these expressions. </p> <a name="48146"> </a><h5 class="TT"><a name="48146"><font face="Helvetica,Arial,sans-serif"><em> </em></font></a><font face="Helvetica,Arial,sans-serif"><em><a name="48051"></a> <strong><font face="tahoma" size="2"><em>Table 2-2 Operators in Datetime/Interval Value Expressions</em></font></strong> </em></font></h5> <table class="Formal" dir="ltr" border="1" cellpadding="3" cellspacing="0" frame="hsides" rules="groups" width="100%"> <thead> <tr class="Formal"><th class="Formal" scope="col" align="left" valign="bottom"> <a name="48059"></a> <font face="tahoma" size="2"><strong>Operand 1</strong></font>&nbsp;</th> <th class="Formal" scope="col" align="left" valign="bottom"> <a name="48061"></a> <font face="tahoma" size="2"><strong>Operator</strong></font>&nbsp;</th> <th class="Formal" scope="col" align="left" valign="bottom"> <a name="48063"></a> <font face="tahoma" size="2"><strong>Operand 2 </strong></font>&nbsp;</th> <th class="Formal" scope="col" align="left" valign="bottom"> <a name="48065"></a> <font face="tahoma" size="2"><strong>Result Type</strong></font>&nbsp;</th> </tr> </thead><tbody> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="48067"></a> <p class="TB"><font face="tahoma" size="2"> Datetime&nbsp;</p></td> <td class="Formal"> <a name="48069"></a> <p class="TB"><font face="tahoma" size="2"> +&nbsp;</p></td> <td class="Formal"> <a name="48071"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> <td class="Formal"> <a name="48073"></a> <p class="TB"><font face="tahoma" size="2"> Datetime&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="48075"></a> <p class="TB"><font face="tahoma" size="2"> Datetime&nbsp;</p></td> <td class="Formal"> <a name="48077"></a> <p class="TB"><font face="tahoma" size="2"> -&nbsp;</p></td> <td class="Formal"> <a name="48079"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> <td class="Formal"> <a name="48081"></a> <p class="TB"><font face="tahoma" size="2"> Datetime&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="48083"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> <td class="Formal"> <a name="48085"></a> <p class="TB"><font face="tahoma" size="2"> +&nbsp;</p></td> <td class="Formal"> <a name="48087"></a> <p class="TB"><font face="tahoma" size="2"> Datetime&nbsp;</p></td> <td class="Formal"> <a name="48089"></a> <p class="TB"><font face="tahoma" size="2"> Datetime&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="48091"></a> <p class="TB"><font face="tahoma" size="2"> Datetime&nbsp;</p></td> <td class="Formal"> <a name="48093"></a> <p class="TB"><font face="tahoma" size="2"> -&nbsp;</p></td> <td class="Formal"> <a name="48095"></a> <p class="TB"><font face="tahoma" size="2"> Datetime&nbsp;</p></td> <td class="Formal"> <a name="48097"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="48099"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> <td class="Formal"> <a name="48101"></a> <p class="TB"><font face="tahoma" size="2"> +&nbsp;</p></td> <td class="Formal"> <a name="48103"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> <td class="Formal"> <a name="48105"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="48107"></a> <p class="TB"><font face="tahoma" size="2"> Interval &nbsp;</p></td> <td class="Formal"> <a name="48109"></a> <p class="TB"><font face="tahoma" size="2"> -&nbsp;</p></td> <td class="Formal"> <a name="48111"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> <td class="Formal"> <a name="48113"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="48115"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> <td class="Formal"> <a name="48117"></a> <p class="TB"><font face="tahoma" size="2"> *&nbsp;</p></td> <td class="Formal"> <a name="48119"></a> <p class="TB"><font face="tahoma" size="2"> Numeric&nbsp;</p></td> <td class="Formal"> <a name="48121"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="48123"></a> <p class="TB"><font face="tahoma" size="2"> Numeric&nbsp;</p></td> <td class="Formal"> <a name="48125"></a> <p class="TB"><font face="tahoma" size="2"> *&nbsp;</p></td> <td class="Formal"> <a name="48127"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> <td class="Formal"> <a name="48129"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> </tr> <tr class="Formal" align="left" valign="top"><td class="Formal"> <a name="48131"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> <td class="Formal"> <a name="48133"></a> <p class="TB"><font face="tahoma" size="2"> /&nbsp;</p></td> <td class="Formal"> <a name="48135"></a> <p class="TB"><font face="tahoma" size="2"> Numeric&nbsp;</p></td> <td class="Formal"> <a name="48137"></a> <p class="TB"><font face="tahoma" size="2"> Interval&nbsp;</p></td> </tr> </tbody></table> <table class="TableNote" dir="ltr" cellpadding="3" cellspacing="0" width="100%"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> <a name="51539"></a> <p class="BP"> Oracle performs all timestamp arithmetic in UTC time. For <code>TIMESTAMP</code> <code>WITH</code> <code>LOCAL</code> <code>TIME</code> <code>ZONE</code>, Oracle converts the datetime value from the database time zone to UTC and converts back to the database time zone after performing the arithmetic. For <code>TIMESTAMP</code> <code>WITH</code> <code>TIME</code> <code>ZONE</code>, the datetime value is always in UTC, so no conversion is necessary. </p> <a name="51542"></a> <!--TOC=h3-"51542"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Support for Daylight Savings Times </font></h4> <!--/TOC=h3--> <a name="51589"></a> <p class="BP"> Oracle automatically determines, for any given time zone region, whether daylight savings is in effect and returns local time values based accordingly. The datetime value is sufficient for Oracle to determine whether daylight savings time is in effect for a given region in all cases except <strong class="Bold">boundary cases</strong>. A boundary case occurs during the period when daylight savings goes into or comes out of effect. For example, in the US-Pacific region, when daylight savings goes into effect, the time changes from 2:00 a.m. to 3:00 a.m. The one hour interval between 2 and 3 a.m. does not exist. When daylight savings goes out of effect, the time changes from 2:00 a.m. back to 1:00 a.m., and the one-hour interval between 1 and 2 a.m. is repeated. </p> <a name="51590"></a> <p class="BP"> To resolve these boundary cases, Oracle uses the <code>TZR</code> and <code>TZD</code> format elements, as described in Table&nbsp;2-12<a href="file:///C:/Surbhi/Oracle%20Documenation/DOC/server.901/a90125/sql_elements4.htm#48515"></a>. <code>TZR</code> represents the time zone region in datetime input strings. Examples are '<code>Australia/North</code>', '<code>UTC</code>', and '<code>Singapore</code>'. <code>TZD</code> represents an abbreviated form of the time zone region with daylight savings information. Examples are '<code>PST</code>' for US/Pacific standard time and '<code>PDT</code>' for US/Pacific daylight time. To see a listing of valid values for the <code>TZR</code> and <code>TZD</code> format elements, query the <code>TZNAME</code> and <code>TZABBREV</code> columns of the <code>V$TIMEZONE_NAMES</code> dynamic performance view. </p> <a name="51550"> </a><div align="center"> <p> </p> </div> <a name="48147"></a> <!--TOC=h3-"48147"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Datetime and Interval Example</font></h4> <!--/TOC=h3--> <a name="48148"></a> <p class="BP"> The following example shows how to declare some datetime and interval datatypes. </p> <pre class="CE"><a name="48149"></a>CREATE TABLE my_table ( <a name="48150"></a> start_time TIMESTAMP, <a name="48151"></a> duration_1 INTERVAL DAY (6) TO SECOND (5), <a name="48152"></a> duration_2 INTERVAL YEAR TO MONTH); <a name="48153"></a> </pre> <a name="48154"></a> <p class="BP"> The <code>start_time</code> column is of type <code>TIMESTAMP</code>. The implicit fractional seconds precision of <code>TIMESTAMP</code> is 6. </p> <a name="48155"></a> <p class="BP"> The <code>duration_1</code> column is of type <code>INTERVAL</code> <code>DAY</code> <code>TO</code> <code>SECOND</code>. The maximum number of digits in field <code>DAY</code> is 6 and the maximum number of digits in the fractional second is 5. (The maximum number of digits in all other datetime fields is 2.) </p> <a name="48156"></a> <p class="BP"> The <code>duration_2</code> column is of type <code>INTERVAL</code> <code>YEAR</code> <code>TO</code> <code>MONTH</code>. The maximum number of digits of the value in each field (<code>YEAR</code> and <code>MONTH</code>) is 2. </p> <a name="46020"></a> <!--TOC=h3-"46020"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> RAW and LONG RAW Datatypes </font></h4> <!--/TOC=h3--> <a name="46021"></a> <p class="BP"> The <code>RAW</code> and <code>LONG</code> <code>RAW</code> datatypes store data that is not to be interpreted (not explicitly converted when moving data between different systems) by Oracle. These datatypes are intended for binary data or byte strings. For example, you can use <code>LONG</code> <code>RAW</code> to store graphics, sound, documents, or arrays of binary data, for which the interpretation is dependent on the use. </p> <a name="46031"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="46032"></a> <p class="BP"> <code>RAW</code> is a variable-length datatype like <code>VARCHAR2</code>, except that Oracle Net (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting <code>RAW</code> or <code>LONG</code> <code>RAW</code> data. In contrast, Oracle Net and Import/Export automatically convert <code>CHAR</code>, <code>VARCHAR2</code>, and <code>LONG</code> data from the database character set to the user session character set (which you can set with the <code>NLS_LANGUAGE</code> parameter of the <code>ALTER</code> <code>SESSION</code> statement), if the two character sets are different. </p> <a name="46034"></a> <p class="BP"> When Oracle automatically converts <code>RAW</code> or <code>LONG</code> <code>RAW</code> data to and from <code>CHAR</code> data, the binary data is represented in hexadecimal form, with one hexadecimal character representing every four bits of <code>RAW</code> data. For example, one byte of <code>RAW</code> data with bits 11001011 is displayed and entered as 'CB'. </p> <a name="46037"></a> <h5 class="SH2W"><font face="tahoma" size="2">Large Object (LOB) Datatypes </font></h5> <a name="46038"></a> <p class="BP"> The built-in LOB datatypes <code>BLOB</code>, <code>CLOB</code>, and <code>NCLOB</code> (stored internally) and <code>BFILE</code> (stored externally), can store large and unstructured data such as text, image, video, and spatial data up to 4 gigabytes in size. </p> <a name="46039"></a> <p class="BP">When creating a table, you can optionally specify different tablespace and storage characteristics for LOB columns or LOB object attributes from those specified for the table. </p> <a name="46041"></a> <p class="BP"> LOB columns contain LOB locators that can refer to out-of-line or in-line LOB values. Selecting a LOB from a table actually returns the LOB's locator and not the entire LOB value. The <code>DBMS_LOB</code> package and Oracle Call Interface (OCI) operations on LOBs are performed through these locators. </p> <a name="46043"></a> <p class="BP"> LOBs are similar to <code>LONG</code> and <code>LONG</code> <code>RAW</code> types, but differ in the following ways: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="46044"></a>LOBs can be attributes of a user-defined datatype (object). <p> </p></li><li class="LB1" type="disc"><a name="46045"></a>The LOB locator is stored in the table column, either with or without the actual LOB value. <code>BLOB</code>, <code>NCLOB</code>, and <code>CLOB</code> values can be stored in separate tablespaces. <code>BFILE</code> data is stored in an external file on the server. <p> </p></li><li class="LB1" type="disc"><a name="46046"></a>When you access a LOB column, the locator is returned. <p> </p></li><li class="LB1" type="disc"><a name="46047"></a>A LOB can be up to 4 gigabytes in size. <code>BFILE</code> maximum size is operating system dependent, but cannot exceed 4 gigabytes. <p> </p></li><li class="LB1" type="disc"><a name="46048"></a>LOBs permit efficient, random, piece-wise access to and manipulation of data. <p> </p></li><li class="LB1" type="disc"><a name="46049"></a>You can define more than one LOB column in a table. <p> </p></li><li class="LB1" type="disc"><a name="46050"></a>With the exception of <code>NCLOB</code>, you can define one or more LOB attributes in an object. <p> </p></li><li class="LB1" type="disc"><a name="46051"></a>You can declare LOB bind variables. <p> </p></li><li class="LB1" type="disc"><a name="46052"></a>You can select LOB columns and LOB attributes. <p> </p></li><li class="LB1" type="disc"><a name="46053"></a>You can insert a new row or update an existing row that contains one or more LOB columns and/or an object with one or more LOB attributes. (You can set the internal LOB value to <code>NULL</code>, empty, or replace the entire LOB with data. You can set the <code>BFILE</code> to <code>NULL</code> or make it point to a different file.) <p> </p></li><li class="LB1" type="disc"><a name="46054"></a>You can update a LOB row/column intersection or a LOB attribute with another LOB row/column intersection or LOB attribute. <p> </p></li><li class="LB1" type="disc"><a name="46055"></a>You can delete a row containing a LOB column or LOB attribute and thereby also delete the LOB value. Note that for BFILEs, the actual operating system file is not deleted. <p> </p></li></ul> <a name="46057"></a> <p class="BP"> You can access and populate rows of an internal LOB column (a LOB column stored in the database) simply by issuing an <code>INSERT</code> or <code>UPDATE</code> statement. However, to access and populate a LOB attribute that is part of an object type, you must first initialize the LOB attribute using the <code>EMPTY_CLOB</code> or <code>EMPTY_BLOB</code> function. You can then select the empty LOB attribute and populate it using the <code>DBMS_LOB</code> package or some other appropriate interface. </p> <a name="72744"></a> <p class="BP"> LOB columns are subject to the following restrictions: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="72745"></a>Distributed LOBs are not supported. Therefore, you cannot use a remote locator in <code>SELECT</code> or <code>WHERE</code> clauses of queries or in functions of the <code>DBMS_LOB</code> package. <p> <a name="72746"></a> </p><p class="BP1"> The following syntax is not supported for LOBs: </p> <pre class="CE1"><a name="72747"></a>SELECT lobcol FROM table1@remote_site; <a name="72748"></a>INSERT INTO lobtable SELECT type1.lobattr FROM table1@remote_ site; <a name="72749"></a>SELECT DBMS_LOB.getlength(lobcol) FROM table1@remote_site; <a name="72750"></a> </pre> <a name="72751"></a> <p class="BP1"> However, you can use a remote locator in others parts of queries that reference LOBs. The following syntax is supported on remote LOB columns: </p> <pre class="CE1"><a name="72752"></a>CREATE TABLE t AS SELECT * FROM table1@remote_site; <a name="72753"></a>INSERT INTO t SELECT * FROM table1@remote_site; <a name="72754"></a>UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site); <a name="72755"></a>INSERT INTO table1@remote_site ... <a name="72756"></a>UPDATE table1@remote_site ... <a name="72757"></a>DELETE table1@remote_site ... <a name="72758"></a> </pre> <a name="72759"></a> <p class="BP1">For the first three types of statement, which contain subqueries, only standalone LOB columns are allowed in the select list. SQL functions or <code>DBMS_LOB</code> APIs on LOBs are not supported. For example, the following statement is supported: </p> <pre class="CE1"><a name="72760"></a>CREATE TABLE AS SELECT clob_col FROM tab@dbs2; <a name="72761"></a> </pre> <a name="72762"></a> <p class="BP1"> However, the following statement is not supported: </p> <pre class="CE1"><a name="72763"></a>CREATE TABLE AS SELECT dbms_lob.substr(clob_col) from tab@dbs2; <a name="72764"></a> </pre> </li><li class="LB1" type="disc"><a name="72765"></a>Clusters cannot contain LOBs, either as key or nonkey columns. <p> </p></li><li class="LB1" type="disc"><a name="72766"></a>You cannot create a varray of LOBs. <p> </p></li><li class="LB1" type="disc"><a name="72767"></a>You cannot specify LOB columns in the <code>ORDER</code> <code>BY</code> clause of a query, or in the <code>GROUP</code> <code>BY</code> clause of a query or in an aggregate function. <p> </p></li><li class="LB1" type="disc"><a name="72768"></a>You cannot specify a LOB column in a <code>SELECT</code> ... <code>DISTINCT</code> or <code>SELECT</code> ... <code>UNIQUE</code> statement or in a join. However, you can specify a LOB attribute of an object type column in a <code>SELECT</code> ... <code>DISTINCT</code> statement or in a query that uses the <code>UNION</code> or <code>MINUS</code> set operator if the column's object type has a <code>MAP</code> or <code>ORDER</code> function defined on it. <p> </p></li><li class="LB1" type="disc"><a name="72769"></a>You cannot specify an <code>NCLOB</code> as an attribute of an object type when creating a table. However, you can specify <code>NCLOB</code> parameters in methods. <p> </p></li><li class="LB1" type="disc"><a name="72770"></a>You cannot specify LOB columns in <code>ANALYZE</code> ... <code>COMPUTE</code> or <code>ANALYZE</code> ... <code>ESTIMATE</code> statements. <p> </p></li><li class="LB1" type="disc"><a name="72771"></a>You cannot store LOBs in <code>AUTO</code> segment-managed tablespaces. <p> </p></li><li class="LB1" type="disc"><a name="72772"></a>In a PL/SQL trigger body of an <code>BEFORE</code> <code>ROW</code> DML trigger, you can read the <code>:old</code> value of the LOB, but you cannot read the <code>:new</code> value. However, for <code>AFTER</code> <code>ROW</code> and <code>INSTEAD</code> <code>OF</code> DML triggers, you can read both the <code>:new</code> and <code>:old</code> values. <p> </p></li><li class="LB1" type="disc"><a name="72773"></a>You cannot define an <code>UPDATE</code> DML trigger on a LOB column. <p> </p></li><li class="LB1" type="disc"><a name="72774"></a>You cannot specify a LOB as a primary key column. <p> </p></li><li class="LB1" type="disc"><a name="72775"></a>You cannot specify a LOB column as part of an index key. However, you can specify a LOB column in the function of a function-based index or in the indextype specification of a domain index. In addition, Oracle Text lets you define an index on a CLOB column. <p> <a name="72785"> </a></p><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> </li><li class="LB1" type="disc"><a name="72786"></a>In an <code>INSERT</code> or <code>UPDATE</code> operation, you can bind data of any size to a LOB column, but you cannot bind data to a LOB attribute of an object type. In an <code>INSERT</code> ... <code>AS</code> <code>SELECT</code> operation, you can bind up to 4000 bytes of data to LOB columns. <p> <a name="72796"><code> </code></a></p><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> </li><li class="LB1" type="disc"><a name="72797"></a>If a table has both <code>LONG</code> and LOB columns, you cannot bind more than 4000 bytes of data to both the <code>LONG</code> and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the <code>LONG</code> or the LOB column. <p> <a name="72811"> </a></p><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="46067"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> </li></ul> <a name="60120"></a> <p class="BP"> The following example shows how the sample table <code>pm.print_media</code> was created. (This example assumes the existence of the <code>textdoc_tab</code> object table, which is nested table in the <code>print_media</code> table.) </p> <pre class="CE"><a name="46069"></a>CREATE TABLE print_media <a name="58555"></a> ( product_id NUMBER(6) <a name="58557"></a> , ad_id NUMBER(6) <a name="67575"></a> , ad_composite BLOB <a name="67576"></a> , ad_sourcetext CLOB <a name="67577"></a> , ad_finaltext CLOB <a name="58565"></a> , ad_fltextn NCLOB <a name="58567"></a> , ad_textdocs_ntab textdoc_tab <a name="58569"></a> , ad_photo BLOB <a name="58571"></a> , ad_graphic BFILE <a name="58573"></a> , ad_header adheader_typ <a name="58575"></a> , press_release LONG <a name="58577"></a> ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab; </pre> <a name="46103"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="46105"></a> <!--TOC=h3-"46105"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> BFILE Datatype </font></h4> <!--/TOC=h3--> <a name="46106"></a> <p class="BP"> The <code>BFILE</code> datatype enables access to binary file LOBs that are stored in file systems outside the Oracle database. A <code>BFILE</code> column or attribute stores a <code>BFILE</code> locator, which serves as a pointer to a binary file on the server's file system. The locator maintains the directory alias and the filename. </p> <a name="60128"></a> <p class="BP"> You can change the filename and path of a <code>BFILE</code> without affecting the base table by using the <code>BFILENAME</code> function. </p> <a name="60129"> </a><div align="center"> <p> </p> </div> <a name="60147"></a> <p class="BP"> Binary file LOBs do not participate in transactions and are not recoverable. Rather, the underlying operating system provides file integrity and durability. The maximum file size supported is 4 gigabytes. </p> <a name="46108"></a> <p class="BP"> The database administrator must ensure that the file exists and that Oracle processes have operating system read permissions on the file. </p> <a name="46109"></a> <p class="BP"> The <code>BFILE</code> datatype enables read-only support of large binary files. You cannot modify or replicate such a file. Oracle provides APIs to access file data. The primary interfaces that you use to access file data are the <code>DBMS_LOB</code> package and the OCI. </p> <a name="46132"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="46134"></a> <!--TOC=h3-"46134"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> BLOB Datatype </font></h4> <!--/TOC=h3--> <a name="46135"></a> <p class="BP"> The <code>BLOB</code> datatype stores unstructured binary large objects. <code>BLOBs</code> can be thought of as bitstreams with no character set semantics. <code>BLOBs</code> can store up to 4 gigabytes of binary data. </p> <a name="46137"></a> <p class="BP"> <code>BLOBs</code> have full transactional support. Changes made through SQL, the <code>DBMS_LOB</code> package, or the OCI participate fully in the transaction. <code>BLOB</code> value manipulations can be committed and rolled back. However, you cannot save a <code>BLOB</code> locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session. </p> <a name="46139"></a> <!--TOC=h3-"46139"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> CLOB Datatype </font></h4> <!--/TOC=h3--> <a name="46140"></a> <p class="BP"> The <code>CLOB</code> datatype stores single-byte and multibyte character data. Both fixed-width and variable-width character sets are supported, and both use the <code>CHAR</code> database character set. <code>CLOBs</code> can store up to 4 gigabytes of character data. </p> <a name="46142"></a> <p class="BP"> <code>CLOBs</code> have full transactional support. Changes made through SQL, the <code>DBMS_LOB</code> package, or the OCI participate fully in the transaction. <code>CLOB</code> value manipulations can be committed and rolled back. However, you cannot save a <code>CLOB</code> locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session. </p> <a name="46144"></a> <!--TOC=h3-"46144"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> NCLOB Datatype </font></h4> <!--/TOC=h3--> <a name="46145"></a> <p class="BP"> The <code>NCLOB</code> datatype stores Unicode data using the national character set. Both fixed-width and variable-width character sets are supported. <code>NCLOBs</code> can store up to 4 gigabytes of character text data. </p> <a name="46147"></a> <p class="BP"> <code>NCLOBs</code> have full transactional support. Changes made through SQL, the <code>DBMS_LOB</code> package, or the OCI participate fully in the transaction. <code>NCLOB</code> value manipulations can be committed and rolled back. However, you cannot save an <code>NCLOB</code> locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session. </p> <a name="55352"> </a><div align="center"> <p> </p> </div> <a name="46150"></a> <!--TOC=h3-"46150"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> ROWID Datatype </font></h4> <!--/TOC=h3--> <a name="46152"></a> <p class="BP"> Each row in the database has an address. You can examine a row's address by querying the pseudocolumn <code>ROWID</code>. Values of this pseudocolumn are hexadecimal strings representing the address of each row. These strings have the datatype <code>ROWID</code>. You can also create tables and clusters that contain actual columns having the <code>ROWID</code> datatype. Oracle does not guarantee that the values of such columns are valid rowids. </p> <a name="46163"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="46165"></a> <!--TOC=h3-"46165"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Restricted Rowids </font></h4> <!--/TOC=h3--> <a name="46166"></a> <p class="BP"> Beginning with Oracle8, Oracle SQL incorporated an extended format for rowids to efficiently support partitioned tables and indexes and tablespace-relative data block addresses (DBAs) without ambiguity. </p> <a name="46167"></a> <p class="BP"> Character values representing rowids in Oracle7 and earlier releases are called <strong class="Bold">restricted</strong> rowids. Their format is as follows: </p> <pre class="CE"><a name="46168"></a>block.row.file <a name="46169"></a> </pre> <a name="46171"></a> <p class="BP"> where: </p> <ul class="LB1"> <li class="LB1" type="disc"><a name="53997"></a><em><code>block</code></em> is a hexadecimal string identifying the data block of the datafile containing the row. The length of this string depends on your operating system. <p> </p></li><li class="LB1" type="disc"><a name="54004"></a><em><code>row</code></em> is a four-digit hexadecimal string identifying the row in the data block. The first row of the block has a digit of 0. <p> </p></li><li class="LB1" type="disc"><a name="54011"></a><em><code>file</code></em> is a hexadecimal string identifying the database file containing the row. The first datafile has the number 1. The length of this string depends on your operating system. <p> </p></li></ul> <a name="46187"></a> <!--TOC=h3-"46187"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Extended Rowids </font></h4> <!--/TOC=h3--> <a name="46188"></a> <p class="BP"> The <strong class="Bold">extended</strong> <code>ROWID</code> datatype stored in a user column includes the data in the restricted rowid plus a <strong class="Bold">data object number</strong>. 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 <code>USER_OBJECTS</code>, <code>DBA_OBJECTS</code>, and <code>ALL_OBJECTS</code>. Objects that share the same segment (clustered tables in the same cluster, for example) have the same object number. </p> <a name="46190"></a> <p class="BP"> Extended rowids are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, as well as the plus sign (+) and forward slash (/). Extended rowids are not available directly. You can use a supplied package, <code>DBMS_ROWID</code>, 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. </p> <a name="46199"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="46201"></a> <!--TOC=h3-"46201"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> Compatibility and Migration </font></h4> <!--/TOC=h3--> <a name="46202"></a> <p class="BP"> The restricted form of a rowid is still supported in Oracle9<em class="Italic">i</em> for backward compatibility, but all tables return rowids in the extended format. </p> <a name="46211"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="46215"></a> <!--TOC=h3-"46215"--> <h4 class="H3"><font color="#000000" face="tahoma" size="2"> UROWID Datatype </font></h4> <!--/TOC=h3--> <a name="46216"></a> <p class="BP"> Each row in a database has an address. However, the rows of some tables have addresses that are not physical or permanent or were not generated by Oracle. 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. </p> <a name="46218"></a> <p class="BP"> Oracle uses "universal rowids" (<strong class="Bold">urowids</strong>) 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 <code>ROWID</code> pseudocolumn (as are the physical rowids of heap-organized tables). </p> <a name="46219"></a> <p class="BP"> Oracle creates logical rowids based on a table's primary key. The logical rowids do not change as long as the primary key does not change. The <code>ROWID</code> pseudocolumn of an index-organized table has a datatype of <code>UROWID</code>. You can access this pseudocolumn as you would the <code>ROWID</code> pseudocolumn of a heap-organized table (that is, using the <code>SELECT</code> <code>ROWID</code> statement). If you wish <code>to</code> store the rowids of an index-organized table, you can define a column of type <code>UROWID</code> for the table and retrieve the value of the <code>ROWID</code> pseudocolumn into that column. </p> <a name="46223"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <a name="46246"> </a><div align="center"> <p> </p> <table class="TableNote" dir="ltr" cellpadding="3"> <tbody><tr class="TableNote" align="left" valign="top"><td class="TableNote"> </td></tr> </tbody></table> </div> <br><br> <script type="text/javascript"><!-- google_ad_client = "pub-4228419744604469"; google_ad_width = 728; google_ad_height = 90; google_ad_format = "728x90_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "E7EFE7"; google_color_bg = "E7EFE7"; google_color_link = "000000"; google_color_url = "000000"; google_color_text = "000000"; //--></script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script> <p> <a href="introduction_sql.html">Previous Chapter </a> | <a href="literals_sql.html">Next Chapter </a> <BR><BR> <a href = "http://learnoracle.activeboard.com/index.spark?forumID=122598&p=3&topicID=27926969">Discuss Article</a><br><br> <!-- <font face="tahoma" size="2" size="2"> <B><U>More Articles</U></B><BR><BR> 1. <a href="http://oracleonline.info/Advanced_queue.html">What Is Advanced Queuing?</a><br> 2. <a href="http://oracleonline.info/features_advanced_queue.html"> General Features of Advanced Queuing </a><br> 3. <a href="http://oracleonline.info/more_features_advanced_queue.html">Enqueue, Dequeue, Propagation Features of Advanced Queuing</a><br> 4. <a href="http://oracleonline.info/elements_advanced_queue.html">Elements of Advanced Queuing</a><br> 5. <a href="http://oracleonline.info/component_advanced_queue.html">Basic Components of Advanced Queuing</a><br><br> </font> --> </b><font face="tahoma" size="2" size="2"> <P> <A HREF="http://oracleonline.info"> <B>More Tutorials on Oracle dba ...</B></FONT></A> <BR> <BR> Source :Oracle Documentation</a> <BR><BR> <!-- Social Bookmarking code starts here --> <!-- * Social Bookmark Script * @ Version 1.9 * @ Copyright (C) 2006-2008 by Alexander Hadj Hassine - All rights reserved * @ Website http://www.social-bookmark-script.com/ * @ * @ By using our script <b> Oracle DBA </b> must leave our copyright notices and the links * @ in the script untouched. The links doesn't be removed, converted, hidden * @ or made invisible. If <b> Oracle DBA </b> set a backlink to http://www.social bookmark script.com/ * @ (at least 1 time "search machines friendly" from the starting side of your web page) * @ <b> Oracle DBA </b> can be adapt the script to <b> Oracle DBA </b> for your purpose changes. --> <a target="_blank" style="text-decoration:none; font-size:11px; font-family:Arial; color: #2A4956;" href="http://www.social-bookmark-script.com/">Liked it ? Want to share it ? Social Bookmarking</a><br> <div style="border-top-style:solid; padding-top:3px; border-top-width: 1px; border-top-color: #2A4956; float: left;"> <script language="JavaScript" type="text/JavaScript"> <!-- function Social_Load() { var d=document; if(d.images){ if(!d.Social) d.Social=new Array(); var i,j=d.Social.length,a=Social_Load.arguments; for(i=0; i<a.length; i++) if (a[i].indexOf("#")!=0){ d.Social[j]=new Image; d.Social[j++].src=a[i];}} } Social_Load('http://www.social-bookmark-script.de/img/bookmarks/wong_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/boni_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/newsider_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/digg_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/del_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/reddit_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/jumptags_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/stumbleupon_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/slashdot_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/netscape_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/furl_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/yahoo_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/spurl_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/google_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/blinklist_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/technorati_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/newsvine_trans_ani.gif','http://www.social-bookmark-script.de/img/bookmarks/what_trans_ani.gif','http://www.social-bookmark-script.de/load.gif') function schnipp() { var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc; } function schnupp(n, d) { var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) { d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);} if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n]; for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=schnupp(n,d.layers[i].document); if(!x && d.getElementById) x=d.getElementById(n); return x; } function schnapp() { var i,j=0,x,a=schnapp.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3) if ((x=schnupp(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];} } //--> </script> <a rel="nofollow" style="text-decoration:none;" href="http://www.mister-wong.de/" onclick="window.open('http://www.mister-wong.de/index.php?action=addurl&amp;bm_url='+encodeURIComponent(location.href)+'&amp;bm_notice=&amp;bm_description='+encodeURIComponent(document.title)+'&amp;bm_tags=');return false;" title="Add to: Mr. Wong" onmouseover="schnapp('wong','','http://www.social-bookmark-script.de/img/bookmarks/wong_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/wong_trans.gif" alt="Add to: Mr. Wong" name="wong" border="0" id="wong"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.bonitrust.de/" onclick="window.open('http://www.bonitrust.de/account/bookmark/?bookmark_url='+ unescape(location.href));return false;" title="Add to: BoniTrust" onmouseover="schnapp('Boni','','http://www.social-bookmark-script.de/img/bookmarks/boni_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/boni_trans.gif" alt="Add to: BoniTrust" name="Boni" border="0" id="Boni"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.newsider.de/" onclick="window.open('http://www.newsider.de/submit.php?url='+(document.location.href));return false;" title="Add to: Newsider" onmouseover="schnapp('Newsider','','http://www.social-bookmark-script.de/img/bookmarks/newsider_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/newsider_trans.gif" alt="Add to: Newsider" name="Newsider" border="0" id="Newsider"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://digg.com/" onclick="window.open('http://digg.com/submit?phase=2&amp;url='+encodeURIComponent(location.href)+'&amp;bodytext=&amp;tags=&amp;title='+encodeURIComponent(document.title));return false;" title="Add to: Digg" onmouseover="schnapp('Digg','','http://www.social-bookmark-script.de/img/bookmarks/digg_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/digg_trans.gif" alt="Add to: Digg" name="Digg" border="0" id="Digg"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://del.icio.us/" onclick="window.open('http://del.icio.us/post?v=2&amp;url='+encodeURIComponent(location.href)+'&amp;notes=&amp;tags=&amp;title='+encodeURIComponent(document.title));return false;" title="Add to: Del.icio.us" onmouseover="schnapp('Delicious','','http://www.social-bookmark-script.de/img/bookmarks/del_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/del_trans.gif" alt="Add to: Del.icio.us" name="Delicious" border="0" id="Delicious"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://reddit.com/" onclick="window.open('http://reddit.com/submit?url='+encodeURIComponent(location.href)+'&amp;title='+encodeURIComponent(document.title));return false;" title="Add to: Reddit" onmouseover="schnapp('Reddit','','http://www.social-bookmark-script.de/img/bookmarks/reddit_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/reddit_trans.gif" alt="Add to: Reddit" name="Reddit" border="0" id="Reddit"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.jumptags.com/" onclick="window.open('http://www.jumptags.com/add/?url='+encodeURIComponent(location.href)+'&amp;title='+encodeURIComponent(document.title));return false;" title="Add to: Jumptags" onmouseover="schnapp('Jumptags','','http://www.social-bookmark-script.de/img/bookmarks/jumptags_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/jumptags_trans.gif" alt="Add to: Jumptags" name="Jumptags" border="0" id="Jumptags"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.stumbleupon.com/" onclick="window.open('http://www.stumbleupon.com/submit?url='+encodeURIComponent(location.href)+'&amp;title='+encodeURIComponent(document.title));return false;" title="Add to: StumbleUpon" onmouseover="schnapp('StumbleUpon','','http://www.social-bookmark-script.de/img/bookmarks/stumbleupon_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/stumbleupon_trans.gif" alt="Add to: StumbleUpon" name="StumbleUpon" border="0" id="StumbleUpon"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://slashdot.org/" onclick="window.open('http://slashdot.org/bookmark.pl?url='+encodeURIComponent(location.href)+'&amp;title='+encodeURIComponent(document.title));return false;" title="Add to: Slashdot" onmouseover="schnapp('Slashdot','','http://www.social-bookmark-script.de/img/bookmarks/slashdot_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/slashdot_trans.gif" alt="Add to: Slashdot" name="Slashdot" border="0" id="Slashdot"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.netscape.com/" onclick="window.open('http://www.netscape.com/submit/?U='+encodeURIComponent(location.href)+'&amp;T='+encodeURIComponent(document.title));return false;" title="Add to: Netscape" onmouseover="schnapp('Netscape','','http://www.social-bookmark-script.de/img/bookmarks/netscape_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/netscape_trans.gif" alt="Add to: Netscape" name="Netscape" border="0" id="Netscape"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.furl.net/" onclick="window.open('http://www.furl.net/storeIt.jsp?u='+encodeURIComponent(location.href)+'&amp;keywords=&amp;t='+encodeURIComponent(document.title));return false;" title="Add to: Furl" onmouseover="schnapp('Furl','','http://www.social-bookmark-script.de/img/bookmarks/furl_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/furl_trans.gif" alt="Add to: Furl" name="Furl" border="0" id="Furl"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.yahoo.com/" onclick="window.open('http://myweb2.search.yahoo.com/myresults/bookmarklet?t='+encodeURIComponent(document.title)+'&amp;d=&amp;tag=&amp;u='+encodeURIComponent(location.href));return false;" title="Add to: Yahoo" onmouseover="schnapp('Yahoo','','http://www.social-bookmark-script.de/img/bookmarks/yahoo_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/yahoo_trans.gif" alt="Add to: Yahoo" name="Yahoo" border="0" id="Yahoo"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.spurl.net/" onclick="window.open('http://www.spurl.net/spurl.php?v=3&amp;tags=&amp;title='+encodeURIComponent(document.title)+'&amp;url='+encodeURIComponent(document.location.href));return false;" title="Add to: Spurl" onmouseover="schnapp('Spurl','','http://www.social-bookmark-script.de/img/bookmarks/spurl_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/spurl_trans.gif" alt="Add to: Spurl" name="Spurl" border="0" id="Spurl"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.google.com/" onclick="window.open('http://www.google.com/bookmarks/mark?op=add&amp;hl=de&amp;bkmk='+encodeURIComponent(location.href)+'&amp;annotation=&amp;labels=&amp;title='+encodeURIComponent(document.title));return false;" title="Add to: Google" onmouseover="schnapp('Google','','http://www.social-bookmark-script.de/img/bookmarks/google_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/google_trans.gif" alt="Add to: Google" name="Google" border="0" id="Google"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.blinklist.com/" onclick="window.open('http://www.blinklist.com/index.php?Action=Blink/addblink.php&amp;Description=&amp;Tag=&amp;Url='+encodeURIComponent(location.href)+'&amp;Title='+encodeURIComponent(document.title));return false;" title="Add to: Blinklist" onmouseover="schnapp('Blinklist','','http://www.social-bookmark-script.de/img/bookmarks/blinklist_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/blinklist_trans.gif" alt="Add to: Blinklist" name="Blinklist" border="0" id="Blinklist"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.technorati.com/" onclick="window.open('http://technorati.com/faves?add='+encodeURIComponent(location.href)+'&amp;tag=');return false;" title="Add to: Technorati" onmouseover="schnapp('Technorati','','http://www.social-bookmark-script.de/img/bookmarks/technorati_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/technorati_trans.gif" alt="Add to: Technorati" name="Technorati" border="0" id="Technorati"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://www.newsvine.com/" onclick="window.open('http://www.newsvine.com/_wine/save?popoff=1&amp;u='+encodeURIComponent(location.href)+'&amp;tags=&amp;blurb='+encodeURIComponent(document.title));return false;" title="Add to: Newsvine" onmouseover="schnapp('Newsvine','','http://www.social-bookmark-script.de/img/bookmarks/newsvine_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/newsvine_trans.gif" alt="Add to: Newsvine" name="Newsvine" border="0" id="Newsvine"> </a> <a rel="nofollow" style="text-decoration:none;" href="http://en.wikipedia.org/wiki/Social_bookmarking" target="" title="Information" onmouseover="schnapp('Information','','http://www.social-bookmark-script.de/img/bookmarks/what_trans_ani.gif',1)" onmouseout="schnipp()" > <img style="padding-bottom:1px;" src="http://www.social-bookmark-script.de/img/bookmarks/what_trans.gif" alt="Information" name="Information" border="0" id="Information"> </a> </div> <!-- Social Bookmarking code ends here --> <BR><BR> <BR><BR> <p align="center"> <font face="tahoma" size="2" size="2">Want to share or request <B>Oracle Tutorial</B> articles to become a <B>Oracle DBA</B>. Direct your requests to<a href="mailto:webmaster@oracleonline.info" alt='Learn oracle sql plan'> webmaster@oracleonline.info</a></font></p> </td> </tr> </table> </div> </div> </div> </div> </div> </div> </div> </th> </tr> </table> </body> </html>