Document Type | Technical Information
Category | Monitoring/Inspection
Applicable Product Version | 7FS04PS
Document Number | TMOTI043
Overview
Test Environment
| Server Name | OS version | DB Version | DB Name | Port | Username | IP |
|---|---|---|---|---|---|---|
| Tibero | CentOS 8.4.2105 | Tibero 7.2.4 | tibero | 8629 | sys | 172.16.59.110 |
Method
The method of declaring bind variables differs depending on the program.
Example)
- Java programs, orange for tibero: declared in '?' form
- cli programs, tbsql: declared in ':character' form
Therefore, if you need to execute the query in tbsql to check the plan stat information of a query run in a Java program, you must substitute the bind variables in '?' form within the query to ':character' form.
In such cases, using the following PSM allows you to easily convert the query into one executable in tbsql.
Example of Using Bind Variables in a Java Program
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Date;
public class bind_capture {
public static void main(String[] args) {
// DB connection information
String url = "jdbc:tibero:thin:@localhost:8629:tibero0";
String user = "sys";
String password = "tibero";
// Variables to query
int col1 = 1;
String col2 = "A";
Date col3 = new Date(System.currentTimeMillis());
// SQL statement
String sql = "SELECT col1, col2, col3 "
+ "FROM tibero.test "
+ "WHERE col1 = ? "
+ "AND col2 = ? "
+ "AND TO_CHAR(col3, 'yyyy/mm/dd') = ?";
// Driver class name: Tibero JDBC driver required (tibero6-jdbc.jar or tibero7-jdbc.jar)
try {
Class.forName("com.tmax.tibero.jdbc.TbDriver");
try (Connection conn = DriverManager.getConnection(url, user, password);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, col1);
pstmt.setString(2, col2);
pstmt.setString(3, col3.toString().replace("-", "/")); // Match yyyy/mm/dd format
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
int r1 = rs.getInt("col1");
String r2 = rs.getString("col2");
Date r3 = rs.getDate("col3");
System.out.println("col1=" + r1 + ", col2=" + r2 + ", col3=" + r3);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
Execution Statement
Check SQL_ID and PLAN_HASH_VALUE executed by Java program
[tibero@kimmi java_test]$ tbsql sys/tibero tbSQL 7 TmaxTibero Corporation Copyright (c) 2020-. All rights reserved. Connected to Tibero. SQL> select * from v$sqltext where sql_text like '%SELECT col1, col2, col3%'; HASH_VALUE PLAN_HASH_VALUE SQL_ID CHILD_NUMBER COMMAND_TYPE PIECE SQL_TEXT ---------- --------------- ------------- ------------ ------------ ---------- ---------------------------------------------------------------- 207937103 3704774042 5qkffdw669rkg 1484 1 0 SELECT col1, col2, col3 FROM tibero.test WHERE col1 = ? AND col2 1 row selected.
Execute Statement
The values for v_sqlid and v_plan_val in the execution statement should be entered based on the results of the previously queried data.
SQL> set serverout on
SQL> DECLARE
v_cnt number;
v_str varchar(4000);
v_tmp1 varchar(4000);
v_tmp2 varchar(4000);
v_tmp3 varchar(4000);
v_sqlid varchar(13);
v_plan_val number;
v_type varchar(128);
v_len number;
v_val varchar(4000);
BEGIN
v_cnt := 0;
v_str := '';
v_tmp1 := '';
v_tmp2 := '';
v_tmp3 := '';
v_sqlid := '';
v_plan_val := 0;
v_type := '';
v_len := 0;
v_val := '';
-- Input sql_id and plan_hash_value values
v_sqlid := '5qkffdw669rkg';
v_plan_val := '3704774042';
-- Merge sql_text contents with the same sql_id and plan_hash_value into v_str
FOR l_cnt IN (select sql_text from V$SQLTEXT_WITH_NEWLINES2 where sql_id = v_sqlid and plan_hash_value = v_plan_val order by piece) LOOP
v_str := v_str || chr(13)||chr(10) || l_cnt.sql_text;
END LOOP;
-- Check number of bind variables
select regexp_count(v_str,'\?') into v_cnt from dual;
v_tmp3 := v_str;
-- Declare bind variables
-- Check last captured bind values from V$SQL_BIND_CAPTURE_ALL
FOR l_cnt IN 1..v_cnt LOOP
select type_name, length, value into v_type, v_len, v_val from (select type_name, length, value, time_captured as time_captured from V$SQL_BIND_CAPTURE_ALL where sql_id = v_sqlid and plan_hash_value = v_plan_val and position = l_cnt-1 group by sql_hash_value, position, type_name, length, value, time_captured order by time_captured desc) where rownum = 1;
IF v_type = 'VARCHAR2' THEN
v_tmp1 := 'var a'||l_cnt||' ' || v_type || '(' || v_len || ')' || ';';
v_tmp2 := 'exec :a'||l_cnt||' := ' || '''' || v_val || ''';';
ELSIF v_type = 'DATE' THEN
v_tmp1 := 'var a'||l_cnt||' ' || v_type || ';';
v_tmp2 := 'exec :a'||l_cnt||' := ' || '''' || v_val || ''';';
ELSE
v_tmp1 := 'var a'||l_cnt||' ' || v_type || ';';
v_tmp2 := 'exec :a'||l_cnt||' := ' || v_val || ';';
END IF;
dbms_output.put_line (v_tmp1);
dbms_output.put_line (v_tmp2);
END LOOP;
-- Substitute ? in query with bind variables
FOR l_cnt IN 1..v_cnt LOOP
select regexp_replace(v_tmp3,'\?',':a'||l_cnt,1,1) into v_tmp3 from dual;
END LOOP;
dbms_output.put_line ('');
dbms_output.put_line (v_tmp3);
EXCEPTION
WHEN NO_DATA_FOUND THEN
null;
END;
/
Result
var a1 NUMBER; exec :a1 := 1; var a2 VARCHAR2(32); exec :a2 := 'A'; var a3 VARCHAR2(32); exec :a3 := '2025/11/06'; SELECT col1, col2, col3 FROM tibero.test WHERE col1 = :a1 AND col2 = :a2 AND TO_CHAR(col3, 'yyyy/mm/dd') = :a3 PSM completed.
Note
- Can be used if BIND_VARIABLE_CAPTURE=Y
- Only applicable when bind variable types are number, varchar, or date