Release 9.0.1
Part Number A89857-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle Call Interface Programmer's Guide Release 9.0.1 Part Number A89857-01 |
|
This chapter provides a reference to Oracle external datatypes used by OCI applications. It also provides a general discussion of Oracle datatypes, including special datatypes new in the latest Oracle release. The information in this chapter is useful for understanding the conversions between internal and external representations that occur when you transfer data between your program and Oracle. This chapter contains the following sections:
One of the main functions of an OCI program is to communicate with a database through an Oracle server. The OCI application may retrieve data from database tables through SQL SELECT queries, or it may modify existing data in tables through INSERT, UPDATE, or DELETE statements.
Inside a database, values are stored in columns in tables. Internally, Oracle represents data in particular formats known as internal datatypes. Examples of internal datatypes include NUMBER, CHAR, and DATE.
In general, OCI applications do not work with internal datatype representations of data. OCI applications work with host language datatypes which are predefined by the language in which they are written. When data is transferred between an OCI client application and a database table, the OCI libraries convert the data between internal datatypes and external datatypes.
External datatypes are host language types that have been defined in the OCI header files. When an OCI application binds input variables, one of the bind parameters is an indication of the external datatype code (or SQLT code) of the variable. Similarly, when output variables are specified in a define call, the external representation of the retrieved data must be specified.
In some cases, external datatypes are similar to internal types. External types provide a convenience for the programmer by making it possible to work with host language types instead of proprietary data formats.
The OCI is capable of performing a wide range of datatype conversions when transferring data between Oracle and an OCI application. There are more OCI external datatypes than Oracle internal datatypes. In some cases a single external type maps to an internal type; in other cases multiple external types map to an single internal type.
The many-to-one mappings for some datatypes provide flexibility for the OCI programmer. For example, if you are processing the SQL statement
SELECT sal FROM emp WHERE empno = :employee_number
and you want the salary to come back as character data, rather than in a binary floating-point format, specify an Oracle external string datatype, such as VARCHAR2 (code = 1) or CHAR (code = 96) for the dty parameter in the OCIDefineByPos() call for the sal column. You also need to declare a string variable in your program and specify its address in the valuep parameter.
If you want the salary information to be returned as a binary floating-point value, however, specify the FLOAT (code = 4) external datatype. You also need to define a variable of the appropriate type for the valuep parameter.
Oracle performs most data conversions transparently. The ability to specify almost any external datatype provides a lot of power for performing specialized tasks. For example, you can input and output DATE values in pure binary format, with no character conversion involved, by using the DATE external datatype (code = 12). See the description of the DATE external datatype for more information.
To control data conversion, you must use the appropriate external datatype codes in the bind and define routines. You must tell Oracle where the input or output variables are in your OCI program and their datatypes and lengths.
OCI also supports an additional set of OCI typecodes which are used by Oracle's type management system to represent datatypes of object type attributes. There is a set of predefined constants which can be used to represent these typecodes. The constants each contain the prefix OCI_TYPECODE.
In summary, the OCI programmer must be aware of the following different datatypes or data representations:
Information about a column's internal datatype is conveyed to your application in the form of an internal datatype code. Once your application knows what type of data will be returned, it can make appropriate decisions about how to convert and format the output data. The Oracle internal datatype codes are listed in the section "Internal Datatypes".
|
See Also:
For detailed information about Oracle internal datatypes, see the Oracle9i SQL Reference. For information about describing select-list items in a query, see the section "Describing Select-List Items". |
An external datatype code indicates to Oracle how a host variable represents data in your program. This determines how the data is converted when returned to output variables in your program, or how it is converted from input (bind) variables to Oracle column values. For example, if you want to convert a NUMBER in an Oracle column to a variable-length character array, you specify the VARCHAR2 external datatype code in the OCIDefineByPos() call that defines the output variable.
To convert a bind variable to a value in an Oracle column, specify the external datatype code that corresponds to the type of the bind variable. For example, if you want to input a character string such as 02-FEB-65 to a DATE column, specify the datatype as a character string and set the length parameter to nine.
It is always the programmer's responsibility to make sure that values are convertible. If you try to insert the string MY BIRTHDAY into a DATE column, you will get an error when you execute the statement.
|
See Also:
For a complete list of the external datatypes and datatype codes, see Table 3-2, "External Datatypes and Codes" |
The following table lists the Oracle internal (also known as built-in) datatypes, along with each type's maximum internal length and datatype code.
You can use the piecewise capabilities provided by OCIBindByName(), OCIBindByPos(), OCIDefineByPos(), OCIStmtGetPieceInfo() and OCIStmtSetPieceInfo() to perform inserts, updates or fetches involving column data of these types.
You can use five Oracle internal datatypes to specify columns that contain characters or arrays of bytes: CHAR, VARCHAR2, RAW, LONG, and LONG RAW.
|
Note: LOBs can contain characters and FILEs can contain binary data. They are handled differently than other types, so they are not included in this discussion. See Chapter 7, "LOB and FILE Operations", for more information about these data types. |
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 bit-mapped graphics image. Character data can be transformed when passed through a gateway between networks. For example, character data passed between machines 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.
It is the responsibility of the database designer to choose the appropriate Oracle internal datatype for each column in the table. The OCI programmer must be aware of the many possible ways that character and byte-array data can be represented and converted between variables in the OCI program and Oracle tables.
When an array holds characters, the length parameter for the array in an OCI call is always passed in and returned in bytes, not characters.
The Universal ROWID (UROWID) is a datatype that can store both logical and physical rowids of Oracle tables, and rowids of the foreign tables, such as DB2 tables accessed by a gateway. Logical rowids are primary key-based logical identifiers for the rows of Index-Organized Tables (IOTs).
To use columns of the UROWID datatype, the value of the COMPATIBLE initialization parameter must be set to 8.1 or higher.
The following host variables can be bound to Universal ROWIDs:
VARCHAR2)
VARCHAR)
CHAR)
CHARZ)
ROWID descriptor)
Table 3-2 lists datatype codes for external datatypes. For each datatype, the table lists the program variable types for C from or to which Oracle internal data is normally converted.
| EXTERNAL DATATYPE | TYPE OF PROGRAM VARIABLE | OCI DEFINED CONSTANT | |
|---|---|---|---|
| NAME | CODE | ||
|
VARCHAR2 |
1 |
char[n] |
SQLT_CHR |
|
NUMBER |
2 |
unsigned char[21] |
SQLT_NUM |
|
8-bit signed INTEGER |
3 |
signed char |
SQLT_INT |
|
16-bit signed INTEGER |
3 |
signed short, signed int |
SQLT_INT |
|
32-bit signed INTEGER |
3 |
signed int, signed long |
SQLT_INT |
|
FLOAT |
4 |
float, double |
SQLT_FLT |
|
Null-terminated STRING |
5 |
char[n+1] |
SQLT_STR |
|
VARNUM |
6 |
char[22] |
SQLT_VNU |
|
LONG |
8 |
char[n] |
SQLT_LNG |
|
VARCHAR |
9 |
char[n+sizeof(short integer)] |
SQLT_VCS |
|
DATE |
12 |
char[7] |
SQLT_DAT |
|
VARRAW |
15 |
unsigned char[n+sizeof(short integer)] |
SQLT_VBI |
|
RAW |
23 |
unsigned char[n] |
SQLT_BIN |
|
LONG RAW |
24 |
unsigned char[n] |
SQLT_LBI |
|
UNSIGNED INT |
68 |
unsigned |
SQLT_UIN |
|
LONG VARCHAR |
94 |
char[n+sizeof(integer)] |
SQLT_LVC |
|
LONG VARRAW |
95 |
unsigned char[n+sizeof(integer)] |
SQLT_LVB |
|
CHAR |
96 |
char[n] |
SQLT_AFC |
|
CHARZ |
97 |
char[n+1] |
SQLT_AVC |
|
ROWID descriptor |
104 |
OCIRowid * |
SQLT_RDD |
|
NAMED DATA TYPE |
108 |
struct |
SQLT_NTY |
|
REF |
110 |
OCIRef |
SQLT_REF |
|
Character LOB descriptor |
112 |
OCILobLocator (see note 3) |
SQLT_CLOB |
|
Binary LOB descriptor |
113 |
OCILobLocator (see note 3) |
SQLT_BLOB |
|
Binary FILE descriptor |
114 |
OCILobLocator |
SQLT_FILE |
|
OCI string type |
155 |
OCIString |
SQLT_VST (see note 2) |
|
OCI date type |
156 |
OCIDate * |
SQLT_ODT (see note 2) |
|
ANSI DATE descriptor |
184 |
OCIDateTime * |
SQLT_DATE |
|
TIMESTAMP descriptor |
187 |
OCIDateTime * |
SQLT_TIMESTAMP |
|
TIMESTAMP WITH TIME ZONE descriptor |
188 |
OCIDateTime * |
SQLT_TIMESTAMP_TZ |
|
INTERVAL YEAR TO MONTH descriptor |
189 |
OCIInterval * |
SQLT_INTERVAL_YM |
|
INTERVAL DAY TO SECOND descriptor |
190 |
OCIInterval * |
SQLT_INTERVAL_DS |
|
TIMESTAMP WITH LOCAL TIME ZONE descriptor |
232 |
OCIDateTime * |
SQLT_TIMESTAMP_LTZ |
|
(1) This type is valid only for version 7.x OCI calls. OCI release 8 or later applications should use the ROWID descriptor (type 104). (2) For more information on the use of these datatypes, refer to Chapter 11, "Object-Relational Datatypes". (3) In applications using datatype mappings generated by OTT, CLOBs may be mapped as OCIClobLocator, and BLOBs may be mapped as OCIBlobLocator. For more information, refer to Chapter 14, "The Object Type Translator (OTT)". |
|||
Each of the external datatypes is described below. Datatypes that are new as of release 8.0 or later are described in the section "New External Datatypes".
The following three types are internal to PL/SQL and cannot be returned as values by OCI:
The VARCHAR2 datatype is a variable-length string of characters with a maximum length of 4000 bytes.
|
Note: If you are using Oracle objects, you can work with a special OCIString external datatype using a set of predefined OCI functions. Refer to Chapter 11, "Object-Relational Datatypes" for more information about this datatype. |
The value_sz parameter determines the length in the OCIBindByName() or OCIBindByPos() call.
If the value_sz parameter is greater than zero, Oracle obtains the bind variable value by reading exactly that many bytes, starting at the buffer address in your program. Trailing blanks are stripped, and the resulting value is used in the SQL statement or PL/SQL block. If, in the case of an INSERT statement, the resulting value is longer than the defined length of the database column, the INSERT fails, and an error is returned.
If the value_sz parameter is zero, Oracle treats the bind variable as a null, regardless of its actual content. Of course, a null must be allowed for the bind variable value in the SQL statement. If you try to insert a null into a column that has a NOT NULL integrity constraint, Oracle issues an error, and the row is not inserted.
When the Oracle internal (column) datatype is NUMBER, input from a character string that contains the character representation of a number is legal. Input character strings are converted to internal numeric format. If the VARCHAR2 string contains an illegal conversion character, Oracle returns an error and the value is not inserted into the database.
Specify the desired length for the return value in the value_sz parameter of the OCIDefineByPos() call, or the value_sz parameter of OCIBindByName() or OCIBindByPos() for PL/SQL blocks. If zero is specified for the length, no data is returned.
If you omit the rlenp parameter of OCIDefineByPos(), returned values are blank-padded to the buffer length, and nulls are returned as a string of blank characters. If rlenp is included, returned values are not blank-padded. Instead, their actual lengths are returned in the rlenp parameter.
To check if a null is returned or if character truncation has occurred, include an indicator parameter in the OCIDefineByPos() call. Oracle sets the indicator parameter to -1 when a null is fetched and to the original column length when the returned value is truncated. Otherwise, it is set to zero. If you do not specify an indicator parameter and a null is selected, the fetch call returns the error code OCI_SUCCESS_WITH_INFO. Retrieving diagnostic information on the error will return ORA-1405.
You can also request output to a character string from an internal NUMBER datatype. Number conversion follows the conventions established by National Language Support for your system. For example, your system might be configured to recognize a comma rather than period as the decimal point.
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.
|
Note: If you are using objects in an Oracle database server, you can work with a special OCINumber datatype using a set of predefined OCI functions. Refer to Chapter 11, "Object-Relational Datatypes" for more information about this datatype. |
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..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 96 (101 - 5). 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 2 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 Oracle NUMBER.
If you specify the datatype code 2 in the dty parameter of an OCIDefineByPos() call, your program receives numeric data in this Oracle internal format. The output variable should be a 21-byte array to accommodate the largest possible number. Note that only the bytes that represent the number are returned. There is no blank padding or null termination. If you need to know the number of bytes returned, use the VARNUM external datatype instead of NUMBER. See the description of VARNUM for examples of the Oracle internal number format.
The INTEGER datatype converts numbers. An external integer is a signed binary number; the size in bytes is system dependent. The host system architecture determines the order of the bytes in the variable. A length specification is required for input and output. If the number being returned from Oracle is not an integer, the fractional part is discarded, and no error or other indication is returned. If the number to be returned exceeds the capacity of a signed integer for the system, Oracle returns an "overflow on conversion" error.
The FLOAT datatype processes numbers that have fractional parts or that exceed the capacity of an integer. The number is represented in the host system's floating-point format. Normally the length is either four or eight bytes. The length specification is required for both input and output.
The internal format of an Oracle number is decimal, and most floating-point implementations are binary; therefore Oracle can represent numbers with greater precision than floating-point representations.
The null-terminated STRING format behaves like the VARCHAR2 format (datatype code 1), except that the string must contain a null terminator character. This datatype is most useful for C language programs.
The string length supplied in the OCIBindByName() or OCIBindByPos() call limits the scan for the null terminator. If the null terminator is not found within the length specified, Oracle issues the error
If the length is not specified in the bind call, the OCI uses an implied maximum string length of 4000.
The minimum string length is two bytes. If the first character is a null terminator and the length is specified as two, a null is inserted in the column, if permitted. Unlike types 1 and 96, a string containing all blanks is not treated as a null on input; it is inserted as is.
A null terminator is placed after the last character returned. If the string exceeds the field length specified, it is truncated and the last character position of the output variable contains the null terminator.
A null select-list item returns a null terminator character in the first character position. An ORA-01405 error is possible, as well.
The VARNUM datatype is like the external NUMBER datatype, except that the first byte contains the length of the number representation. This length does not include the length byte itself. Reserve 22 bytes to receive the longest possible VARNUM. Set the length byte when you send a VARNUM value to Oracle.
The following table shows several examples of the VARNUM values returned for numbers in an Oracle table.
The LONG datatype stores character strings longer than 4000 bytes. You can store up to two gigabytes (2^31-1 bytes) in a LONG column. Columns of this type are used only for storage and retrieval of long strings. They cannot be used in functions, expressions, or WHERE clauses. LONG column values are generally converted to and from character strings.
The VARCHAR datatype stores character strings of varying length. The first two bytes contain the length of the character string, and the remaining bytes contain the string. The specified length of the string in a bind or a define call must include the two length bytes, so the largest VARCHAR string that can be received or sent is 65533 bytes long, not 65535. For converting longer strings, use the LONG VARCHAR external datatype.
The DATE datatype can update, insert, or retrieve a date value using the Oracle internal date binary format. A date in binary format contains seven bytes, as shown in Table 3-4.
| Byte | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
|---|---|---|---|---|---|---|---|
|
Meaning |
Century |
Year |
Month |
Day |
Hour |
Minute |
Second |
|
Example (for 30-NOV-1992, 3:17 PM) |
119 |
192 |
11 |
30 |
16 |
18 |
1 |
The century and year bytes (bytes 1 and 2) are in excess-100 notation. The first byte stores the value of the year, which is 1992, as an integer, divided by 100, giving 119 in excess-100 notation. The second byte stores year modulo 100, giving 192. Dates Before Common Era (BCE) are less than 100. The era begins on 01-JAN-4712 BCE, which is Julian day 1. For this date, the century byte is 53, and the year byte is 88. The hour, minute, and second bytes are in excess-1 notation. The hour byte ranges from 1 to 24, the minute and second bytes from 1 to 60. If no time was specified when the date was created, the time defaults to midnight (1, 1, 1).
When you enter a date in binary format using the DATE external datatype, the database does not do consistency or range checking. All data in this format must be carefully validated before input.
When a DATE column is converted to a character string in your program, it is returned using the default format mask for your session, or as specified in the INIT.ORA file.
|
Note: If you are using objects in an Oracle database, you can work with a special OCIDate datatype using a set of predefined OCI functions.
|
The RAW datatype is used for binary data or byte strings that are not to be interpreted by Oracle, for example, to store 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 in a program, the data is represented in hexadecimal character code. Each byte of the RAW data is returned as two characters that indicate the value of the byte, from '00' to 'FF'. If you want to input a character string in your program to a RAW column in an Oracle table, you must code the data in the character string using this hexadecimal code.
You can use the piecewise capabilities provided by OCIDefineByPos(), OCIBindByName(), OCIBindByPos(), OCIStmtGetPieceInfo(), and OCIStmtSetPieceInfo() to perform inserts, updates, or fetches involving RAW (or LONG RAW) columns.
|
Note: If you are using objects in an Oracle database, you can work with a special OCIRaw datatype using a set of predefined OCI functions. Refer to Chapter 11, "Object-Relational Datatypes" for more information about this datatype. |
The VARRAW datatype is similar to the RAW datatype. However, 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 long, not 65535. For converting longer strings, use the LONG VARRAW external datatype.
The LONG RAW datatype is similar to the RAW datatype, except that it stores raw data with a length up to two gigabytes (2^31-1 bytes).
The UNSIGNED datatype is used for unsigned binary integers. The size in bytes is system dependent. The host system architecture determines the order of the bytes in a word. A length specification is required for input and output. If the number being output from Oracle is not an integer, the fractional part is discarded, and no error or other indication is returned. If the number to be returned exceeds the capacity of an unsigned integer for the system, Oracle returns an "overflow on conversion" error.
The LONG VARCHAR datatype stores data from and into an Oracle LONG column. The first four bytes of a LONG VARCHAR contain the length of the item. So, the maximum length of a stored item is 2^31-5 bytes.
The LONG VARRAW datatype is used to store data from and into an Oracle LONG RAW column. The length is contained in the first four bytes. The maximum length is 2^31-5 bytes.
The CHAR datatype is a string of characters, with a maximum length of 2000. CHAR strings are compared using blank-padded comparison semantics
The length is determined by the value_sz parameter in the OCIBindByName() or OCIBindByPos() call.
If the value_sz parameter is zero, Oracle treats the bind variable as a null, regardless of its actual content. Of course, a null must be allowed for the bind variable value in the SQL statement. If you try to insert a null into a column that has a NOT NULL integrity constraint, Oracle issues an error and does not insert the row.
Negative values for the value_sz parameter are not allowed for CHARs.
When the Oracle internal (column) datatype is NUMBER, input from a character string that contains the character representation of a number is legal. Input character strings are converted to internal numeric format. If the CHAR string contains an illegal conversion character, Oracle returns an error and does not insert the value. Number conversion follows the conventions established by National Language Support settings for your system. For example, your system might be configured to recognize a comma (,) rather than a period (.) as the decimal point.
Specify the desired length for the return value in the value_sz parameter of the OCIDefineByPos() call. If zero is specified for the length, no data is returned.
If you omit the rlenp parameter of OCIDefineByPos(), returned values are blank padded to the buffer length, and nulls are returned as a string of blank characters. If rlenp is included, returned values are not blank padded. Instead, their actual lengths are returned in the rlenp parameter.
To check whether a null is returned or if character truncation has occurred, include an indicator parameter or array of indicator parameters in the OCIDefineByPos() call. An indicator parameter is set to -1 when a null is fetched and to the original column length when the returned value is truncated. Otherwise, it is set to zero. If you do not specify an indicator parameter and a null is selected, the fetch call returns an ORA-01405 error.
You can also request output to a character string from an internal NUMBER datatype. Number conversion follows the conventions established by the National Language Support settings for your system. For example, your system might use a comma (,) rather than a period (.) as the decimal point.
The CHARZ external datatype 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.
On input, the length parameter must indicate the exact length, including the null terminator. For example, if an array in C is declared as
char my_num[] = "123.45";
then the length parameter when you bind my_num must be seven. Any other value would return an error for this example.
The following new external datatypes were introduced with or after release 8.0. These datatypes are not supported when you connect to an Oracle release 7 server.
Named data types are user-defined types which are specified with the CREATE TYPE command in SQL. Examples include object types, varrays, and nested tables. In the OCI, named data type refers to a host language representation of the type. The SQLT_NTY datatype code is used when binding or defining named data types.
In a C application, named data types are represented as C structs. These structs can be generated from types stored in the database by using the Object Type Translator. These types correspond to OCI_TYPECODE_OBJECT.
|
See Also:
|
This is a reference to a named data type. The C language representation of a REF is a variable declared to be of type OCIRef *. The SQLT_REF datatype code is used when binding or defining REFs.
Access to REFs is only possible when an OCI application has been initialized in object mode. When REFs are retrieved from the server, they are stored in the client-side object cache.
To allocate a REF for use in your application, you should declare a variable to be a pointer to a REF, and then call OCIObjectNew(), passing OCI_TYPECODE_REF as the typecode parameter.
The ROWID datatype identifies a particular row in a database table. ROWID can be a select-list item in a query, such as:
SELECT ROWID, ename, empno FROM emp
In this case, you can use the returned ROWID in further DELETE statements.
If you are performing a SELECT for UPDATE, the ROWID is implicitly returned. This ROWID can be read into a user-allocated ROWID descriptor using OCIAttrGet() on the statement handle and used in a subsequent UPDATE statement. The prefetch operation fetches all ROWIDs on a SELECT for UPDATE; use prefetching and then a single row fetch.
You access rowids through the use of a ROWID descriptor, which you can use as a bind or define variable.
|
See Also:
See the sections "Descriptors" and "Positioned Updates and Deletes" for more information about the use of the |
A LOB (Large Object) stores binary or character data up to 4 gigabytes in length. Binary data is stored in a BLOB (Binary LOB), and character data is stored in a CLOB (Character LOB) or NCLOB (National Character LOB).
LOB values may or may not be stored inline with other row data in the database. In either case, LOBs have the full transactional support of the database server. A database table stores a LOB locator which points to the LOB value which may be in a different storage space.
When an OCI application issues a SQL query which includes a LOB column or attribute in its select-list, fetching the result(s) of the query returns the locator, rather than the actual LOB value. In the OCI, the LOB locator maps to a variable of type OCILobLocator.
|
See Also:
|
The OCI functions for LOBs take a LOB locator as one of their arguments. The OCI functions assume that the locator has already been created, whether or not the LOB to which it points contains data.
Bind and define operations are performed on the LOB locator, which is allocated with the OCIDescriptorAlloc() function.
The locator is always fetched first using SQL or OCIObjectPin(), and then operations are performed using the locator. The OCI functions never take the actual LOB value as a parameter.
The datatype codes available for binding or defining LOBs are:
The NCLOB is a special type of CLOB with the following requirements:
NCLOB, the user must set the character set form (csfrm) parameter to be SQLCS_NCHAR.
amtp) parameter in calls involving CLOBs and NCLOBs is always interpreted in terms of characters, rather than bytes, for fixed-width character sets.
The BFILE datatype provides access to file LOBs that are stored in file systems outside an Oracle database. Oracle currently only supports access to binary files, or BFILEs.
A BFILE column or attribute stores a file LOB locator, which serves as a pointer to a binary file on the server's file system. The locator maintains the directory alias and the filename.
Binary file LOBs do not participate in transactions. Rather, the underlying operating system provides file integrity and durability. The maximum file size supported is 4 gigabytes.
The database administrator must ensure that the file exists and that Oracle processes have operating system read permissions on the file.
The BFILE datatype allows read-only support of large binary files; you cannot modify a file through Oracle. Oracle provides APIs to access file data.
The datatype code available for binding or defining FILEs is:
For more information about directory aliases, refer to the Oracle9i Application Developer's Guide - Large Objects (LOBs)
See Also:
The BLOB datatype stores unstructured binary large objects. BLOBs can be thought of as bitstreams with no character set semantics. BLOBs can store up to four gigabytes of binary data.
BLOBs have full transactional support; changes made through the OCI participate fully in the transaction. The 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 CLOB datatype stores fixed- or varying-width character data. CLOBs can store up to 4 gigabytes of character data.
CLOBs have full transactional support; changes made through the OCI participate fully in the transaction. The 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.
NCLOB. An NCLOB is a national character version of a CLOB. It stores fixed-width, single-byte or multibyte national character set (NCHAR) data, or varying-width character set data. NCLOBs can store up to 4 gigabytes of character text data.
NCLOBs have full transactional support; changes made through the OCI participate fully in the transaction. NCLOB value manipulations can be committed or rolled back. You cannot save a 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.
The datetime and interval datatype descriptors are briefly summarized here.
The ANSI DATE is based on the DATE, but contains no time portion. (Therefore, it also has no time zone.) ANSI DATE follows the ANSI specification for the DATE datatype. When assigning an ANSI DATE to a DATE or a timestamp datatype, the time portion of the Oracle DATE and the timestamp are set to zero. When assigning a DATE or a timestamp to an ANSI DATE, the time portion is ignored.
You are encouraged to instead use the TIMESTAMP datatype which contains both date and time.
The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus the hour, minute, and second values. It has no time zone. The TIMESTAMP datatype has the form:
TIMESTAMP(fractional_seconds_precision)
where fractional_seconds_precision (which is optional) 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.
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 UTC (Coordinated Universal Time--formerly Greenwich Mean Time). The TIMESTAMP WITH TIME ZONE datatype has the form:
TIMESTAMP(fractional_seconds_precision) WITH TIME ZONE
where 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.
TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) is another variant of TIMESTAMP that includes a time zone displacement in its value. Storage is in the same format as for TIMESTAMP. This type differs from TIMESTAMP WITH TIME ZONE in that data stored in the database is normalized to the database time zone, and the time zone displacement is not stored as part of the column data. When users retrieve the data, Oracle returns it in the users' local session time zone.
The time zone displacement is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time--formerly Greenwich Mean Time). The TIMESTAMP WITH LOCAL TIME ZONE datatype has the form:
TIMESTAMP(fractional_seconds_precision) WITH LOCAL TIME ZONE
where 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.
INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. The INTERVAL YEAR TO MONTH datatype has the form:
INTERVAL YEAR(year_precision) TO MONTH
where the optional year_precision is the number of digits in the YEAR datetime field. The default value of year_precision is 2.
INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. The INTERVAL DAY TO SECOND datatype has the form:
INTERVAL DAY (day_precision) TO SECOND(fractional_seconds_precision)
where:
day_precision is the number of digits in the DAY datetime field. It is optional. Accepted values are 0 to 9. The default is 2.
fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. It is optional. Accepted values are 0 to 9. The default is 6.
The OCI supports Oracle-defined C datatypes used to map user-defined datatypes to C representations (e.g. OCINumber, OCIArray). The OCI provides a set of calls to operate on these datatypes, and to use these datatypes in bind and define operations, in conjunction with OCI external datatype codes.
|
See Also:
For information on using these Oracle-defined C datatypes, refer to Chapter 11, "Object-Relational Datatypes" |
Table 3-5 and Table 3-6 show the supported conversions from internal datatypes to external datatypes, and from external datatypes into internal column representations, for all datatypes available through release 7.3. Information about data conversions for data types newer than release 7.3 is listed here:
LOBs are shown in a separate table that follows, because of the width limitation.
|
See Also:
For information about OCIString, OCINumber, and other new datatypes, refer to Chapter 11, "Object-Relational Datatypes" |
| INTERNAL DATATYPES | ||
|---|---|---|
| EXTERNAL DATATYPES | CLOB | BLOB |
|
VARCHAR |
I/O |
|
|
CHAR |
I/O |
|
|
LONG |
I/O |
|
|
LONG VARCHAR |
I/O |
|
|
RAW |
|
I/O |
|
VARRAW |
|
I/O |
|
LONG RAW |
|
I/O |
|
LONG VARRAW |
|
I/O |
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.
(1) When converting from TSLTZ to CHAR, DATE, TIMESTAMP and TSTZ, the value will be adjusted to the session time zone.
(2) When converting from CHAR, DATE, and TIMESTAMP to TSLTZ, the session time zone will be stored in memory.
(3) When assigning TSLTZ to ANSI DATE, the time portion will be zero.
(4) When converting from TSTZ, the time zone which the time stamp is in will be stored in memory.
(5) When assigning a character string to an interval, the character string must be a valid interval character format.
OCI has full forward and backward compatibility between a client application and the database server as far as the datetime and date columns are concerned.
The only datetime datatype available to a pre-9.0 application is the DATE datatype, SQLT_DAT. When a pre-9.0 client that defined a buffer as SQLT_DAT, tries to obtain data from a TSLTZ column, then only the date portion of the value will be returned to the client.
In this case the new client might have a bind or define buffer of type SQLT_TIMESTAMP_LTZ. The following compatibilities are maintained in this case.
If any client application tries to insert a SQLT_TIMESTAMP_LTZ (or any of the new datetime datatypes) into a DATE column, an error will be issued since there is potential data loss in this situation.
When a client has an OUT bind or a define buffer that is of datatype SQLT_TIMESTAMP_LTZ and the underlying server side SQL buffer or column is of DATE type, then the session time zone is assigned.
There is a unique typecode associated with each Oracle type, whether scalar, collection, reference, or object type. This typecode identifies the type, and is used by Oracle to manage information about object type attributes. This typecode system is designed to be generic and extensible, and is not tied to a direct one-to-one mapping to Oracle datatypes. Consider the following SQL statements:
CREATE TYPE my_type AS OBJECT ( attr1 NUMBER, attr2 INTEGER, attr3 SMALLINT); CREATE TABLE my_table AS TABLE OF my_type;
These statements create an object type and an object table. When it is created, my_table will have three columns, all of which are of Oracle NUMBER type, because SMALLINT and INTEGER map internally to NUMBER. The internal representation of the attributes of my_type, however, maintains the distinction between the datatypes of the three attributes: attr1 is OCI_TYPECODE_NUMBER, attr2 is OCI_TYPECODE_INTEGER, and attr3 is OCI_TYPECODE_SMALLINT. If an application describes my_type, these typecodes are returned.
OCITypeCode is the C datatype of the typecode. The typecode is used by some OCI functions, like OCIObjectNew() (where it helps determine what type of object is created). It is also returned as the value of some attributes when an object is described; e.g., querying the OCI_ATTR_TYPECODE attribute of a type returns an OCITypeCode value.
Table 3-8 lists the possible values for an OCITypeCode. There is a value corresponding to each Oracle datatype.
Oracle recognizes two different sets of datatype code values. One set is distinguished by the SQLT_ prefix, the other by the OCI_TYPECODE_ prefix.
The SQLT typecodes are used by OCI to specify a datatype in a bind or define operation. In this way, the SQL typecodes help to control data conversions between Oracle and OCI client applications. The OCI_TYPECODE types are used by Oracle's type system to reference or describe predefined types when manipulating or creating user-defined types.
In many cases there are direct mappings between SQLT and OCI_TYPECODE values. In other cases, however, there is not a direct one-to-one mapping. For example OCI_TYPECODE_SIGNED16, OCI_TYPECODE_SIGNED32, OCI_TYPECODE_INTEGER, OCI_TYPECODE_OCTET, and OCI_TYPECODE_SMALLINT are all mapped to the SQLT_INT type.
The following table illustrates the mappings between SQLT and OCI_TYPECODE types.
Throughout this guide you will see references to datatypes like ub2 or sb4, or to constants like UB4MAXVAL. These types are defined in the oratypes.h header file, an example of which is included here. The exact contents may vary according to the platform you are using.
#ifndef ORATYPES # define ORATYPES # define SX_ORACLE # define SX3_ORACLE #ifndef ORASTDDEF # include <stddef.h> # define ORASTDDEF #endif #ifndef ORALIMITS # include <limits.h> # define ORALIMITS #endif #ifndef TRUE # define TRUE 1 # define FALSE 0 #endif #ifdef lint # ifndef mips # define signed # endif #endif #ifdef ENCORE_88K # ifndef signed # define signed # endif #endif #if defined(SYSV_386) || defined(SUN_OS) # ifdef signed # undef signed # endif # define signed #endif #ifndef lint typedef unsigned char ub1; typedef signed char sb1; #else #define ub1 unsigned char #define sb1 signed char #endif #define UB1MAXVAL ((ub1)UCHAR_MAX) #define UB1MINVAL ((ub1) 0) #define SB1MAXVAL ((sb1)SCHAR_MAX) #define SB1MINVAL ((sb1)SCHAR_MIN) #define MINUB1MAXVAL ((ub1) 255) #define MAXUB1MINVAL ((ub1) 0) #define MINSB1MAXVAL ((sb1) 127) #define MAXSB1MINVAL ((sb1) -127) #ifndef lint typedef unsigned short ub2; typedef signed short sb2; #else #define ub2 unsigned short #define sb2 signed short #endif #define UB2MAXVAL ((ub2)USHRT_MAX) #define UB2MINVAL ((ub2) 0) #define SB2MAXVAL ((sb2) SHRT_MAX) #define SB2MINVAL ((sb2) SHRT_MIN) #define MINUB2MAXVAL ((ub2) 65535) #define MAXUB2MINVAL ((ub2) 0) #define MINSB2MAXVAL ((sb2) 32767) #define MAXSB2MINVAL ((sb2)-32767) #ifndef lint typedef unsigned int ub4; typedef signed int sb4; #else #define eb4 int #define ub4 unsigned int #define sb4 signed int #endif #define UB4MAXVAL ((ub4)UINT_MAX) #define UB4MINVAL ((ub4) 0) #define SB4MAXVAL ((sb4) INT_MAX) #define SB4MINVAL ((sb4) INT_MIN) #define MINUB4MAXVAL ((ub4) 4294967295) #define MAXUB4MINVAL ((ub4) 0) #define MINSB4MAXVAL ((sb4) 2147483647) #define MAXSB4MINVAL ((sb4)-2147483647) #define UB1BITS CHAR_BIT #define UB1MASK ((1 << ((uword)CHAR_BIT)) - 1) typedef ub1 bitvec; #define BITVEC(n) (((n)+(UB1BITS-1))>>3) #ifdef lint # define oratext unsigned char #else typedef unsigned char oratext; #endif #ifndef lint typedef ub4 duword; typedef sb4 dsword; typedef dsword dword; #else #define duword ub4 #define dsword sb4 #define dword dsword #endif #define DUWORDMAXVAL UB4MAXVAL #define DUWORDMINVAL UB4MINVAL #define DSWORDMAXVAL SB4MAXVAL #define DSWORDMINVAL SB4MINVAL #define MINDUWORDMAXVAL MINUB4MAXVAL #define MAXDUWORDMINVAL MAXUB4MINVAL #define MINDSWORDMAXVAL MINSB4MAXVAL #define MAXDSWORDMINVAL MAXSB4MINVAL #define DEWORDMAXVAL EB4MAXVAL #define DEWORDMINVAL EB4MINVAL #define MINDEWORDMAXVAL MINEB4MAXVAL #define MAXDEWORDMINVAL MAXEB4MINVAL #define DWORDMAXVAL DSWORDMAXVAL #define DWORDMINVAL DSWORDMINVAL #ifndef lint typedef ub4 dsize_t; # else # define dsize_t ub4 #endif # define DSIZE_TMAXVAL UB4MAXVAL # define MINDSIZE_TMAXVAL (dsize_t)65535 #ifndef lint typedef sb4 dboolean; # else # define dboolean sb4 #endif #ifndef lint typedef ub4 dptr_t; #else #define dptr_t ub4 #endif #ifndef lint typedef char eb1; typedef short eb2; typedef int eb4; typedef eb4 deword; #else # define eb1 char # define eb2 short # define eb4 int # define deword eb4 #endif #define EB1MAXVAL ((eb1)SCHAR_MAX) #define EB1MINVAL ((eb1) 0) #define MINEB1MAXVAL ((eb1) 127) #define MAXEB1MINVAL ((eb1) 0) #define EB2MAXVAL ((eb2) SHRT_MAX) #define EB2MINVAL ((eb2) 0) #define MINEB2MAXVAL ((eb2) 32767) #define MAXEB2MINVAL ((eb2) 0) #define EB4MAXVAL ((eb4) INT_MAX) #define EB4MINVAL ((eb4) 0) #define MINEB4MAXVAL ((eb4) 2147483647) #define MAXEB4MINVAL ((eb4) 0) #ifndef lint typedef sb1 b1; #else #define b1 sb1 #endif #define B1MAXVAL SB1MAXVAL #define B1MINVAL SB1MINVAL #ifndef lint typedef sb2 b2; #else #define b2 sb2 #endif #define B2MAXVAL SB2MAXVAL #define B2MINVAL SB2MINVAL #ifndef lint typedef sb4 b4; #else #define b4 sb4 #endif # define B4MAXVAL SB4MAXVAL # define B4MINVAL SB4MINVAL #ifndef uiXT typedef ub1 BITS8; typedef ub2 BITS16; typedef ub4 BITS32; #endif #if !defined(LUSEMFC) # ifdef lint # define text unsigned char # define OraText oratext # else typedef oratext text; typedef oratext OraText; # endif #endif #define M_IDEN 30 #ifdef AIXRIOS # define SLMXFNMLEN 256 #else # define SLMXFNMLEN 512 #endif #ifndef lint typedef int eword; typedef unsigned int uword; typedef signed int sword; #else #define eword int #define uword unsigned int #define sword signed int #endif #define EWORDMAXVAL ((eword) INT_MAX) #define EWORDMINVAL ((eword) 0) #define UWORDMAXVAL ((uword)UINT_MAX) #define UWORDMINVAL ((uword) 0) #define SWORDMAXVAL ((sword) INT_MAX) #define SWORDMINVAL ((sword) INT_MIN) #define MINEWORDMAXVAL ((eword) 2147483647) #define MAXEWORDMINVAL ((eword) 0) #define MINUWORDMAXVAL ((uword) 4294967295) #define MAXUWORDMINVAL ((uword) 0) #define MINSWORDMAXVAL ((sword) 2147483647) #define MAXSWORDMINVAL ((sword) -2147483647) #ifndef lint typedef unsigned long ubig_ora; typedef signed long sbig_ora; #else #define ubig_ora unsigned long #define sbig_ora signed long #endif #define UBIG_ORAMAXVAL ((ubig_ora)ULONG_MAX) #define UBIG_ORAMINVAL ((ubig_ora) 0) #define SBIG_ORAMAXVAL ((sbig_ora) LONG_MAX) #define SBIG_ORAMINVAL ((sbig_ora) LONG_MIN) #define MINUBIG_ORAMAXVAL ((ubig_ora) 4294967295) #define MAXUBIG_ORAMINVAL ((ubig_ora) 0) #define MINSBIG_ORAMAXVAL ((sbig_ora) 2147483647) #define MAXSBIG_ORAMINVAL ((sbig_ora)-2147483647) #define UBIGORABITS (UB1BITS * sizeof(ubig_ora)) #ifndef lint #if (__STDC__ != 1) # define SLU8NATIVE # define SLS8NATIVE #endif #endif #ifdef SLU8NATIVE #ifdef SS_64BIT_SERVER # ifndef lint typedef unsigned long ub8; # else # define ub8 unsigned long # endif #else # ifndef lint typedef unsigned long long ub8; # else # define ub8 unsigned long long # endif #endif #define UB8ZERO ((ub8)0) #define UB8MINVAL ((ub8)0) #define UB8MAXVAL ((ub8)18446744073709551615) #define MAXUB8MINVAL ((ub8)0) #define MINUB8MAXVAL ((ub8)18446744073709551615) #endif #ifdef SLS8NATIVE #ifdef SS_64BIT_SERVER # ifndef lint typedef signed long sb8; # else # define sb8 signed long # endif #else # ifndef lint typedef signed long long sb8; # else # define sb8 signed long long # endif #endif #define SB8ZERO ((sb8)0) #define SB8MINVAL ((sb8)-9223372036854775808) #define SB8MAXVAL ((sb8) 9223372036854775807) #define MAXSB8MINVAL ((sb8)-9223372036854775807) #define MINSB8MAXVAL ((sb8) 9223372036854775807) #endif #undef CONST #ifdef _olint # define CONST const #else #if defined(PMAX) && defined(__STDC__) # define CONST const #else # ifdef M88OPEN # define CONST const # else # if defined(SEQ_PSX) && defined(__STDC__) # define CONST const # else # ifdef A_OSF # if defined(__STDC__) # define CONST const # else # define CONST # endif # else # define CONST # endif # endif # endif #endif #endif #ifdef lint # define dvoid void #else # ifdef UTS2 # define dvoid char # else # define dvoid void # endif #endif typedef void (*lgenfp_t)( void ); #ifndef ORASYS_TYPES # include <sys/types.h> # define ORASYS_TYPES #endif #ifndef boolean #ifndef lint typedef int boolean; #else #define boolean int #endif #endif #ifndef SIZE_TMAXVAL # define SIZE_TMAXVAL UBIG_ORAMAXVAL #endif #ifndef MINSIZE_TMAXVAL # define MINSIZE_TMAXVAL (size_t)4294967295 #endif #if !defined(MOTIF) && !defined(LISPL) && !defined(__cplusplus) && \ !defined(LUSEMFC) typedef OraText *string; #endif #ifndef lint typedef unsigned short utext; #else #define utext unsigned short #endif #endif
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|