Programmer's Guide to the Oracle7 Server Call Interface Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Datatypes


This chapter is your principal reference for external datatype codes. These are required for the ODEFIN, OBNDRA, OBNDRV, and OBNDRN routines, and for the data conversions that are performed when you transfer data between your program and Oracle.

This chapter discusses the following topics:


Oracle Datatypes

When you change or insert data in an Oracle table, and when you receive data from Oracle on a query, the data passes between variables in your program and columns in the Oracle table. Oracle represents data internally in several formats. Among them are NUMBER, CHAR, DATE, and RAW.

Your OCI program stores data in variables, whose types are predefined by the language you are using. When you transfer data between Oracle and your program, you need to specify the format of the data in your program. 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 FTYPE parameter in the ODEFIN call. You also need to declare a string variable in your program and specify its address in the BUF 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 BUF 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, such as OBNDRA or ODEFIN. You must tell Oracle where the input or output variables are in your OCI program and their datatypes and lengths.

Internal Datatype Codes

The way Oracle represents data internally is conveyed to your program in the form of an internal datatype code. For example, if you do not know the datatypes of items to be returned by a query, you can call the ODESCR (describe) routine, which return an internal datatype code for each select-list item that is described. The internal datatypes are important for queries, because they let the program decide how to convert and format the output data.

External Datatype Codes

