Skip to Content
Information
This version of the product has reached end of support. The documentation is available for your convenience. However, you must be logged in to access it. You will not be able to leave comments.

Using Oracle with AR System

Remember the following considerations when using an Oracle database with AR System.

Diary and Character field qualifications

When specifying search criteria, you cannot use diary fields or character fields that contain more than 4000 characters. The database system does not support qualifications on these field types. If you specify a qualification for one of these field types, you receive an error.

When the Oracle-Search-On-Clob setting option in the AR System Configuration Generic UI form is set to true (the default), you can perform a string search (without wildcards) on these field types. For more information, see Configuration-settings-N-R.  

For searches on database entries, the only wildcard character supported in the LIKE comparison is the percent symbol (%). There is no support for sets or ranges of values.

When used with the LIKE operator, the underscore (_) character cannot function as a wildcard or as literal text.

Wildcards are fully supported in filter, escalation, and active link qualifications and in pattern specifications for character fields.

Error
Warning

If you use an Oracle AR System database with the Unicode database option, problems might occur if the NLS_LENGTH_SEMANTICS parameter is not set to BYTE in the AR System database and in the database server instance. See Preparing to install on a Unicode database in BMC Helix Innovation Suite Deployment documentation.

If you try to save an empty value to any character field, Oracle stores the value as a null value and not as an empty value.

Case sensitivity in queries

  • For SaaS subscribers, the Oracle database is case insensitive
  • For  on-premises customers the Oracle database dumps are case insensitive.  


JDBC query with LPAD and RPAD functions

When using an Oracle database, if you use a JDBC query with the LPAD and RPAD functions, the following error occurs:

ERROR (552): The SQL database operation failed.; ORA-00932: inconsistent data types: expected CHAR got NUMBER The LPAD and RPAD functionality is supported for character fields only and not supported for integer fields.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

AR System 21.3