Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\userguide\oraclewp.12.06.html
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html;charset=utf-8" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <!-- MOTW-DISABLED saved from url=(0014)about:internet --> <title>Data Types</title> <link rel="StyleSheet" href="css/oraclewp.css" type="text/css" media="all" /> <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" /> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/context.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/towwhdir.js"></script> <script type="text/javascript" language="JavaScript1.2" src="wwhdata/common/wwhpagef.js"></script> <script type="text/javascript" language="JavaScript1.2"> <!-- var WebWorksRootPath = ""; // --> </script> <script type="text/javascript" language="JavaScript1.2"> <!-- // Set reference to top level help frame // var WWHFrame = WWHGetWWHFrame("", true); // --> </script> <script type="text/javascript" language="JavaScript1.2" src="scripts/expand.js"></script> </head> <body class="" style="background-color: #FFFFEE;" onload="WWHUpdate();" onunload="WWHUnload();" onkeydown="WWHHandleKeyDown((document.all||document.getElementById||document.layers)?event:null);" onkeypress="WWHHandleKeyPress((document.all||document.getElementById||document.layers)?event:null);" onkeyup="WWHHandleKeyUp((document.all||document.getElementById||document.layers)?event:null);"> <br /> <div class="WebWorks_Breadcrumbs" style="text-align: left;"> <a class="WebWorks_Breadcrumb_Link" href="oraclewp.12.01.html#110036">8 The Oracle Wire Protocol Driver</a> : Data Types</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="110036">Data Types</a></div> <div class="Body"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'oraclewp.12.06.html#166852', '');" name="110040">Table 8-2</a></span> shows how the Oracle data types are mapped to the standard ODBC data types. <span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'oraclewp.12.07.html#110178', '');">â??Unicode Supportâ??</a></span> lists Oracle to Unicode data type mappings.</div> <table class="Format_A" cellspacing="0" summary=""> <caption> <div class="table_title">Table 8-2. <a name="166852">Oracle Data Types</a> </div> </caption> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="166856">Oracle</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_column_heads"><a name="166858">ODBC</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166863">BFILE <sup><a name="wwfootnote_inline_47" href="#47">1</a></sup></a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166865">SQL_LONGVARBINARY</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166867">BINARY DOUBLE <sup><a name="wwfootnote_inline_48" href="#48">2</a></sup></a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166869">SQL_REAL</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166871">BINARY FLOAT </a><span class="superscript" style="color: #0000ff;"><a href="javascript:WWHClickedPopup('ODBC', 'oraclewp.12.06.html#166867', '');">2</a></span></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166873">SQL_DOUBLE</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166875">BLOB </a><span class="superscript" style="color: #0000ff;"><a href="javascript:WWHClickedPopup('ODBC', 'oraclewp.12.06.html#166867', '');">2</a></span></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166877">SQL_LONGVARBINARY</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166879">CHAR</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166881">SQL_CHAR</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166883">CLOB </a><span class="superscript" style="color: #0000ff;"><a href="javascript:WWHClickedPopup('ODBC', 'oraclewp.12.06.html#166867', '');">2</a></span></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166885">SQL_LONGVARCHAR</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166887">DATE</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166889">SQL_TYPE_TIMESTAMP</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166891">LONG</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166893">SQL_LONGVARCHAR</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166895">LONG RAW</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166897">SQL_LONGVARBINARY</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166899">NUMBER</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166901">SQL_DOUBLE</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166903">NUMBER (p,s)</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166905">SQL_DECIMAL</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166907">RAW</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166909">SQL_VARBINARY</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="186644">TIMESTAMP <sup><a name="wwfootnote_inline_49" href="#49">3</a></sup></a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166913">SQL_TIMESTAMP</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166915">TIMESTAMP WITH LOCAL TIMEZONE </a><span class="superscript" style="color: #0000ff;"><a href="javascript:WWHClickedPopup('ODBC', 'oraclewp.12.06.html#186644', '');">3</a></span></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166917">SQL_TIMESTAMP</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166919">TIMESTAMP WITH TIMEZONE </a><span class="superscript" style="color: #0000ff;"><a href="javascript:WWHClickedPopup('ODBC', 'oraclewp.12.06.html#186644', '');">3</a></span> <sup><a name="wwfootnote_inline_50" href="#50">4</a></sup></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="166921">SQL_VARCHAR</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="379245">VARCHAR2</a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="379247">SQL_VARCHAR</a></div> </td> </tr> <tr style="vertical-align: top;"> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="379278">XMLType <sup><a name="wwfootnote_inline_51" href="#51">5</a></sup></a></div> </td> <td style="border-bottom-color: Black; border-bottom-style: solid; border-bottom-width: thin; border-left-color: Black; border-left-style: solid; border-left-width: thin; border-right-color: Black; border-right-style: solid; border-right-width: thin; border-top-color: Black; border-top-style: solid; border-top-width: thin; padding-bottom: auto; padding-left: auto; padding-right: auto; padding-top: auto; vertical-align: top;"> <div class="table_text"><a name="379255">SQL_LONGVARCHAR</a></div> </td> </tr> </table> <hr /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="47" href="#wwfootnote_inline_47">1</a> </div> <div class="TableFootnote"><a name="166862">Read-Only</a></div> <br style="clear: all;" /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="48" href="#wwfootnote_inline_48">2</a> </div> <div class="TableFootnote"><a name="167080">Supported only on Oracle 10</a><span class="Emphasis">g</span> and higher.</div> <br style="clear: all;" /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="49" href="#wwfootnote_inline_49">3</a> </div> <div class="TableFootnote"><a name="186643">Supported only on Oracle 9</a><span class="Emphasis">i</span> and higher.</div> <br style="clear: all;" /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="50" href="#wwfootnote_inline_50">4</a> </div> <div class="TableFootnote"><a name="383851">Timestamp with timezone mapping changes based on the setting of the Fetch TSWTZ as </a>Timestamp option only on Oracle 10<span class="Emphasis">g</span> R2 and higher.</div> <br style="clear: all;" /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="51" href="#wwfootnote_inline_51">5</a> </div> <div class="TableFootnote"><a name="379277">Supported only on Oracle 9</a><span class="Emphasis">i</span> R2 and higher.</div> <br style="clear: all;" /> <div class="Body"><a name="166934">The Oracle Wire Protocol driver does not support any object types (also known as abstract </a>data types). When the driver encounters an object type during data retrieval, it returns an Unknown Data Type error (SQL State HY000).</div> <div class="Body"><a name="110133">See </a><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'usingproduct.05.09.html#107277', '');">â??Retrieving Data Type Informationâ??</a></span> for more information about data types.</div> <div class="head_2"><a name="110135">XMLType</a></div> <div class="Body"><a name="545222">Oracle 9</a><span class="Emphasis">i</span> R2 and higher supports the XMLType data type. The driver supports tables containing columns whose data type is specified as XMLType. </div> <div class="Body"><a name="545223">When inserting or updating XMLType columns, the data to be inserted or updated must be </a>in the form of an XMLType data type. The database provides functions to construct XMLType data. The xmlData argument to xmltype( ) may be specified as a string literal.</div> <div class="head_3"><a name="545224">Examples</a></div> <div class="Body"><a name="545225">If the XMLType column is created with the CLOB storage type, then the driver returns it </a>without use of the special getClobVal function, that is, you can use:</div> <div class="syntax_first"><a name="545226">SELECT </a><span class="EquationVariables">XML_col</span> FROM <span class="EquationVariables">table_name...</span></div> <div class="Body"><a name="545227"> instead of</a></div> <div class="syntax_first"><a name="545228">SELECT </a><span class="EquationVariables">XML_col</span>.getClobVal()...</div> <div class="Body"><a name="545229">The following example illustrates using the CLOB storage type:</a></div> <div class="syntax_first"><a name="545230">CREATE TABLE po_xml_tab(</a></div> <div class="syntax"><a name="545231"> </a> poid NUMBER(10),</div> <div class="syntax"><a name="545232"> </a> poDoc XMLTYPE</div> <div class="syntax"><a name="545233"> </a> )</div> <div class="syntax"><a name="545234"> </a> XMLType COLUMN poDoc</div> <div class="syntax"><a name="545235"> </a> STORE AS CLOB (</div> <div class="syntax"><a name="545236"> </a> TABLESPACE lob_seg_ts</div> <div class="syntax"><a name="545237"> </a> STORAGE (INITIAL 4096 NEXT 4096)</div> <div class="syntax"><a name="545238"> </a> CHUNK 4096 NOCACHE LOGGING</div> <div class="syntax"><a name="545239"> </a> );</div> <div class="Body"><a name="545240">The next example illustrates how to create a table, insert data, and retrieve data when not </a>using the CLOB storage type:</div> <div class="syntax_first"><a name="545241">CREATE TABLE PURCHASEORDER (PODOCUMENT sys.XMLTYPE);</a></div> <div class="Body"><a name="545242">The PURCHASEORDER table contains one columnâ??PODOCUMENTâ??with a data type of </a>XMLType (<span class="Syntax">sys.XMLTYPE</span>). The next step is to insert one purchase order, created by the static function <span class="Syntax">sys.XMLTYPE.createXML</span>:</div> <div class="syntax_first"><a name="545243">INSERT INTO PURCHASEORDER (PODOCUMENT) values (</a></div> <div class="syntax"><a name="545244">sys.XMLTYPE.createXML(</a></div> <div class="syntax"><a name="545245">'</a></div> <div class="syntax"><a name="545246"><PurchaseOrder></a></div> <div class="syntax"><a name="545247"> </a> <Reference>BLAKE-2001062514034298PDT</Reference></div> <div class="syntax"><a name="545248"> </a></div> <div class="syntax"><a name="545249"> </a> <Actions></div> <div class="syntax"><a name="545250"> </a> <Action></div> <div class="syntax"><a name="545251"> </a> <User>KING</User></div> <div class="syntax"><a name="545252"> </a> <Date/></div> <div class="syntax"><a name="545253"> </a> </Action></div> <div class="syntax"><a name="545254"> </a> </Actions></div> <div class="syntax"><a name="545255"> </a> <Reject/></div> <div class="syntax"><a name="545256"> </a></div> <div class="syntax"><a name="545257"> </a> <Requester>David E. Blake</Requester></div> <div class="syntax"><a name="545258"> </a> <User>BLAKE</User></div> <div class="syntax"><a name="545259"> </a> <CostCenter>S30</CostCenter></div> <div class="syntax"><a name="545260"> </a> <ShippingInstructions></div> <div class="syntax"><a name="545261"> </a> <name>David E. Blake</name></div> <div class="syntax"><a name="545262"> </a> <address>400 Oracle Parkway Redwood Shores, CA, 94065 USA</address></div> <div class="syntax"><a name="545263"> </a> <telephone>650 999 9999</telephone></div> <div class="syntax"><a name="545264"> </a> </ShippingInstructions></div> <div class="syntax"><a name="545265"> </a></div> <div class="syntax"><a name="545266"> </a> <SpecialInstructions>Air Mail</SpecialInstructions></div> <div class="syntax"><a name="545267"> </a> <LineItems></div> <div class="syntax"><a name="545268"> </a> <LineItem ItemNumber="1"></div> <div class="syntax"><a name="545269"> </a> <Description>The Birth of a Nation</Description></div> <div class="syntax"><a name="545270"> </a> <Part Id="EE888" UnitPrice="65.39" Quantity="31"/></div> <div class="syntax"><a name="545271"> </a> </LineItem></div> <div class="syntax"><a name="545272"> </a> </LineItems></div> <div class="syntax"><a name="545273"></PurchaseOrder></a></div> <div class="syntax"><a name="545274">'));</a></div> <div class="Body"><a name="545275">Use the getClobVal function to retrieve the data:</a></div> <div class="syntax_first"><a name="545276">SELECT p.podocument.getClobVal() FROM PURCHASEORDER p;</a></div> <script type="text/javascript" language="JavaScript1.2"> <!-- // Clear related topics // WWHClearRelatedTopics(); document.writeln(WWHRelatedTopicsInlineHTML()); // --> </script> </blockquote> <script type="text/javascript" language="JavaScript1.2"> <!-- document.write(WWHRelatedTopicsDivTag() + WWHPopupDivTag() + WWHALinksDivTag()); // --> </script> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de