Data Types

Table 13-2 shows how the Oracle data types are mapped to the standard ODBC data types. “Unicode Support” lists Oracle to Unicode data type mappings.

Table 13-2. Oracle Data Types 

Oracle	ODBC
BFILE 1	SQL_LONGVARBINARY
BINARY DOUBLE 2	SQL_REAL
BINARY FLOAT 2	SQL_DOUBLE
BLOB 3	SQL_LONGVARBINARY
CHAR	SQL_CHAR
CLOB 3	SQL_LONGVARCHAR
DATE	SQL_TYPE_TIMESTAMP
LONG	SQL_LONGVARCHAR
LONG RAW	SQL_LONGVARBINARY
NUMBER	SQL_DOUBLE
NUMBER (p,s)	SQL_DECIMAL
RAW	SQL_VARBINARY
TIMESTAMP 4	SQL_TIMESTAMP
TIMESTAMP WITH LOCAL TIMEZONE 4	SQL_TIMESTAMP
TIMESTAMP WITH TIMEZONE 4	SQL_VARCHAR
VARCHAR2	SQL_VARCHAR
XMLType 5	SQL_LONGVARCHAR

---

1

Read-Only

2

Supported only on Oracle 10g and higher.

3

Valid when connecting to Oracle 8 servers; these data types support output parameters to stored procedures

4

Supported only on Oracle 9i and higher.

5

Supported only on Oracle 9i R2 and higher.

The Oracle driver does not support any object types (also known as abstract data types). When the driver encounters an object type during data retrieval, it will return an Unknown Data Type error (SQL State HY000).

See “Retrieving Data Type Information” for more information about data types.

XMLType

Oracle 9i R2 and higher supports the XMLType data type. The driver supports tables containing columns whose data type is specified as XMLType.

When inserting or updating XMLType columns, the data to be inserted or updated must be 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.

Examples

If the XMLType column is created with the CLOB storage type, then the driver returns it without use of the special getClobVal function, that is, you can use:

SELECT XML_col FROM table_name...

instead of

SELECT XML_col.getClobVal()...

The following example illustrates using the CLOB storage type:

CREATE TABLE po_xml_tab(

  poid NUMBER(10),

  poDoc XMLTYPE

  )

  XMLType COLUMN poDoc

    STORE AS CLOB (

      TABLESPACE lob_seg_ts

      STORAGE (INITIAL 4096 NEXT 4096)

      CHUNK 4096 NOCACHE LOGGING

    );

The next example illustrates how to create a table, insert data, and retrieve data when not using the CLOB storage type:

CREATE TABLE PURCHASEORDER (PODOCUMENT sys.XMLTYPE);

The PURCHASEORDER table contains one column—PODOCUMENT—with a data type of XMLType (sys.XMLTYPE). The next step is to insert one purchase order, created by the static function sys.XMLTYPE.createXML:

INSERT INTO PURCHASEORDER (PODOCUMENT) values (

sys.XMLTYPE.createXML(

'

<PurchaseOrder>

   <Reference>BLAKE-2001062514034298PDT</Reference>

 

   <Actions>

      <Action>

         <User>KING</User>

         <Date/>

      </Action>

   </Actions>

   <Reject/>

 

   <Requester>David E. Blake</Requester>

   <User>BLAKE</User>

   <CostCenter>S30</CostCenter>

   <ShippingInstructions>

      <name>David E. Blake</name>

      <address>400 Oracle Parkway Redwood Shores, CA, 94065 USA</address>

      <telephone>650 999 9999</telephone>

    </ShippingInstructions>

 

   <SpecialInstructions>Air Mail</SpecialInstructions>

   <LineItems>

      <LineItem ItemNumber="1">

         <Description>The Birth of a Nation</Description>

         <Part Id="EE888" UnitPrice="65.39" Quantity="31"/>

      </LineItem>

   </LineItems>

</PurchaseOrder>

'));

Use the getClobVal function to retrieve the data:

SELECT p.podocument.getClobVal() FROM PURCHASEORDER p;