Edit C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\win32_x86\odbc\7.0.1\help\userguide\oraclecli.17.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/oraclecli.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="oraclecli.17.01.html#558854">13 The Oracle Driver </a> : Data Types</div> <hr align="left" /> <blockquote> <div class="head_1"><a name="558854">Data Types</a></div> <div class="Body"><span class="Cross_ref_"><a href="javascript:WWHClickedPopup('ODBC', 'oraclecli.17.06.html#558868', '');" name="558858">Table 13-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', 'oraclecli.17.07.html#559048', '');">â??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 13-2. <a name="558868">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="576784">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="576786">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="558879">BFILE <sup><a name="wwfootnote_inline_58" href="#58">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="558881">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="558887">BINARY DOUBLE <sup><a name="wwfootnote_inline_59" href="#59">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="558889">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="558894">BINARY FLOAT </a><span class="superscript" style="color: #0000ff;"><a href="javascript:WWHClickedPopup('ODBC', 'oraclecli.17.06.html#558887', '');">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="558896">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="558902">BLOB <sup><a name="wwfootnote_inline_60" href="#60">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="558904">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="558906">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="558908">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="558913">CLOB </a><span class="superscript" style="color: #0000ff;"><a href="javascript:WWHClickedPopup('ODBC', 'oraclecli.17.06.html#558902', '');">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="558915">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="558917">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="558919">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="558921">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="558923">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="558925">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="558927">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="558929">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="558931">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="558933">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="558935">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="558937">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="558939">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="558945">TIMESTAMP <sup><a name="wwfootnote_inline_61" href="#61">4</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="558947">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="558952">TIMESTAMP WITH LOCAL TIMEZONE </a><span class="superscript" style="color: #0000ff;"><a href="javascript:WWHClickedPopup('ODBC', 'oraclecli.17.06.html#558945', '');">4</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="558954">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="558959">TIMESTAMP WITH TIMEZONE </a><span class="superscript" style="color: #0000ff;"><a href="javascript:WWHClickedPopup('ODBC', 'oraclecli.17.06.html#558945', '');">4</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="558961">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="558963">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="558965">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="558970">XMLType <sup><a name="wwfootnote_inline_62" href="#62">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="558972">SQL_LONGVARCHAR</a></div> </td> </tr> </table> <hr /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="58" href="#wwfootnote_inline_58">1</a> </div> <div class="TableFootnote"><a name="558878">Read-Only</a></div> <br style="clear: all;" /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="59" href="#wwfootnote_inline_59">2</a> </div> <div class="TableFootnote"><a name="558886">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="60" href="#wwfootnote_inline_60">3</a> </div> <div class="TableFootnote"><a name="558901">Valid when connecting to Oracle 8 servers; these data types support output parameters to stored procedures</a></div> <br style="clear: all;" /> <div style="font-family: Arial; font-size: 8pt; float: left; padding-right: 10px;"> <a name="61" href="#wwfootnote_inline_61">4</a> </div> <div class="TableFootnote"><a name="558944">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="62" href="#wwfootnote_inline_62">5</a> </div> <div class="TableFootnote"><a name="558969">Supported only on Oracle 9</a><span class="Emphasis">i</span> R2 and higher.</div> <br style="clear: all;" /> <div class="Body"><a name="558981">The Oracle driver does not support any object types (also known as abstract data types). </a>When the driver encounters an object type during data retrieval, it will return an Unknown Data Type error (SQL State HY000).</div> <div class="Body"><a name="558985">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="558987">XMLType</a></div> <div class="Body"><a name="608763">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="608764">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="608765">Examples</a></div> <div class="Body"><a name="608766">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="608767">SELECT </a><span class="EquationVariables">XML_col</span> FROM <span class="EquationVariables">table_name...</span></div> <div class="Body"><a name="608768"> instead of</a></div> <div class="syntax_first"><a name="608769">SELECT </a><span class="EquationVariables">XML_col</span>.getClobVal()...</div> <div class="Body"><a name="608770">The following example illustrates using the CLOB storage type:</a></div> <div class="syntax_first"><a name="608771">CREATE TABLE po_xml_tab(</a></div> <div class="syntax"><a name="608772"> </a> poid NUMBER(10),</div> <div class="syntax"><a name="608773"> </a> poDoc XMLTYPE</div> <div class="syntax"><a name="608774"> </a> )</div> <div class="syntax"><a name="608775"> </a> XMLType COLUMN poDoc</div> <div class="syntax"><a name="608776"> </a> STORE AS CLOB (</div> <div class="syntax"><a name="608777"> </a> TABLESPACE lob_seg_ts</div> <div class="syntax"><a name="608778"> </a> STORAGE (INITIAL 4096 NEXT 4096)</div> <div class="syntax"><a name="608779"> </a> CHUNK 4096 NOCACHE LOGGING</div> <div class="syntax"><a name="608780"> </a> );</div> <div class="Body"><a name="608781">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="608782">CREATE TABLE PURCHASEORDER (PODOCUMENT sys.XMLTYPE);</a></div> <div class="Body"><a name="608783">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="608784">INSERT INTO PURCHASEORDER (PODOCUMENT) values (</a></div> <div class="syntax"><a name="608785">sys.XMLTYPE.createXML(</a></div> <div class="syntax"><a name="608786">'</a></div> <div class="syntax"><a name="608787"><PurchaseOrder></a></div> <div class="syntax"><a name="608788"> </a> <Reference>BLAKE-2001062514034298PDT</Reference></div> <div class="syntax"><a name="608789"> </a></div> <div class="syntax"><a name="608790"> </a> <Actions></div> <div class="syntax"><a name="608791"> </a> <Action></div> <div class="syntax"><a name="608792"> </a> <User>KING</User></div> <div class="syntax"><a name="608793"> </a> <Date/></div> <div class="syntax"><a name="608794"> </a> </Action></div> <div class="syntax"><a name="608795"> </a> </Actions></div> <div class="syntax"><a name="608796"> </a> <Reject/></div> <div class="syntax"><a name="608797"> </a></div> <div class="syntax"><a name="608798"> </a> <Requester>David E. Blake</Requester></div> <div class="syntax"><a name="608799"> </a> <User>BLAKE</User></div> <div class="syntax"><a name="608800"> </a> <CostCenter>S30</CostCenter></div> <div class="syntax"><a name="608801"> </a> <ShippingInstructions></div> <div class="syntax"><a name="608802"> </a> <name>David E. Blake</name></div> <div class="syntax"><a name="608803"> </a> <address>400 Oracle Parkway Redwood Shores, CA, 94065 USA</address></div> <div class="syntax"><a name="608804"> </a> <telephone>650 999 9999</telephone></div> <div class="syntax"><a name="608805"> </a> </ShippingInstructions></div> <div class="syntax"><a name="608806"> </a></div> <div class="syntax"><a name="608807"> </a> <SpecialInstructions>Air Mail</SpecialInstructions></div> <div class="syntax"><a name="608808"> </a> <LineItems></div> <div class="syntax"><a name="608809"> </a> <LineItem ItemNumber="1"></div> <div class="syntax"><a name="608810"> </a> <Description>The Birth of a Nation</Description></div> <div class="syntax"><a name="608811"> </a> <Part Id="EE888" UnitPrice="65.39" Quantity="31"/></div> <div class="syntax"><a name="608812"> </a> </LineItem></div> <div class="syntax"><a name="608813"> </a> </LineItems></div> <div class="syntax"><a name="608814"></PurchaseOrder></a></div> <div class="syntax"><a name="608815">'));</a></div> <div class="Body"><a name="608816">Use the getClobVal function to retrieve the data:</a></div> <div class="syntax_first"><a name="608817">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