Oracle

Collaborator supports Oracle 11gR2, 12c, 18c, 19c.

Oracle's extended support for the Oracle 11g database will end on December 31, 2020 and Collaborator will no longer be able to support it.

• Oracle Database – Oracle database server.
• Oracle JDBC drivers – Database driver for Java platforms.

Create a database for Collaborator. It is recommended that you also create a username and password pair just for Collaborator and give this account full access to the database and no access to other databases.

Install the CTX_DDL PL/SQL package on your DB server and grant the EXECUTE privileges on CTX_DDL to your Oracle user of Collaborator Server. This package allows use of the Oracle Text component instead of regular expression query for full-text searches from the Web Client. By default, Oracle databases search based on regular expressions. If the default settings do not suit your requirements, you can change them on the Oracle side or through Collaborator admin UI. Once done, restart your Collaborator server to complete its upgrade.

When installing Collaborator on a server, the installation wizard will prompt for the Oracle server host name, TCP/IP port (default is 1521), database service name (not the SID), user name, password and path to JDBC driver. The installer will not report any connectivity errors.

The database service name is not the same thing as the SID. This change was made by Oracle in version 9iR2. The database service name is fully-qualified, corresponding to GLOBAL_DBNAME in the .ora file – for example, mysid.mydomain.com. The database service name is also sometimes referred to as TNS alias or connect descriptor. The SID is the shorter name, corresponding to SID_NAME in the .ora file.

Note: When you first visit the web page for Collaborator, it will detect that you have a new database and will create all tables, indexes, and views for you automatically, or give you an appropriate error message if there is a connectivity problem. Connectivity problems should be resolved by re-running the installer.

• Oracle 11g server with non-English locale: An error may occur when initializing Collaborator database on Oracle 11g servers with non-English locale. To avoid the problem:

  1. Append the following Java VM options to the <Collaborator Server>/ccollab-server.vmoptions file:

     -Duser.country=US
     -Duser.language=en

  2. Restart Collaborator server to apply the changes.

  3. Open the Collaborator web client and proceed with database initialization.

• Double quote characters (") in custom field names may break Oracle reporting views. Custom field names become column headers in the views, and Oracle does not allow double quotes in column names. Because of this, Collaborator removes double quote characters from the names of custom fields when it creates reporting views for Oracle databases.
  If some custom field names differ only by double quotes, this would result in an "ORA-00957: duplicate column name" error in server logs. To resolve the issue, you may either remove one of duplicate column names from the reporting view, or rename the custom fields to avoid coincidence.

• Loading large dump files to Oracle may take up to a full day to complete. This appears to be an issue with Oracle driver and is under investigation.

• On Oracle databases, Collaborator does not search the contents of custom fields by default (since this significantly reduces search performance). Instead, the search results page display additional fields that define in what areas to perform new search. In this panel you can enable searching in custom fields. Also, predefined search scopes can be configured via VM options.

We use the Oracle JDBC driver to connect to your Oracle database. The driver has a few undocumented behaviors that may come as a surprise. There are threads on the Oracle tech support forums about this.

Most of the problems arise in the GLOBAL_DBNAME field in your SID_DESC entry from your listener.ora file. A typical entry might look like this:

Most other Oracle-based programs use the SID_NAME field to identify the database, but the JDBC driver uses GLOBAL_DBNAME. This would cause a connection error in the example above.