An external datatype code indicates to Oracle how the 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 (1) in the ODEFIN 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 `25-JAN-64' to a DATE column, specify the datatype as a character string (1) 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.

For a complete list of the external datatypes and datatype codes, see Table 3 - 2 [*].


Internal Datatypes

Table 3 - 1 represents the internal datatypes and datatype codes. This section, from the point of view of the Oracle user, provides the information needed to create or modify a database table.

Internal Oracle Datatype Maximum Internal Length Datatype Code
VARCHAR2 2000 bytes 1
NUMBER 21 bytes 2
LONG 2^31-1 bytes 8
ROWID 6 bytes 11
DATE 7 bytes 12
RAW 255 bytes 23
LONG RAW 2^31-1 bytes 24
CHAR 255 bytes 96
MLSLABEL 255 bytes 105
Table 3 - 1. Oracle Internal Datatypes

VARCHAR2

The VARCHAR2 datatype stores variable-length character strings. When a VARCHAR2 column is created, a maximum length between 1 and 2000 must be specified. Trailing blanks are never appended to a VARCHAR2 value on output.

See "Character Strings and Byte Arrays" [*] for more information about the VARCHAR2 internal datatype.

NUMBER

The NUMBER datatype stores fixed or floating-point numbers of virtually any size. You can specify precision, which is the total number of digits, and scale, which determines where rounding will occur.

The maximum precision of a NUMBER value is 38 decimal digits; the magnitude range is 1.0E-129 to 9.99E125. Scale can range from -84 to 127. For example, a scale of -3 means the number is rounded to the nearest thousand (3456 becomes 3000). A scale of 2 means the value is rounded to the nearest hundredth (3.456 becomes 3.46).

When you specify precision and scale, Oracle does extra integrity checks before storing data in the column.

LONG

The LONG datatype stores variable-length character strings. LONG columns can store text, arrays of characters, or even complete documents. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG column is 2^31-1 bytes.

You can use the OFLNG routine to retrieve a portion of a LONG (or LONG RAW) column, starting at any offset in the column.

You can also use the piecewise capabilities provided by ODEFINPS, OBINDPS, OGETPI and OSETPI to perform inserts, updates or fetches involving LONG columns.

Note: The maximum length of LONG and LONG RAW columns is 2^31-1 bytes in an Oracle7 database. In Oracle Version 6 and earlier it is 65535 bytes.

Restrictions: You can use LONG columns in UPDATE, INSERT, and (most) SELECT statements, but not in expressions, function calls, or certain SQL clauses, such as WHERE, GROUP BY, and CONNECT BY. Only one LONG (or LONG RAW) column is allowed per database table, and that column cannot be indexed.

ROWID

The ROWID datatype returns the address of a row in an Oracle database. ROWIDs are the fastest way to access a particular row.

The internal size of an Oracle ROWID is six bytes. However, ROWIDs are not accessible in their internal format; the OCI program handles them in a binary or character external format. The size of the binary representation is operating-system dependent and is returned by a describe operation on a ROWID select-list item. The size of the character representation also varies. For additional information, see the discussion of VARCHAR2 and ROWID in the section "External Datatypes" [*].

DATE

The DATE datatype stores date and time information. Inside Oracle, DATEs are stored in a binary format. See the description of the DATE datatype [*] for a description of this format.

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.

If you need additional date information on a query, such as the time, or a date in Julian days, apply the TO_CHAR function to the date and use a format mask. Be sure to set the length of the output parameter in the ODEFIN routine to accommodate the additional characters. See Oracle7 Server SQL Reference for more information about TO_CHAR and format masks. Unless you want date information in the Oracle internal format (see the "External Datatypes" section [*]), always convert DATE columns and expressions to and from character strings using external character datatypes, such as VARCHAR2 or STRING.

RAW

The RAW datatype represents binary data (for example, graphical data) that must not be interpreted as a character string. Gateways between networks might convert CHAR or LONG data from one character format to another (for example, ASCII to EBCDIC). RAW data is never converted in this way.

The maximum length of a RAW column is 255 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 ODEFINPS, OBINDPS, OGETPI and OSETPI to perform inserts, updates or fetches involving RAW (or LONG RAW) columns.

LONG RAW

The LONG RAW datatype is just like RAW, except that the maximum length of a LONG RAW column is 2^31-1 bytes. The restrictions that apply to LONG data also apply to LONG RAW data.

CHAR

The CHAR datatype stores fixed-length character strings. When a column is created of type CHAR, a maximum length between 1 and 255 is specified. When a CHAR value is output, trailing blanks are appended to the string, up to the column length specified.

See the section "Character Arrays and Strings" [*] for more information about the CHAR internal datatype.

MLSLABEL

You use the MLSLABEL datatype to store the binary format of an operating-system label. Trusted Oracle uses a label to control access to information. See the Trusted Oracle7 Server Administrator's Guide for more information about labels. The maximum width of an MLSLABEL column is 255 bytes.

In standard Oracle, you can define a column using the MLSLABEL datatype. However, the only valid value for the column is NULL. In Trusted Oracle7, you can insert any valid operating system label, in any valid format, into a column having the MLSLABEL datatype. Trusted Oracle implicitly converts the data to the binary format of a label.


Character Strings and Byte Arrays

There are five Oracle internal datatypes that you can use to specify columns that contain 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 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.

CHAR and VARCHAR2

The VARCHAR2 internal datatype is equivalent to the CHAR datatype in Oracle V6 or earlier. The CHAR datatype in Oracle7 is a fixed-length string type. String comparisons between CHAR strings behave differently from string comparisons between VARCHAR2 strings.

String Comparison

Strings are compared using either blank-padded or non-blank-padded comparison semantics. In a blank-padded comparison, blanks are added to the shorter of the two strings to make the lengths equal. The strings are then compared, character by character, until a difference occurs or the end of the strings is reached. If a difference occurs, the string having the character with the greater value is considered greater.

In a non-blank-padded comparison, the strings are compared until a difference occurs or the end of the shorter string is reached. If two strings are equivalent up to the end of the shorter string, the longer string is considered greater. If two strings of equal length have no differing characters, they are equal.

Blank-padded comparisons are used whenever both of the strings are fixed length. See Chapter 3 of the Oracle7 Server SQL Reference for additional information about string comparison.


External Datatypes

Table 3 - 2 lists datatype codes for external datatypes. For each datatype, the table lists the program variable types for C, COBOL and FORTRAN from or to which Oracle internal data is normally converted.

EXTERNAL DATATYPE TYPE OF PROGRAM VARIABLE
NAME CODE C COBOL FORTRAN
VARCHAR2 1 char[n] PIC X(n) CHARACTER*n
NUMBER 2 unsigned char[21] PIC X(21) LOGICAL*1(21)
8-bit signed INTEGER 3 signed char PIC S9(3) COMP LOGICAL*1
16-bit signed INTEGER 3 signed short, signed int PIC S9(4) COMP INTEGER*2
32-bit signed INTEGER 3 signed int, signed long PIC S9(9) COMP INTEGER*4
FLOAT 4 float, double PIC S9(n)V9(n) COMP-1,2 REAL*4, REAL*8
Null-terminated STRING 5 char[n+1] n/a n/a
VARNUM 6 char[22] PIC X(22) LOGICAL*1 (22)
PACKED DECIMAL 7 n/a PIC S9(n)V9(n) COMP-3 n/a
LONG 8 char[n] PIC X(n) CHARACTER*n
VARCHAR 9 char[n+slen] PIC X(n+slen) VARYING LOGICAL*1(n+slen)
ROWID 11 char[n] PIC X(n) LOGICAL*1(n)
DATE 12 char[7] PIC X(7) LOGICAL*1(7)
VARRAW 15 unsigned char[n+slen] PIC X(n+slen) LOGICAL*1(n+slen)
RAW 23 unsigned char[n] PIC X(n) LOGICAL*1(n)
LONG RAW 24 unsigned char[n] PIC X(n) LOGICAL*1(n)
UNSIGNED INT 68 unsigned n/a n/a
DISPLAY 91 n/a PIC S9(n) PIC S9(n)V9(n) n/a
LONG VARCHAR 94 char[n+ilen] PIC X(n+ilen) LOGICAL*1(n+ilen)
LONG VARRAW 95 unsigned char[n+ilen] PIC X(n+ilen) LOGICAL*1(n+ilen)
CHAR 96 char[n] PIC X(n) CHARACTER*n
CHARZ 97 char[n+1] n/a n/a
CURSOR VARIABLE 102 struct cda_def
MLSLABEL 105 char(n) PIC X(n) LOGICAL*1(n)
Table 3 - 2. External Datatypes and Codes

Each of the external datatypes is described below.

VARCHAR2

The VARCHAR2 datatype is a variable-length string of characters with a maximum length of 2000 bytes.

Input

The PROGVL parameter determines the length in the OBNDRA, OBNDRN, OBNDRV or OBINDPS call.

If the PROGVL 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.

Note: A trailing null is not stripped. Variables should be blank-padded but not null-terminated.

If the PROGVL parameter is -1, the character array is scanned for a null terminator character. If a null terminator is found in the first 2001 bytes of the array, the array elements up to, but not including the null terminator, are used as the bind variable value. If a null terminator is not found, Oracle returns an error. When the BUFL parameter is -1, trailing blanks (blanks immediately preceding the null terminator) are not stripped.

If the PROGVL 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 input.

Output

Specify the desired length for the return value in the BUFL parameter of the ODEFIN or ODEFINPS call, or the PROGVL parameter of OBNDRA or OBNDRV for PL/SQL blocks. If zero is specified for the length, no data is returned.

If you omit the RLEN parameter of ODEFIN, returned values are blank-padded to the buffer length, and nulls are returned as a string of blank characters. If RLEN is included, returned values are not blank-padded. Instead, their actual lengths are returned in the RLEN parameter.

To check if a null is returned or if character truncation has occurred, include an indicator parameter in the ODEFIN call. The 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 the error 1405.

Warning: If you are connected to an Oracle7 database, and you set the LNGFLG parameter of the OPARSE call to zero, then selecting a null with no indicator parameter defined does not cause an error to be returned. This is for Oracle Version 6 compatability.

Output to a character string from an internal NUMBER datatype can also be made. 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.

Special Considerations for ROWID When an OCI program is connected to a non-Oracle data manager via an Oracle Open Gateway, the Oracle ROWID field in the CDA is not valid. In this case, ROWIDs must be explicitly SELECTed from the table into a character array.

The maximum length of the array is 255 bytes. To do dynamic memory allocation of the output buffer for a ROWID, you can call the ODESCR routine for a ROWID select-list item. When ODESCR returns datatype code 1 in the DBTYPE parameter, the DBSIZE parameter contains the correct size of the non-Oracle ROWID.

When you are connected to a non-Oracle data manager, you must use the VARCHAR2 external datatype code (1) in the FTYPE parameter of the bind or define call for all ROWIDs.

For maximum portability of your OCI application, Oracle recommends that you use explicit ROWIDs, rather than depending on the Oracle ROWID field of the CDA. In this case, always bind and define ROWIDs using the VARCHAR2 external datatype.

NUMBER

You should not need to use NUMBER as an external datatype. If you do use it, Oracle will output 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. The lower 7 bits represent the exponent, which is a base 100 digit with an offset of 65.

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 FTYPE parameter of an ODEFIN or ODEFINPS 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.

INTEGER

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 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 a signed integer for the system, an "overflow on conversion" error is returned.

FLOAT

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.

Because the internal format of an Oracle number is decimal and most floating-point implementations are binary, Oracle can represent numbers with greater precision than floating-point representations.

Note: You may get a round-off error when converting between FLOAT and NUMBER. Thus, using a FLOAT as a bind variable in a query may return an ORA-1403 error. You can avoid this situation by converting the FLOAT into a STRING and then using datatype code 1 or 5.

STRING

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 programs.

Input

The string length supplied in the OBINDPS, OBNDRA, OBNDRN, or OBNDRV call limits the scan for the null terminator. If the null terminator is not found within the length specified, Oracle issues the error

ORA-01480:  trailing null missing from STR bind value

If the length is not specified in the bind call, an implied maximum string length of 2000 is used.

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.

Output

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.

VARNUM

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 input a VARNUM value to Oracle.

Table 3 - 3 shows several examples of the VARNUM values returned for numbers in an Oracle table.

Decimal Value Length Byte Exponent Byte Mantissa Bytes Terminator Byte
0 1 128 n/a n/a
5 2 193 6 n/a
-5 3 62 96 102
2767 3 194 28, 68 n/a
-2767 4 61 74, 34 102
100000 2 195 11 n/a
1234567 5 196 2, 24, 46, 68 n/a
Table 3 - 3. VARNUM Examples

PACKED DECIMAL

The PACKED DECIMAL datatype converts between non-integral numbers in Oracle and a datatype that is suitable for calculation. In COBOL, the data area must be a signed COMP-3 field with an implied decimal point. The number of digits to the right of the decimal point is specified in the FMT parameter of the ODEFIN or ODEFINPS routine and in the SCALE parameter. For more information about defining conversion format strings, see the descriptions of ODEFIN[*]. The returned value can be used as is for COBOL calculations or can be moved to a computational field before calculations. The number will never be converted to scientific notation. If the number to be returned loses significant digits during the conversion, Oracle fills the buffer with asterisk (*) characters.

LONG

The LONG datatype stores character strings longer than 2000 bytes. You can store up to 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.

VARCHAR

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.

ROWID

The ROWID datatype identifies a particular row in a database table. ROWID can be a select-list item in a query; for example:

SELECT rowid, ename, sal FROM emp FOR UPDATE OF sal

In this case, you use the returned ROWID in further INSERT, UPDATE, or DELETE statements.

Also, after INSERT, DELETE, UPDATE, and SELECT FOR UPDATE statements are executed, the Oracle ROWID field in the CDA can contain a binary representation of the ROWID for the row that was just changed or selected. However, this field is valid only if the OCI program is connected to an Oracle database. See the "Special Considerations" section [*], in the description of the external datatype VARCHAR2, for more information.

The size of the binary representation of a ROWID is system dependent. One way to determine the binary ROWID size on your system is to describe, using ODESCR, a SQL statement such as

SELECT rowid FROM dual

Whenever ODESCR returns the datatype code 11 in the DBTYPE parameter, the DBSIZE parameter contains the size in bytes of the binary ROWID.

Never attempt to construct a ROWID and use it in subsequent DML statements. Use only ROWIDs returned by Oracle.

DATE

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
Table 3 - 4. Format of the DATE Datatype

The century and year bytes are in an excess-100 notation. 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 input 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.

Note: There is little need to use the Oracle external DATE datatype in ordinary database operations. It is much more convenient to convert DATEs in character format, because the program usually displays (on a query) or inputs in a character format, such as `DD-MON-YY'.

VARRAW

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.

RAW

The RAW datatype is used for data that is not to be interpreted by Oracle. The maximum length of a RAW column is 255 bytes. The raw datatypes are intended for binary data or byte strings, for example, to store graphics character sequences. For more information, see to the Oracle7 Server SQL Reference.

LONG RAW

The LONG RAW datatype is similar to the RAW datatype, except that it stores raw data with a length up to 2^31-1 bytes.

UNSIGNED

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, an "overflow on conversion" error is returned.

DISPLAY

The DISPLAY datatype stores numeric character data. The DISPLAY datatype refers to a COBOL "DISPLAY SIGN LEADING SEPARATE" number, which typically requires n + 1 bytes of storage for PIC S9(n) and n + d + 1 bytes for PIC S9(n)V9(d).

LONG VARCHAR

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. On a bind or define call, if the length of the data area is greater than 65533 bytes, pass a -1 in the length parameter and make sure the length is in the first four bytes of the data area before the bind or define.

LONG VARRAW

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. On a bind or define call, if the length of the data area is greater than 65533 bytes, pass a -1 in the length parameter, and make sure the length is in the first four bytes of the data area before the bind or define.

CHAR

The CHAR datatype is a string of characters, with a maximum length of 255. CHAR strings are compared using blank-padded comparison semantics (see Chapter 3 in the Oracle7 Server SQL Reference).

