Document Type | Technical Information
Category | Monitoring/Inspection
Applicable Product Version | 7FS02PS
Document Number | TMOTI002
Overview
This explains methods to check the values of bind variables.
- Using view
- Using ilog
Method
Checking using view
The BIND_VARIABLE_CAPTURE parameter must be set to YES.
You can check it in V$SQL_BIND_CAPTURE_ALL.
SQL> select * from V$SQL_BIND_CAPTURE_ALL; SQL_HASH_VALUE PLAN_HASH_VALUE SQL_ID CHILD_NUMBER USER_ID NAME POSITION TYPE -------------- --------------- ------------- ------------ ---------- -------------------- ---------- ---------- TYPE_NAME PRECISION SCALE LENGTH TIME_CAPTURED VALUE -------------------- ---------- ---------- ---------- --------------- -------------------- 3980673988 3845467455 9a1kqxzqn8hy4 124 18 ? 2 5 DATE 0 0 8 2025/03/12 2025/03/12 3980673988 3845467455 9a1kqxzqn8hy4 124 18 ? 1 3 VARCHAR2 0 0 32 2025/03/12 PARK 3980673988 3845467455 9a1kqxzqn8hy4 124 18 ? 0 1 NUMBER 38 -128 22 2025/03/12 1 3 rows selected.
#. Query to check along with SQL_TEXT SQL> select a.SQL_ID, a.USER_ID, a.NAME, a.TYPE_NAME, a.VALUE, b.sql_text from V$SQL_BIND_CAPTURE_ALL a, v$sql b where a.SQL_ID = b.SQL_ID; #. Example) SQL> select a.SQL_ID, a.USER_ID, a.NAME, a.TYPE_NAME, a.VALUE, b.sql_text from V$SQL_BIND_CAPTURE_ALL a, v$sql b where a.SQL_ID = b.SQL_ID; SQL_ID USER_ID NAME TYPE_NAME VALUE SQL_TEXT ------------- ---------- ---------- --------------- -------------------- -------------------------------------------------- 9a1kqxzqn8hy4 18 ? DATE 2025/03/12 SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?,'YYYY/MM/DD') 9a1kqxzqn8hy4 18 ? VARCHAR2 PARK SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?,'YYYY/MM/DD') 9a1kqxzqn8hy4 18 ? NUMBER 1 SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?,'YYYY/MM/DD') 3 rows selected.
Checking using ilog
Connect to Tibero and activate the following TAG list. TAG activation is done at the System level, and the activation level is set to Detail.
SQL>ALTER SYSTEM ILOG ENABLE NAME 'BODY_MSG_CSR_BIND_PARAM_INFO' LEVEL DETAIL; SQL>ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG_UNKNOWN' LEVEL DETAIL; SQL>ALTER SYSTEM ILOG ENABLE NAME 'BODY_SQL_PROCESS' LEVEL DETAIL; SQL>ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG_NULL' LEVEL DETAIL; SQL>ALTER SYSTEM ILOG ENABLE NAME 'CSR_USERPARAM_TO_STRNLOG' LEVEL DETAIL;
$ cd /$TB_HOME/instance/$TB_SID/log/ilog $ ls -alt total 12844 -rw-r--r-- 1 jinhwa jinhwa 65536 Mar 13 13:16 tibero7_2_2_287079-91.ilg -- omitted -- $ tbiv tibero7_2_2_287079-91.ilg -- omitted -- 03/13 13:16:40.5 0-0091 tbsvr_sq:277 SQL PROCESS: sql=SELECT * FROM test01 WHERE id = ? OR name = ? OR TO_CHAR(day, 'YYYY/MM/DD') = TO_CHAR(?, 'YYYY/MM/DD'), csr=2 03/13 13:16:40.5 0-0091 tbsvr_ms:955 BINDING[CSR_ID=2]: param #0, param_kind=IN, param_type=TB_TYPE_NUMBER 03/13 13:16:40.5 0-0091 csr.c:3761 BINDING[CSR_ID=2]: param #0: param_val_len=1, param_val: [1] 03/13 13:16:40.5 0-0091 tbsvr_ms:955 BINDING[CSR_ID=2]: param #1, param_kind=IN, param_type=TB_TYPE_VARCHAR 03/13 13:16:40.5 0-0091 csr.c:3761 BINDING[CSR_ID=2]: param #1: param_val_len=4, param_val: [PARK] 03/13 13:16:40.5 0-0091 tbsvr_ms:955 BINDING[CSR_ID=2]: param #2, param_kind=IN, param_type=TB_TYPE_DATE 03/13 13:16:40.5 0-0091 csr.c:3761 BINDING[CSR_ID=2]: param #2: param_val_len=10, param_val: [2025/03/13] Completed.