Skip to main content

Search

Welcome to Tibero GTS!

How to Substitute Bind Variables (BIND_VARIABLE)

Document Type | Technical Information

Category | App Development

Applicable Product Version | 6FS06

Document Number | TDETI005

 

Overview

The way bind variables are declared differs depending on the program.
  • JAVA programs, orange for tibero: declared in the form of '?'
  • cli programs, tbsql: declared in the form of ':character'
 
When you need to execute a query in tbsql to check the plan stat information of a query executed by a JAVA program, it is necessary to replace the bind parts in the query written as '?' into the form of ':character'.
At this time, by using psm as shown below, you can simply convert it into a query executable in tbsql.    
int col1 = 1;
String col2 = "A";
Date col3 = new Date(System.currentTimeMillis());
strSQL = "select col1, col2, col3 from tibero.test where col1 = ? and col2 = ? and to_char(col3,
'yyyy/mm/dd') = ?";
This document explains, with examples, how to substitute bind variables declared as '?' in the query into bind variables that can be executed in tbsql.

 

Method

Applicable Conditions

  • When BIND_VARIABLE_CAPTURE=Y
  • When the bind variable type is number, varchar, or date
 

Execution Syntax

  1. Query the sql_id and plan_hash_value of the query you want to modify.
  2. Enter the retrieved sql_id and plan_hash_value values into the psm below and execute it.
set serverout on
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 := '';

--Enter sql_id and plan_hash_value values
v_sqlid := '4mvt49cc3tgaw';
v_plan_val := '1519877515';

--Merge the sql_text content 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 the number of binds
select regexp_count(v_str,'\?') into v_cnt from dual;
v_tmp3 := v_str;

--Bind variable declaration
--Check the latest 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;

--Replace '?' with bind variables in the query
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;
/

 

Execution Result

The most recently entered bind values are set. At this time, the bind variables are set as ':a+sequential number'.
var a1 NUMBER;
exec :a1 := 1;
var a2 VARCHAR2(32);
exec :a2 := 'A';
var a3 DATE;
exec :a3 := '2017/07/11';
select col1, col2, col3 from tibero.test where col1 = :a1 and col2 = :a2 and to_char(col3, 'yyyy/mm/dd')
= :a3
PSM completed.
 
 

Related to