Input

The length is determined by the PROGVL parameter in the OBNDRA, OBNDRN, or OBNDRV call.

Note: The entire contents of the buffer (PROGVL chars) is passed to the database, including any trailing blanks or nulls.

If the PROGVL 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 (especially -1) for the PROGVL 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 input 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.

Output

Specify the desired length for the return value in the BUFL parameter of the ODEFIN or ODEFINPS call. If zero is specified for the length, no data is returned.

If you omit the RLEN parameter of ODEFIN, returned values are blank padded to the buffer length, and nulls are returned as a string of blank characters. If RLEN is included, returned values are not blank padded. Instead, their actual lengths are returned in the RLEN parameter.

To check if a null is returned or if character truncation has occurred, include an indicator parameter or array of indicator parameters in the ODEFIN or ODEFINPS 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.

Note: If you are connected to an Oracle7 database, but you set the LNGFLG parameter of the OPARSE call to zero, selecting a null with no indicator parameter defined returns no error.

Output to a character string from an internal NUMBER datatype can also be made. 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.

CHARZ

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.

CURSOR VARIABLE

The CURSOR VARIABLE datatype covers all cursor type definitions in PL/SQL, though you can use the older PL/SQL static cursors in a PL/SQL block. To bind a PL/SQL cursor variable to an OCI program variable in one of the host languages supported, you must use the regular cursor datatype for declaring CDAs and bind your variable using OBNDRA or OBINDPS.

