Data Connection to Oracle

Version

Application Scenario

This document introduces how to connect to the Oracle database.

Version/Driver

Connection Information Collection

Before connecting FineBI to the database, you need to collect the following information:

• IP address and port number of the database server

• Database name

• Username and password of the database

• Schema of the database to be connected

1. Log in to FineBI as the admin, choose System Management > Data Connection > Data Connection Management, click Create, and select Data Connection, as shown in the following figure.

Note:

If you are not the admin, you can configure data connections only after the admin assigns you the permission on the data connection module. For details, see Data Connection Permission Overview.

2. Find the Oracle database, as shown in the following figure.

3. Enter the connection information collected in the "Connection Information Collection" section. To use the built-in driver, select Default from the Driver drop-down list. To use the driver uploaded by yourself, select Custom and select the driver as needed.

Click Click to Connect Database, and select the required schema, as shown in the following figure.

Note:

If you use a SQL statement to connect FineBI to a multi-schema database, you need to prefix the database name with the schema name in the statement, for example, SELECT * FROM "ABBY"."FR_S_UEP".

The following table introduces the driver, database name, and URL.

The following describes TNSName.

In the URL jdbc:oracle:thin:@TNSName, the value of TNSName is the same as that of ORCL in the tnsnames.ora file, as shown in the following figure.

The complete URL is jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST =(ADDRESS=(PROTOCOL=TCP)(HOST =localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl))).

4. Click Test Connection. If the connection is successful, click Save, as shown in the following figure.

Two methods are available to add database tables to FineBI.

• Adding Database Tables

• Adding SQL Datasets

• In Oracle9i, a field value with the precision 0 will be recognized as a text instead of a number. (For example, the field value 38 obtained by column.getSize() will be recognized as a text.) This phenomenon does not occur in the Oracle database of other versions.

• The BI platform will automatically release idle connections from the Oracle connection pool after a period of inactivity. Sometimes, the previous connections remain in the pool simply because they are reused by subsequent users. The function can prevent leakage issues caused by unreleased connections.

• If the name of a field in the Oracle database contains special characters (for example, date#$%?/2"), an error will be reported when you select the field in a self-service dataset. In this case, you need to select the driver package ojdbc14.jar to connect to the database. For details, see the "Version/Driver" section.