Oracle treats zero-length VARCHAR2 and NVARCHAR2 values as NULL values. Oracle also treats NULL VARCHAR2 and NVARCHAR2 values as zero-length values. For this reason, Oracle queries may return different results than queries against other databases, for example:
|
The CONCAT function, when concatenating a NULL value and a non-NULL value, returns a non-NULL value because the NULL value is treated as an empty string.
|
|
|
Comparisons with empty strings using the operators =, <, >, and <> never evaluate to TRUE. For example, the expression:
|
does not evaluate to TRUE because Oracle treats '' as a NULL value, and any comparison with NULL evaluates to UNKNOWN, not TRUE.
Actuate SQL uses SQL-92 semantics to perform VARCHAR string comparisons. In most cases, when a query is pushed to the database, the mappings.xml file preserves SQL-92 semantics. For Oracle databases, however, using SQL-92 semantics results in poor performance. For this reason, a query that is pushed to an Oracle database uses Oracle semantics to perform string comparisons. If you are working solely with Oracle databases, string comparisons yield consistent results. If you join a table in an Oracle database to a table in a database of another type, string comparisons may yield inconsistent results because the query pushed to the other database uses SQL-92 semantics. Moreover, if the query is not pushed to the database, the Integration service uses SQL-92 semantics. If you want Oracle databases to use SQL-92 semantics, you must use a different mappings.xml file. Contact Actuate Support to obtain this file.
Table 8-3 shows how Oracle data types map to Actuate SQL data types. The letters p and s represent precision and scale.
|
Oracle
data type |
Actuate SQL data type
|
Compiled to Oracle
data type |
|
|
Oracle NVARCHAR2 has a maximum length of 2000 bytes or characters.
|
|||
|
DATE (Oracle 8i)
TIMESTAMP (Oracle 9i and later)
|
Oracle versions earlier than 9i do not support the TIMESTAMP data type. For those versions the milliseconds field of timestamp values is ignored in comparisons and sorting.
|
||
|
Oracle FLOAT has a maximum precision of 38 decimal digits.
|
|||
|
Oracle NVARCHAR2 has a maximum length of 2000 bytes or characters.
|
|||
|
INTEGER if s=0 and p<=9
DECIMAL if s<>0 or p>9
|
Oracle supports only the NUMBER and FLOAT data types for internal storage. INTEGER and DECIMAL data types are provided to support queries written with standard SQL types.
The maximum number of significant digits (precision) for Oracle DECIMAL is 38. |
||
|
Oracle NVARCHAR2 has a maximum length of 2000 bytes or characters.
|
|||
|
Oracle NVARCHAR2 has a maximum length of 2000 bytes or characters.
|
