This chapter is a reference for Oracle datatypes used by Oracle C++ Interface applications. This information will help you understand the conversions between internal and external representations of data that occur when you transfer data between your application and the database server.
This chapter contains these topics:
Accurate communication between your C++ program and the Oracle database server is critical. OCCI applications can retrieve data from database tables by using SQL queries or they can modify existing data through the use of SQL INSERT
, UPDATE
, and DELETE
functions. To facilitate communication between the host language C++ and the database server, you must be aware of how C++ datatypes are converted to Oracle datatypes and back again.
In the Oracle database, values are stored in columns in tables. Internally, Oracle represents data in particular formats called internal datatypes. NUMBER
, VARCHAR2
, and DATE
are examples of Oracle internal datatypes.
OCCI applications work with host language datatypes, or external datatypes, predefined by the host language. When data is transferred between an OCCI application and the database server, the data from the database is converted from internal datatypes to external datatypes.
OCCI defines an enumerator called Type
that lists the possible data representation formats available in an OCCI application. These representation formats are called external datatypes. When data is sent to the database server from the OCCI application, the external datatype indicates to the database server what format to expect the data. When data is requested from the database server by the OCCI application, the external datatype indicates the format of the data to be returned.
For example, on retrieving a value from a NUMBER
column, the program may be set to retrieve it in OCCIINT
format (a signed integer format into an integer variable). Or, the client might be set to send data in OCCIFLOAT
format (floating-point format) stored in a C++ float variable to be inserted in a column of NUMBER
type.
An OCCI application binds input parameters to a Statement
, by calling a set
xxx
()
method (the external datatype
is implicitly specified by the method name), or by calling the registerOutParam()
, setDataBuffer()
, or setDataBufferArray()
method (the external datatype is explicitly specified in the method call). Similarly, when data values are fetched through a ResultSet
object, the external representation of the retrieved data must be specified. This is done by calling a get
xxx
()
method (the external datatype
is implicitly specified by the method name) or by calling the setDataBuffer()
method (the external datatype is explicitly specified in the method call).
Note:
There are more external datatypes than internal datatypes. In some cases, a single external datatype maps to a single internal datatype; in other cases, many external datatypes map to a single internal datatype. The many-to-one mapping provides you with added flexibility.See Also:
External DatatypesThe internal (built-in) datatypes provided by Oracle are listed in this section. A brief summary of internal Oracle datatypes, including description, code, and maximum size, appears in Table 5-1.
Table 5-1 Summary of Oracle Internal Datatypes
Internal Datatype | Maximum Size |
---|---|
BFILE |
4 gigabytes |
BINARY_DOUBLE |
8 bytes |
BINARY_FLOAT |
4 bytes |
CHAR |
2,000 bytes |
DATE |
7 bytes |
INTERVAL DAY TO SECOND REF |
11 bytes |
INTERVAL YEAR TO MONTH REF |
5 bytes |
LONG |
2 gigabytes (2^31-1 bytes) |
LONG RAW |
2 gigabytes (2^31-1 bytes) |
NCHAR |
2,000 bytes |
NUMBER |
21 bytes |
NVARCHAR2 |
4,000 bytes |
RAW |
2,000 bytes |
REF |
|
BLOB |
4 gigabytes |
CLOB |
4 gigabytes |
NCLOB |
4 gigabytes |
ROWID |
10 bytes |
TIMESTAMP |
11 bytes |
TIMESTAMP WITH LOCAL TIME ZONE |
7 bytes |
TIMESTAMP WITH TIME ZONE |
13 bytes |
UROWID |
4000 bytes |
User-defined type (object type, |
|
VARCHAR2 |
4,000 bytes |
You can use five Oracle internal datatypes to specify columns that contain either characters or arrays of bytes: CHAR
, VARCHAR2
, RAW
, LONG
, and LONG
RAW
.
CHAR
, VARCHAR2
, and LONG
columns normally hold character data. RAW
and LONG
RAW
hold bytes that are not interpreted as characters, for example, pixel values in a bitmapped graphics image. Character data can be transformed when passed through a gateway between networks. For example, character data passed between machines by using different languages (where single characters may be represented by differing numbers of bytes) can be significantly changed in length. Raw data is never converted in this way.
The database designer is responsible for choosing the appropriate Oracle internal datatype for each column in a table. You must be aware of the many possible ways that character and byte-array data can be represented and converted between variables in the OCCI program and Oracle database tables.
The universal rowid (UROWID
) is a datatype that can store both the logical and the physical rowid of rows in Oracle tables and in foreign tables, such as DB2 tables accessed through a gateway. Logical rowid
values are primary key-based logical identifiers for the rows of index organized tables.
To use columns of the UROWID
datatype, the value of the COMPATIBLE
initialization parameter must be set to 8.1
or higher.
The following OCCI_SQLT
types can be bound to universal rowid
s:
OCCI_SQLT_CHR
(VARCHAR2
)
OCCI_SQLT_VCS
(VARCHAR
)
OCCI_SQLT_STR
(NULL
terminated string)
OCCI_SQLT_LVC
(long VARCHAR
)
OCCI_SQLT_AFC
(CHAR)
OCCI_SQLT_AVC
(CHARZ
)
OCCI_SQLT_VST
(string)
OCCI_SQLT_RDD
(ROWID
descriptor)
Communication between the host OCCI application and the Oracle database server is through the use of external datatypes. Specifically, external datatypes are mapped to C++ datatypes.
Table 5-2 lists the Oracle external datatypes, the C++ equivalent (what the Oracle internal datatype is usually converted to), and the corresponding OCCI type. Note the following conditions:
In C++ Datatype column, n
stands for variable length and depends on program requirements or operating system.
The usage of types in Statement
class methods is as follows:
setDataBuffer()
and setDataBufferArray()
: Only types of the form OCCI_SQLT_
xxx
(for example, OCCI_SQLT_INT
) in the occiCommon.h
file are permitted. All types used with these methods are marked with an asterisk, *.
registerOutParam()
: Only types of the form OCCI
xxx
(for example, OCCIDOUBLE
, OCCICURSOR
, and so on) on the occiCommon.h
file are permitted. However, there are some exceptions: OCCIANYDATA
, OCCIMETADATA
, OCCISTREAM
, and OCCIBOOL
are not permitted. All types used with this method are marked with a double asterisk, **.
In the ResultSet
class, only types of the form OCCI_SQLT_
xxx
(for example, OCCI_SQLT_INT
) in the occiCommon.h
file are permitted for use in setDataBuffer()
and setDataBufferArray()
methods. These types are marked with an asterisk, *.
The TIMESTAMP
and TIMESTAMP
WITH
TIME
ZONE
datatypes are collectively known as datetimes. The INTERVAL
YEAR
TO
MONTH
and INTERVAL
DAY
TO
SECOND
are collectively known as intervals.
Table 5-2 External Datatypes and Corresponding C++ and OCCI Types
External Datatype | C++ Type | OCCI Type |
---|---|---|
16 bit signed |
signed short, signed int |
OCCIINT |
32 bit signed |
signed int, signed long |
OCCIINT |
8 bit signed |
signed char |
OCCIINT |
|
Bfile |
OCCIBFILE |
Binary |
OCILobLocator |
OCCI_SQLT_FILE |
Binary |
OCILobLocator |
OCCI_SQLT_BLOB |
|
Blob |
OCCIBLOB |
|
bool |
OCCIBOOL |
|
Bytes |
OCCIBYTES |
|
char[n] |
OCCI_SQLT_AFC |
|
string |
OCCICHAR |
Character |
OCILobLocator |
OCCI_SQLT_CLOB |
|
char[n+1] |
OCCI_SQLT_RDD |
|
Clob |
OCCICLOB |
|
ResultSet |
OCCICURSOR |
|
char[7] |
OCCI_SQLT_DAT |
|
Date |
OCCIDATE |
|
double |
OCCIDOUBLE |
|
float, double |
OCCIFLOAT |
|
float |
OCCIFLOAT |
|
int |
OCCIINT |
|
char[11] |
OCCI_SQLT_INTERVAL_DS |
|
char[5] |
OCCI_SQLT_INTERVAL_YM |
|
IntervalDS |
OCCIINTERVALDS |
|
IntervalYM |
OCCIINTERVALYM |
|
char[n] |
OCCI_SQLT_LNG |
|
unsigned char[n] |
OCCI_SQLT_LBI |
|
char[n+siezeof(integer)] |
OCCI_SQLT_LVC |
|
unsigned char[n+siezeof(integer)] |
OCCI_SQLT_LVB |
|
MetaData |
OCCIMETADATA |
|
struct |
OCCI_SQLT_NTY |
|
double |
OCCIBDOUBLE |
|
Bdouble, double |
OCCIBDOUBLE |
|
float |
OCCIBFLOAT |
|
BFloat, float |
OCCIBFLOAT |
null terminated |
char[n+1] |
OCCI_SQLT_STR |
|
unsigned char[21] |
OCCI_SQLT_NUM |
|
Number |
OCCINUMBER |
|
User defined types generated by OTT utility. |
OCCIPOBJECT |
|
unsigned char[n] |
OCCI_SQLT_BIN |
|
OCIRef |
OCCI_SQLT_REF |
|
Ref |
OCCIREF |
|
RefAny |
OCCIREFANY |
|
OCIRowid |
OCCI_SQLT_RID |
|
Bytes |
OCCIROWID |
|
OCIRowid |
OCCI_SQLT_RDD |
|
STL string |
OCCISTRING |
|
char[11] |
OCCI_SQLT_TIMESTAMP |
|
Timestamp |
OCCITIMESTAMP |
|
char[7] |
OCCI_SQLT_TIMESTAMP_LTZ |
|
char[13] |
OCCI_SQLT_TIMESTAMP_TZ |
|
unsigned int |
OCCIUNSIGNED_INT |
|
unsigned int |
OCCIUNSIGNED_INT |
|
char[n+sizeof( short integer)] |
OCCI_SQLT_VCS |
|
char[n] |
OCCI_SQLT_CHR |
|
char[22] |
OCCI_SQLT_VNU |
|
unsigned char[n+sizeof( short integer)] |
OCCI_SQLT_VBI |
|
STL vector |
OCCIVECTOR |
This section provides a description for each of the external datatypes.
The external datatype BFILE
allows read-only byte stream access to large files on the file system of the database server. A BFILE
is a large binary data object stored in operating system files outside database tablespaces. These files use reference semantics. The Oracle server can access a BFILE
provided the underlying server operating system supports stream-mode access to these operating system files.
The BDouble
interface in OCCI encapsulates the native double data and the NULL
information of a column or object attribute of the type binary_double
. The OCCI methods in AnyData Class, ResultSet Class and Statement Class, and the global methods that take these class objects as parameters, use the following definition for the BDOUBLE
datatype:
The BFloat
interface in OCCI encapsulates the native float data and the NULL
information of a column or object attribute of the type binary_float
. The OCCI methods in AnyData Class, ResultSet Class and Statement Class, and the global methods that take these class objects as parameters, use the following definition for the BFLOAT
datatype:
The external datatype BLOB
stores unstructured binary large objects. A BLOB
can be thought of as a bitstream with no character set semantics. BLOB
s can store up to 4
gigabytes of binary data.
BLOB
datatypes have full transactional support. Changes made through OCCI participate fully in the transaction. BLOB
value manipulations can be committed or rolled back. You cannot save a BLOB
locator in a variable in one transaction and then use it in another transaction or session.
The external datatype CHAR
is a string of characters, with a maximum length of 2000
characters. Character strings are compared by using blank-padded comparison semantics.
The external datatype CHARZ
is similar to the CHAR
datatype, except that the string must be NULL
terminated on input, and Oracle places a NULL
terminator character at the end of the string on output. The NULL
terminator serves only to delimit the string on input or output. It is not part of the data in the table.
The external datatype CLOB
stores fixed-width or varying-width character data. A CLOB
can store up to 4
gigabytes of character data. CLOB
s have full transactional support. Changes made through OCCI participate fully in the transaction. CLOB
value manipulations can be committed or rolled back. You cannot save a CLOB
locator in a variable in one transaction and then use it in another transaction or session.
The external datatype DATE
can update, insert, or retrieve a date value using the Oracle internal seven byte date binary format, as listed in Table 5-3:
Table 5-3 Format of the DATE Datatype
Example | Byte 1Century | Byte 2Year | Byte 3Month | Byte 4Day | Byte 5Hour | Byte 6Minute | Byte 7Second |
---|---|---|---|---|---|---|---|
1: 01-JUN-2000, 3:17PM |
120 |
100 |
6 |
1 |
16 |
18 |
1 |
2: 01-JAN-4712 BCE |
53 |
88 |
1 |
1 |
1 |
1 |
1 |
The century and year bytes (1 and 2) are in excess-100
notation. Dates BCE (Before Common Era) are less than 100
. Dates in the Common Era (CE), 0 and after, are greater than 100
. For dates 0 and after, the first digit of both bytes 1 and 2 merely signifies that it is of the CE.
For byte 1, the second and third digits of the century are calculated as the year (an integer) divided by 100. With integer division, the fractional portion is discarded. The following calculation is for the year 1992: 1992 / 100 = 19
.
For byte 1, 119
represents the twentieth century, 1900 to 1999. A value of 120
would represent the twenty-first century, 2000 to 2099.
For byte 2, the second and third digits of the year are calculated as the year modulo 100: 1992 % 100 = 92
.
For byte 2, 192
represents the ninety-second year of the current century. A value of 100
would represent the zeroth year of the current century.
The year 2000 would yield 120
for byte 1 and 100
for byte 2.
For bytes 3 through 7, valid dates begin at 01-JAN of the year. The month byte ranges from 1
to 12
, the date byte ranges from 1
to 31
, the hour byte ranges from 1
to 24
, the minute byte ranges from 1
to 60
, and the second byte ranges from 1
to 60
.
For years prior to 0 CE, centuries and years are represented by the difference between 100 and the number.
For byte 1, 01-JAN-4712 BCE is century 53:
100 - 47 = 53.
For byte 2, 01-JAN-4712 BCE is year 88
: 100 - 12 = 88
.
Notes:
If no time is specified for a date, the time defaults to midnight and bytes 5 through 6 are set to 1
: 1
, 1
, 1
.
When you enter a date in binary format by using the external datatype DATE
, the database does not perform consistency or range checking. All data in this format must be validated before input.
There is little need for the external datatype DATE
. It is more convenient to convert DATE
values to a character format, because most programs deal with dates in a character format, such as DD-MON-YYYY. Instead, you may use the Date
datatype.
When a DATE
column is converted to a character string in your program, it is returned in the default format mask for your session, or as specified in the INIT.ORA
file.
This datatype is different from OCCI DATE
which corresponds to a C++ Date
datatype.
The external datatype FLOAT
processes numbers with fractional parts. The number is represented in the host system's floating-point format. Normally, the length is 4 or 8 bytes.
The internal format of an Oracle number is decimal. Most floating-point implementations are binary. Oracle, therefore, represents numbers with greater precision than floating-point representations.
The external datatype INTEGER
is used for converting numbers. An external integer is a signed binary number. Its size is operating system-dependent. If the number being returned from Oracle is not an integer, then the fractional part is discarded, and no error is returned. If the number returned exceeds the capacity of a signed integer for the system, then Oracle returns an overflow on conversion error.
Note:
A rounding error may occur when converting betweenFLOAT
and NUMBER
. Using a FLOAT
as a bind variable in a query may return an error. You can work around this by converting the FLOAT
to a string and using the OCCI type OCCI_SQLT_CHR
or the OCCI type OCCI_SQLT_STR
for the operation.The external datatype INTERVAL
DAY
TO
SECOND
stores the difference between two datetime values in terms of days, hours, minutes, and seconds. Specify this datatype as follows:
INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
This example uses the following placeholders:
day_precision
: Number of digits in the DAY
datetime field. Accepted values are 1
to 9
. The default is 2
.
fractional_seconds_precision
: Number of digits in the fractional part of the SECOND
datetime field. Accepted values are 0
to 9
. The default is 6
.
To specify an INTERVAL
DAY
TO
SECOND
literal with nondefault day and second precisions, you must specify the precisions in the literal. For example, you might specify an interval of 100
days, 10
hours, 20
minutes, 42
seconds, and 22
hundredths of a second as follows:
INTERVAL '100 10:20:42.22' DAY(3) TO SECOND(2)
You can also use abbreviated forms of the INTERVAL
DAY
TO
SECOND
literal. For example:
INTERVAL '90' MINUTE
maps to INTERVAL '00 00:90:00.00' DAY TO SECOND(2)
INTERVAL '30:30' HOUR TO MINUTE
maps to INTERVAL '00 30:30:00.00' DAY TO SECOND(2)
INTERVAL '30' SECOND(2,2)
maps to INTERVAL '00 00:00:30.00' DAY TO SECOND(2)
The external datatype INTERVAL
YEAR
TO
MONTH
stores the difference between two datetime values by using the YEAR
and MONTH
datetime fields. Specify INTERVAL
YEAR
TO
MONTH
as follows:
INTERVAL YEAR [(year_precision)] TO MONTH
The placeholder year_precision
is the number of digits in the YEAR
datetime field. The default value of year_precision
is 2
. To specify an INTERVAL
YEAR
TO
MONTH
literal with a nondefault year_precision
, you must specify the precision in the literal. For example, the following INTERVAL
YEAR
TO
MONTH
literal indicates an interval of 123
years, 2
months:
INTERVAL '123-2' YEAR(3) TO MONTH
You can also use abbreviated forms of the INTERVAL
YEAR
TO
MONTH
literal. For example,
INTERVAL '10' MONTH
maps to INTERVAL '0-10' YEAR TO MONTH
INTERVAL '123' YEAR(3)
maps to INTERVAL '123-0' YEAR(3) TO MONTH
The external datatype LONG
stores character strings longer than 4000 bytes and up to 2
gigabytes in a column of datatype LONG
. Columns of this type are only used for storage and retrieval of long strings. They cannot be used in methods, expressions, or WHERE
clauses. LONG
column values are generally converted to and from character strings.
The external datatype LONG RAW
is similar to the external datatype RAW
, except that it stores up to 2
gigabytes.
The external datatype LONG VARCHAR
stores data from and into an Oracle LONG
column. The first four bytes contain the length of the item. The maximum length of a LONG
VARCHAR
is 2
gigabytes.
The external datatype LONG VARRAW
store data from and into an Oracle LONG
RAW
column. The length is contained in the first four bytes. The maximum length is 2
gigabytes.
The external datatype NCLOB
is a national character version of a CLOB
. It stores fixed-width, multibyte national character set character (NCHAR
), or varying-width character set data. An NCLOB
can store up to 4 gigabytes of character text data.
NCLOB
s have full transactional support. Changes made through OCCI participate fully in the transaction. NCLOB
value manipulations can be committed or rolled back. You cannot save an NCLOB
locator in a variable in one transaction and then use it in another transaction or session.
You cannot create an object with NCLOB
attributes, but you can specify NCLOB
parameters in methods.
You should not need to use NUMBER
as an external datatype. If you do use it, Oracle returns numeric values in its internal 21
-byte binary format and will expect this format on input. The following discussion is included for completeness only.
Oracle stores values of the NUMBER
datatype in a variable-length format. The first byte is the exponent and is followed by 1
to 20
mantissa bytes. The high-order bit of the exponent byte is the sign bit; it is set for positive numbers and it is cleared for negative numbers. The lower 7
bits represent the exponent, which is a base-100
digit with an offset of 65
.
To calculate the decimal exponent, add 65
to the base-100
exponent and add another 128
if the number is positive. If the number is negative, you do the same, but subsequently the bits are inverted. For example, -5
has a base-100
exponent =
62 (0x3e)
. The decimal exponent is thus (~0x3e)-128-65 = 0xc1-128-65 = 193-128-65 = 0
.
Each mantissa byte is a base-100
digit, in the range 1
to 100
. For positive numbers, the digit has 1
added to it. So, the mantissa digit for the value 5
is 6
. For negative numbers, instead of adding 1, the digit is subtracted from 101
. So, the mantissa digit for the number -5
is: 101-5 = 96
. Negative numbers have a byte containing 102
appended to the data bytes. However, negative numbers that have 20
mantissa bytes do not have the trailing 102
byte. Because the mantissa digits are stored in base-100,
each byte can represent two decimal digits. The mantissa is normalized; leading zeroes are not stored.
Up to 20
data bytes can represent the mantissa. However, only 19
are guaranteed to be accurate. The 19
data bytes, each representing a base-100
digit, yield a maximum precision of 38
digits for an internal datatype NUMBER
.
Note that this datatype is different from OCCI NUMBER
which corresponds to a C++ Number
datatype.
The external datatype OCCI VECTOR
is used to represent collections, for example, a nested table or VARRAY
. CREATE TYPE num_type as VARRAY OF NUMBER(10)
can be represented in a C++ application as vector<int>
, vector<Number>
, and so on.
The external datatype RAW
is used for binary data or byte strings that are not to be interpreted or processed by Oracle. RAW
could be used, for example, for graphics character sequences. The maximum length of a RAW
column is 2000 bytes.
When RAW
data in an Oracle table is converted to a character string, the data is represented in hexadecimal code. Each byte of RAW
data is represented as two characters that indicate the value of the byte, ranging from 00 to FF. If you input a character string by using RAW
, then you must use hexadecimal coding.
The external datatype REF
is a reference to a named datatype. To allocate a REF
for use in an application, declare a variable as a pointer to a REF
.
The external datatype ROWID
identifies a particular row in a database table. The ROWID
is often returned from a query by issuing a statement similar to the following example:
SELECT ROWID, var1, var2 FROM db;
You can then use the returned ROWID
in further DELETE
statements.
If you are performing a SELECT
for an UPDATE
operation, then the ROWID
is implicitly returned.
The external datatype STRING
behaves like the external datatype VARCHAR2
(datatype code 1), except that the external datatype STRING
must be NULL
-terminated.
Note that this datatype is different from OCCI STRING
which corresponds to a C++ STL string datatype.
The external datatype TIMESTAMP
is an extension of the DATE
datatype. It stores the year, month, and day of the DATE
datatype, plus hour, minute, and second values. Specify the TIMESTAMP
datatype as follows:
TIMESTAMP [(fractional_seconds_precision)]
The placeholder fractional_seconds_precision
optionally specifies the number of digits in the fractional part of the SECOND
datetime field and can be a number in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP(2)
as a literal as follows:
TIMESTAMP '1997-01-31 09:26:50.10'
Note that this datatype is different from OCCI TIMESTAMP
.
The external datatype TIMESTAMP
WITH
TIME
ZONE
(TSTZ
) is a variant of TIMESTAMP
that includes an explicit time zone displacement in its value. The time zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC), formerly Greenwich Mean Time. Specify the TIMESTAMP
WITH
TIME
ZONE
datatype as follows:
TIMESTAMP(fractional_seconds_precision) WITH TIME ZONE
The placeholder fractional_seconds_precision
optionally specifies the number of digits in the fractional part of the SECOND
datetime field and can be a number in the range 0
to 9
. The default is 6
.
Two TIMESTAMP
WITH
TIME
ZONE
values are considered identical if they represent the same instant in UTC, regardless of the TIME
ZONE
offsets stored in the data.
The external datatype TIMESTAMP
WITH
TIME
ZONE
is a variant of TIMESTAMP
that includes a time zone displacement in its value. The time zone displacement is the difference (in hours and minutes) between local time and Coordinated Universal Time (UTC), formerly Greenwich Mean Time. Specify the TIMESTAMP
WITH
TIME
ZONE
datatype as follows:
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
The placeholder fractional_seconds_precision
optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0
to 9
. The default is 6
. For example, you might specify TIMESTAMP(0)
WITH
TIME
ZONE
as a literal as follows:
TIMESTAMP '1997-01-31 09:26:50+02.00'
The external datatype UNSIGNED INT
is used for unsigned binary integers. The size in bytes is operating system dependent. The host system architecture determines the order of the bytes in a word. If the number being output from Oracle is not an integer, the fractional part is discarded, and no error is returned. If the number to be returned exceeds the capacity of an unsigned integer for the operating system, Oracle returns an overflow on conversion error.
The external datatype VARCHAR
store character strings of varying length. The first two bytes contain the length of the character string, and the remaining bytes contain the actual string. The specified length of the string in a bind or a define call must include the two length bytes, meaning the largest VARCHAR
string is 65533
bytes long, not 65535
. For converting longer strings, use the LONG
VARCHAR
external datatype.
The external datatype VARNUM
is similar to the external datatype NUMBER
, except that the first byte contains the length of the number representation. This length value does not include the length byte itself. Reserve 22 bytes to receive the longest possible VARNUM
. You must set the length byte when you send a VARNUM
value to the database.
The external datatype VARRAW
is similar to the external datatype RAW
, except that the first two bytes contain the length of the data. The specified length of the string in a bind or a define call must include the two length bytes. So the largest VARRAW
string that can be received or sent is 65533
bytes, not 65535
. For converting longer strings, use the LONG
VARRAW
datatype.
This external datatype implements the IEEE 754 standard double-precision floating point datatype. It is represented in the host system's native floating point format. The datatype is stored in the Oracle Server in a byte comparable canonical format, and requires 8 bytes for storage, including the length byte. It is an alternative to Oracle NUMBER
and has the following advantages over NUMBER
:
Fewer bytes used in storage
Matches datatypes used by RDBMS Clients
Supports a wider range of values used in scientific calculations.
This external datatype implements the IEEE 754 single-precision floating point datatype. It is represented in the host system's native floating point format. The datatype is stored in the Oracle Server in a byte comparable canonical format, and requires 4 bytes for storage, including the length byte. It is an alternative to Oracle NUMBER
and has the following advantages over NUMBER
:
Fewer bytes used in storage
Matches datatypes used by RDBMS Clients
Supports a wider range of values used in scientific calculations
Table 5-5 lists the supported conversions from Oracle internal datatypes to external datatypes, and from external datatypes to internal column representations. Note the following conditions:
A REF
stored in the database is converted to OCCI_SQLT_REF
on output
OCCI_SQLT_REF
is converted to the internal representation of a REF
on input
A named datatype stored in the database is converted to OCCI_SQLT_NTY
(and represented by a C structure in the application) on output
OCCI_SQLT_NTY
(represented by a C structure in an application) is converted to the internal representation of the corresponding datatype on input
A LOB
and a BFILE
are represented by descriptors in OCCI applications, so there are no input or output conversions
Table 5-5 Data Conversions Between External and Internal datatypes
Internal Datatypes | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
External Datatypes | VARCHAR2 | NUMBER | LONG | ROWID | DATE | RAW | LONG RAW | CHAR | BFLOAT | BDOUBLE |
|
I/O |
I/O |
I/O |
I/O1 |
I/O2 |
I/O3 |
I3, 5 |
I/O |
I/O |
I/O |
|
I/O |
I/O |
I/O |
I/O1 |
I/O2 |
I/O3 |
I3, 5 |
I/O |
- |
- |
|
I/O |
- |
I |
- |
I/O |
- |
- |
I/O |
- |
- |
|
I/O4 |
I/O |
I |
- |
- |
- |
- |
I/O4 |
- |
- |
|
I/O4 |
I/O |
I |
- |
- |
- |
- |
I/O4 |
I/O |
I/O |
|
I/O4 |
I/O |
I |
- |
- |
- |
- |
I/O4 |
I/O |
I/O |
|
I/O |
I/O |
I/O |
I/O1 |
I/O2 |
I/O3 |
I/O3, 5 |
I/O |
I/O |
II/O |
|
O6 |
- |
I5, 6 |
- |
- |
I/O |
I/O |
O6 |
- |
- |
|
I/O |
I/O |
I/O |
I/O1 |
I/O2 |
I/O3 |
I/O3, 5 |
I/O |
I/O |
I/O |
|
I/O6 |
- |
I5, 6 |
- |
- |
I/O |
I/O |
I/O6 |
- |
- |
|
I/O4 |
I/O |
I |
- |
- |
- |
- |
I/O4 |
I/O |
I/O |
OCCI BDouble |
I/O |
1/O |
I |
- |
- |
- |
- |
I/O |
I/O |
I/O |
OCCI BFloat |
I/O |
1/O |
I |
- |
- |
- |
- |
I/O |
I/O |
I/O |
|
I/O6 |
- |
I5, 6 |
- |
- |
I/O |
I/O |
I/O6 |
- |
- |
|
I/O |
- |
I |
- |
I/O |
- |
- |
I/O |
- |
- |
|
I/O4 |
I/O |
I |
- |
- |
- |
- |
I/O4 |
I/O |
I/O |
|
- |
- |
- |
- |
- |
- |
- |
- |
- |
- |
|
I/O6 |
- |
I5, 6 |
- |
- |
I/O |
I/O |
I/O6 |
- |
- |
|
I |
- |
I |
I/O |
- |
- |
- |
I |
- |
- |
|
I/O |
I/O |
I/O |
I/O1 |
I/O2 |
I/O3 |
I/O3 |
- |
I/O4 |
I/O4 |
|
I/O |
I/O |
I/O |
I/O1 |
I/O2 |
I/O3 |
I/O3, 5 |
I/O |
I/O |
I/O |
|
I/O4 |
I/O |
I |
- |
- |
- |
- |
I/O4 |
I/O |
I/O |
|
I/O |
I/O |
I/O |
I/O1 |
I/O2 |
I/O3 |
I/O3 |
- |
I/O |
I/O |
|
I/O |
I/O |
I/O |
I/O1 |
I/O2 |
I/O3 |
I/O3, 5 |
I/O |
I/O |
I/O |
|
I/O4 |
I/O |
I |
- |
- |
- |
- |
I/O4 |
I/O |
I/O |
|
I/O6 |
- |
I5, 6 |
- |
- |
I/O |
I/O |
I/O6 |
- |
- |
Note:
Conversions valid for I (Input only), O (Output Only), I/O (Input or Output)Must be in Oracle ROWID format for input; returned in Oracle ROWID format on output.
Must be in Oracle DATE format for input; returned in Oracle DATE format on output.
Must be in hexadecimal format for input; returned in hexadecimal format on output.
Must represent a valid number for output.
Length must be less than or equal to 2000 characters.
Stored in hexadecimal format on output; must be in hexadecimal format on output.
Table 5-6 Data Conversions for LOBs
EXTERNAL DATATYPES | INTERNAL DATATYPES | |
---|---|---|
CLOB | BLOB | |
|
I/O |
- |
|
I/O |
- |
|
I/O |
- |
|
I/O |
- |
|
I/O |
- |
|
- |
I/O |
|
- |
I/O |
|
- |
I/O |
|
- |
I/O |
|
- |
I/O |
See Also:
Oracle Database Application Developer's Guide - Large Objects for an introduction toLOB
datatypes.You can also use one of the character data types for the host variable used in a fetch or insert operation from or to a datetime or interval column. Oracle will do the conversion between the character data type and datetime/interval data type for you.
Table 5-7 Data Conversions for Date, Timestamp, and Interval Datatypes
External Types | Internal Types | ||||||
---|---|---|---|---|---|---|---|
|
VARCHAR, CHAR | DATE | TS | TSTZ | TSLTZ | INTERVAL YEAR TO MONTH | INTERVAL DAY TO SECOND |
|
I/O |
I/O |
I/O |
I/O |
I/O |
I/O |
I/O |
|
I/O |
I/O |
I/O |
I/O |
I/O |
I/O |
I/O |
|
I/O |
I/O |
I/O |
I/O |
I/O |
- |
- |
|
I/O |
I/O |
I/O |
I/O |
I/O |
- |
- |
|
I/O |
I/O |
I/O |
I/O |
I/O |
- |
- |
|
I/O |
I/O |
I/O |
I/O |
I/O |
- |
- |
|
I/O |
I/O |
I/O |
I/O |
I/O |
- |
- |
|
I/O |
I/O |
I/O |
I/O |
I/O |
- |
- |
|
I/O |
I/O |
I/O |
I/O |
I/O |
- |
- |
|
I/O |
- |
- |
- |
- |
I/O |
- |
|
I/O |
- |
- |
- |
- |
I/O |
- |
|
I/O |
- |
- |
- |
- |
- |
I/O |
|
I/O |
- |
- |
- |
- |
- |
I/O |
These consideration apply when converting between Date, Timestamp and Interval datatypes:
When assigning a source with time zone to a target without a time zone, the time zone portion of the source is ignored. On assigning a source without a time zone to a target with a time zone, the time zone of the target is set to the session's default time zone.
When assigning an Oracle DATE
to a TIMESTAMP
, the TIME
portion of the DATE
is copied over to the TIMESTAMP
. When assigning a TIMESTAMP
to Oracle DATE
, the TIME
portion of the result DATE
is set to zero. This is done to encourage migration of Oracle DATE
to ANSI
compliant DATETIME
data types.
(When assigning an ANSI
DATE
to an Oracle DATE
or a TIMESTAMP
, the TIME
portion of the Oracle DATE
and the TIMESTAMP
are set to zero. When assigning an Oracle DATE
or a TIMESTAMP
to an ANSI DATE
, the TIME
portion is ignored.
When assigning a DATETIME
to a character string, the DATETIME
is converted using the session's default DATETIME
format. When assigning a character string to a DATETIME
, the string must contain a valid DATETIME
value based on the session's default DATETIME
format.
When assigning a character string to an INTERVAL
, the character string must be a valid INTERVAL
character format.
When converting from TSLTZ
to CHAR
, DATE
, TIMESTAMP
and TSTZ
, the value will be adjusted to the session time zone.
When converting from CHAR
, DATE
, and TIMESTAMP
to TSLTZ
, the session time zone will be stored in memory.
When assigning TSLTZ
to ANSI
DATE
, the time portion will be 0
.
When converting from TSTZ
, the time zone which the time stamp is in will be stored in memory.
When assigning a character string to an interval, the character string must be a valid interval character format.