Document Type | Troubleshooting
Category | Patch/Upgrade
Applicable Product Versions | 6FS07PS, 7FS02PS
Document Number | TPATS008
Issue
- When bind variables are directly included in the query and executed in Tibero tbsql, it executes successfully.
- When bind variables are declared in the Tibero tbsql query and executed, it executes successfully.
- When processed via JDBC PreparedStatement, an ORA-00911 invalid character error occurs, causing execution failure.
NoteThis issue occurred in Tibero version 7.2.1.
Below is the JDBC error detail.
- java.sql.SQLException: JDBC-12126:Remote database client API error - (ORA-00911) confirmed occurrence of an unusable error
- Reproduction Scenario
<Oracle>
create table t1(c1 number, c2 varchar(8));
insert into t1 values(1, '10');
insert into t1 values(2, '20');
insert into t1 values(3, '30');
commit;
<Tibero>
create table t2(c1 number, c2 varchar(8));
insert into t2 values(1, '10');
insert into t2 values(2, '20');
insert into t2 values(3, '30');
<Create DB link>
create database link olink connect to xxx identified by 'xxx' using 'xxx';
commit;
<jdbc not used, successful execution when processing bind variables in Tibero>
var b1 varchar(10);
exec :b1 := '20';
SELECT * FROM t1@olink t1, t2 where t1.c2 = t2.c2 and t1.c2 = :b1;
<Create test.java file>
public class test {
public static void main(String[] args) {
try {
Class.forName("com.tmax.tibero.jdbc.TbDriver");
Connection conn = DriverManager.getConnection("jdbc:tibero:thin:@localhost:28485:tibero","tibero","tmax");
System.out.println("db connection!");
String sql="SELECT * FROM t1@olink t1, t2 where t1.c2 = t2.c2 and t1.c2 = ?";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setString(1, "20");
ResultSet rs=pstmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getString(1));
}
}catch(Exception e){
e.printStackTrace();
}
}
}
<Run java file>
tibero7 [develop]+>$ javac test.java
tibero7 [develop]+>$ java -classpath .:$TB_HOME/client/lib/jar/tibero7-jdbc-dbg.jar test
<Execution Result>
java.sql.SQLException: JDBC-12126:Remote database client API error - (ORA-00911) invalid character
at com.tmax.tibero.jdbc.err.TbError.makeSQLException(TbError.java:484)
at com.tmax.tibero.jdbc.err.TbError.newSQLException(TbError.java:549)
at com.tmax.tibero.jdbc.msg.common.TbMsgError.readErrorStackInfo(TbMsgError.java:109)
at com.tmax.tibero.jdbc.msg.TbMsgEreply.deserialize(TbMsgEreply.java:31)
at com.tmax.tibero.jdbc.comm.TbStream.readMsg(TbStream.java:476)
at com.tmax.tibero.jdbc.comm.TbCommType4.prepareExecute(TbCommType4.java:3082)
at com.tmax.tibero.jdbc.driver.TbPreparedStatementImpl.executeCompleteSQL(TbPreparedStatementImpl.java:1175)
at com.tmax.tibero.jdbc.driver.TbPreparedStatementImpl.executeInternal(TbPreparedStatementImpl.java:1314)
at com.tmax.tibero.jdbc.driver.TbPreparedStatementImpl.executeQuery(TbPreparedStatementImpl.java:1380)
at com.tmax.tibero.jdbc.driver.TbPreparedStatement.executeQuery(TbPreparedStatement.java:136)
at test.main(test.java:14)Cause
- When reconstructing DB Link SQL, the '?' character was included literally in the SQL, which Oracle did not recognize, causing the issue.
- The '?' part of the query received via JDBC in Tibero was sent to Oracle through the DB link, causing the error.
- Error details
$6 = 0x7fe2ac5fb980 "SELECT QB_004.\"C1\",QB_004.\"C2\" FROM \"T1\" QB_004 WHERE (QB_004.\"C2\" = ?) AND (QB_004.\"C2\" IS NOT NULL) AND (QB_004.\"C2\" IS NOT NULL) AND (QB_004.\"C2\" = :0) " โ Error occurs at this part
Solutions
Apply the patch to resolve the issue. (Applied patch: FS07PS_333742a)
The patch changes the '?' character in SQL to ':Q' (bind variable format) during SQL generation.
Since this requires a change in the SQL processing itself, there is no separate workaround available.
CautionApply the patch through technical support provided by Tmax Tibero.