After binding the program cursor to a PL/SQL cursor variable, you can execute the PL/SQL block. Then you can use the variable cursor as a regular program cursor. For example, you may then describe the select-list items, bind them to program variables (using ODEFIN or ODEFINPS), fetch rows, and close the cursor.

For more information see the section "Cursor Variables" [*].

MLSLABEL

Use the MLSLABEL datatype to store an operating-system label in binary form. A Trusted Oracle7 label controls access to information. See the Trusted Oracle7 Server Administrator's Guide for more information about labels. In standard Oracle, you can define a column using the MLSLABEL datatype. However, the only valid value for the column is null.

The internal length of MLSLABEL is between two and five bytes.

Input

Trusted Oracle7 translates the input text string (up to 255 bytes long) into a binary label and ensures the label is valid within the operating system. If it is not, Trusted Oracle returns an error. If it is valid, the binary form of the label is stored in the target database column.

Output

Trusted Oracle7 translates the binary label to a character string.


Data Conversions

Table 3 - 5 shows the supported conversions from internal Oracle datatypes to external datatypes, and from external datatypes into internal column representations.

INTERNAL
EXTERNAL 1 VARCHAR2 2 NUMBER 8 LONG 11 ROWID 12 DATE 23 RAW 24 LONG RAW 96 CHAR 105 MLSLABEL
1 VARCHAR2 I/O I/O I/O I/O(1) I/O(2) I/O(3) I(3) I/O I/O(7)
2 NUMBER I/O(4) I/O I I/O(4)
3 INTEGER I/O(4) I/O I I/O(4)
4 FLOAT I/O(4) I/O I I/O(4)
5 STRING I/O I/O I/O I/O(1) I/O(2) I/O(3) I(3,5) I/O I/O(7)
6 VARNUM I/O(4) I/O I I/O(4)
7 DECIMAL I/O(4) I/O I I/O(4)
8 LONG I/O I/O I/O I/O(1) I/O(2) I/O(3) I(3,5) I/O I/O(7)
9 VARCHAR I/O I/O I/O I/O(1) I/O(2) I/O(3) I(3,5) I/O I/O(7)
11 ROWID I I I/O I
12 DATE I/O I I/O I/O
15 VARRAW I/O(6) I(5,6) I/O I/O I/O(6)
23 RAW I/O(6) I(5,6) I/O I/O I/O(6)
24 LONG RAW O(6) I(5,6) I/O I/O O(6)
68 UNSIGNED I/O(4) I/O I I/O(4)
91 DISPLAY I/O(4) I/O I I/O(4)
94 LONG VARCHAR I/O I/O I/O I/O(1) I/O(2) I/O(3) I(3,5) I/O I/O(7)
95 LONG VARRAW I/O(6) I(5,6) I/O I/O I/O(6)
96 CHAR I/O I/O I/O I/O(1) I/O(2) I/O(3) I(3) I/O I/O(7)
97 CHARZ I/O I/O I/O I/O(1) I/O(2) I/O(3) I(3) I/O I/O(7)
105 MLSLABEL I/O(8) I/O(8) I/O(8) I/O
Notes: (1) For input, host string must be in Oracle 'BBBBBBBB.RRRR.FFFF' format. On output, column value is returned in same format. (2) For input, host string must be the default DATE character format. On output, column value is returned in same format (3) For input, host string must be in hex format. On output, column value is returned in same format. (4) For output, column value must represent a valid number. (5) Length must be less than or equal to 2000. (6) On input, column value is stored in hex format. For output, column value must be in hex format. (7) For input, host string must be a valid OS label in text format. On output, column value is returned in same format. (8) For input, host string must be a valid OS label in raw format. On output, column value is returned in same format. Legend: I = input only O = output only I/O = input or output
Table 3 - 5. Data Conversion




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index