Data Connection to Oracle

Version

Application Scenario

This document introduces how to connect to the Oracle database.

Version/Driver

Connection Information Collection

Before connecting 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, and click New 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 Data Connection Management. For details, see Data Connection Permissions Overview.

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

3. Enter the connection information collected in section "Connection Information Collection." 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 you need.

Schema is available after the database is connected by clicking Click to Connect Database, as shown in the following figure.

Note:

If you use a SQL statement to connect 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 describes the driver, database name, and URL.

Driver	Database Name	URL
oracle.jdbc.driver. OracleDriver	Sid_name	jdbc:oracle:thin:@IP address:Port number:Sid_name
	Service_name	jdbc:oracle:thin:@//IP address:Port number/Service_name or jdbc:oracle:thin:@IP address:Port number/Service_name
	TNSname Note: The tnsnames.ora file must exist in the Oracle installation path.	jdbc:oracle:thin:@TNSName

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 the Oracle9i version, a field value with the 0 precision will be recognized as a text instead of a number. (For exmaple, the field value 38 obtained by column.getSize() will be recognized as a text.) This phenomenon does not occur in other versions of Oracle.

• Data connections in the Oracle connection pool that are idle for a period of time in FineBI will be automatically released. Previous connections still exist in the connection pool because they are re-enabled. Thus, no problem will occur even when connections are not released for a long time.

• If the name of a field in Oracle contains special characters (for example, date#$%?/2"), an error shown in the following figure 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 section "Version/Driver."