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();
       }
   }
}

[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;
/