USoft for Oracle

Oracle versions 10 and 11 are currently supported by USoft Developer, in line with the policy that USoft should actively support the two most recent versions of Oracle. In principle, Oracle 9 is also supported by USoft Developer, but support is limited.

For more details of the latest support policy for USoft and Oracle, please contact your local USoft Sales organization.

CHAR	DATE	LONG RAW
NUMBER	RAW	VARCHAR2
LONG	BLOB	CLOB

Oracle 8 introduced new BLOB/CLOB data types to support large object columns. USoft also supports thesedata types.

BLOB	Binary Large Object. The Oracle8 (and higher) data type holding binary data. It is used to store documents and audio/video (multimedia) data. Maximum size 4Gb
CLOB	Character Large Object. The Oracle8 (and higher) data type holding single byte fixed width character data. The maximum size of this column is 4Gb.

CLOB and BLOB data types replace LONG and LONG RAW respectively. In Oracle 7 and earlier, LONG and LONG RAW were used as follows:

LONG	Data type used in Oracle 7 and earlier for large character data. In Oracle 8 this is only supported for backward compatibility.
LONG RAW	Data type used in Oracle 7 and earlier for large binary data. In Oracle 8 this is only supported for backward compatibility.

The LONG and LONG RAW data types are still supported by USoft and Oracle for backward compatibility, but will eventually disappear.

BLOB/CLOB data types differ from LONG and LONG RAW data types in the following ways:

•Multiple BLOBs and CLOBs are allowed in a single row, which means that a table can contain multiple BLOB/CLOB columns.

•A BLOB/CLOB can be up to 4 gigabytes in size. The actual maximum is operating system dependent, but cannot exceed 4 gigabytes.

•SQL character functions are supported on CLOB fields (Oracle9 only). This means that queries can be performed to search the contents of these field types.

•BLOB/CLOB columns can be NULL or empty. NULL means there is no locator, empty means there is a locator pointing to zero-length data.

Newer features of Oracle, for example, user defined data types, are not supported in USoft .

Universal date_to_char and char_to_date functions are available for Oracle. The syntax is:

CURRENT_USER	DATABASE	DBA
INITIAL_USER	LANGUAGE	SYSTEM_USER
TERMINAL	USERNAME

Oracle SQL hints that are used in a SELECT statement are passed through to the RDBMS. An Oracle SQL hint has the following form:

Where <hint> is a hint for the Oracle RDBMS. Refer to your Oracle documentation for details.

The Rules Engine does not interpret these comments. Take in mind that the Rules Engine can change SQL statements for optimization reasons. When making these changes, the Oracle SQL hints are not taken into consideration. Therefore, the hints may not fit the resulting SQL statement.

The "orawarnings.ust" file in USoft's \app folder (app-defaults directory on UNIX) contains error messages that are to be ignored, and warning messages that are to be displayed.

•To suppress an Oracle Error message, add the error number to the orawarnings.ust file.

•To make sure that a warning message is displayed, add the code for that message to the orawarnings.ust file.

•If the orawarnings.ust file exists but is empty, all warnings are ignored, but all error messages are displayed.

For example, to ensure that the message: 'ORA-28002: the password will expire within <X> days’ is displayed, add the message number 28002 to the "orawarnings.ust" file. Note that only the number should be used, not the 'ORA-' part.

When the character set on the client is different from the database server, for example when the client uses WE8ISO8859P1 and the server uses WE8MSWIN1252, Oracle performs a character set conversion. Characters that do not exist in one of the sets will be converted to a ¿ (ASCII value 191) in the database. This might be the case for the Euro sign (ASCII value 128) and several other characters.

You can overrule a client character setting by using the environmentSet resource. To set the client character set to WE8MSWIN1252 add the following resource setting to the Application Properties in the USoft Binder:

This resource setting can be used to change any environment setting for the client. The syntax is: