| Programmer's Guide to the Oracle7 Server Call Interface |  Library |
 Product |
 Contents |
 Index |
| Programmer's Guide to the Oracle7 Server Call Interface | Library |
Product |
Contents |
Index |
This chapter discusses the following topics:
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 ![[*]](jump.gif)
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.
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 .
See "Character Strings and Byte Arrays" for more information about the VARCHAR2 internal datatype.
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.
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.
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" .
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.
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.
See the section "Character Arrays and Strings" for more information about the CHAR internal datatype.
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.
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.
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 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) |
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.
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.
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.
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.
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.
A null select-list item returns a null terminator character in the first character position. An ORA-01405 error is possible, as well.
Table 3 - 3 shows several examples of the VARNUM values returned for numbers in an Oracle table.
. 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.
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.
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.
| 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 |
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'.
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.
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.
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.
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" .
The internal length of MLSLABEL is between two and five bytes.
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |