Solutions to common errors in Oracle

This article introduces some errors and solutions in Oracle data connection.

In the Oracle 9i version, the field value with a precision of 0 will be recognized as text instead of a value. "The field value calculated with column.getSize() is 38, so it will be recognized as a text attribute"; other versions of Oracle do not have this problem.

The BI platform Oracle connection pool will be automatically released if it is not used for a period of time. The reason why you check that the connection pool has previous connections is because the user is re-enabled when the user connects later, so there will be no problems because the connection has not been released.

2.1 Errors

• In versions prior to 2019-01-15, if real-time data is enabled for the Spider engine and directly connected to the Oracle database, fields with more than 22 bits need to be added to FineBI using SQL statement escape. The reason is that the FineBI direct connection will add 8 characters in front of the field, and Oracle will report an error ORA-000972 when adding the table to FineBI when the identifier exceeds 30 digits.

Note: This problem has been fixed in the FineBI 5.1 version after 2019-01-15. You can turn on real-time data and directly add up to 30-digit field/table names to FineBI.

SQL escape statements can be used: select "field name with more than 22 bits" as "escaped name" from "table name", as shown in the following figure:

• All errors that begin with ORAare Oracle database errors. You can search for the error information on the network to troubleshoot the error. As shown below:

2.2 OCS configuration failed

Problem description:

Oracle19c data connection error: create connection error java.lang.IllegalArgumentException: ONS configuration failed

As shown below:

Solution:

Linux/Unix modify thestartWebLogic.sh, search for "setDomainENV.sh", and then insert "JAVA_OPTIONS="-Doracle.jdbc.fanEnabled = false"" in the upper line of the sentence ".${DOMAIN_HOME}", as shown in the figure below :

2.3 Table name/header garbled

Problem description:

Add DB table in Oracle data connection, table name/header is garbled.

Solution:

Change the encoding of the Oracle database to SIMPLIFIED CHINESE_CHINA.ZHS16GBK

2.4 Oracle 11 cannot read DB table

Problem description:

Oracle11 cannot read the DB table, and the log error is shown in the following figure:

Solution:

Click to download:

orai18n.jar

, put orai18n.jar under webapps\webroot\WEB-INF\lib of the project, and restart FineBI.

2.5 Got minus one from a read call

Problem description:

Data connection failed, error: Got minus one from a read call

Cause analysis:

When connecting to the database, it is connected through the connection pool mechanism. The database parameter: max-session determines the size of the connection pool, and the application also has a parameter that indicates the minimum resources it takes to connect to the database connection pool.

For example: There are a total of 10 applications that need to connect to the database. If the minimum number of connections for each application to connect to the database is 10, then there will be a total of 100 connections for 10 applications. This requires that the max-session of the database connection pool must be greater than 100, otherwise it will report Got minus one from a read call error.

Solution:

1) View the parameters: processes and sessions

The SQL statements are:

show parameter processes
show parameter sessions

2) Modify the values of processes and sessions

The SQL statements are:

alter system set processes=300 scope=spfile;
alter system set sessions=335 scope=spfile;

3) Restart Oracle

The SQL statements are:

shutdown immediate;
startup;

2.6 Cannot select pattern after connection successfully

Problem description:

Oracle version 9.2.0.4, using the JAR of 2020-01-15, after the connection is successful, the pattern cannot be selected, and the value is not displayed.

Cause analysis:

The driver version is not compatible.

Solution:

Change to a supported driver according to the actual situation, download link: Official driver download link

Example: Change to ojdbc6 driver to display data and select pattern normally.

2.7 No matching authentication protocol

Problem description:

Oracel data connection error: ORA-28040: No matching authentication protocol；

Cause analysis:

The driver version is not compatible.

Solution:

Click to download:

ojdbc7.jar, put ojdbc7.jar underwebapps\webroot\WEB-INF\lib of the project, and restart FineBI.