Skip to main content

Search

Welcome to Tibero GTS!

How to Use the BIND_VARIABLE_CAPTURE Feature

Document Type | Technical Information

Category | Administration

Document Number | TADTI076

 

Overview

The BIND_VARIABLE_CAPTURE feature stores the values of bind variables used during SQL execution so that they can be reviewed.
 

Key Parameters

  • BIND_VARIABLE_CAPTURE_MAX_COUNT : Maximum number of bind variables to store
  • BIND_VARIABLE_CAPTURE_INTERVAL : Interval (in seconds) for capturing bind variable values
  • BIND_VARIABLE_CAPTURE_MAX_VAR_COUNT : Maximum number of bind variables that can be stored per query
  • v$sql_bind_capture : Allows viewing bind variable values for SQL executed by the current user (based on userid)
  • v$sql_bind_capture_all : Allows viewing bind variable values for all users (access permission: SYS)
 
 

Method

Check Related View Structures

Bind variable values can be checked in dedicated views, and the structures of the two views are as follows.
 
v$sql_bind_capture
SQL> DESC v$sql_bind_capture;

COLUMN_NAME       TYPE
----------------- ------------------
SQL_ID            NUMBER
USER_ID           NUMBER
NAME              VARCHAR(128)
POSITION          NUMBER
TYPE              NUMBER
TYPE_NAME         VARCHAR(128)
PRECISION         NUMBER
SCALE             NUMBER
LENGTH            NUMBER
TIME_CAPTURED     DATE
VALUE             VARCHAR(4000)
 
v$sql_bind_capture_all
You can view bind variable values for all users.
SQL> DESC v$sql_bind_capture_all;

COLUMN_NAME       TYPE
----------------- ------------------
SQL_ID            NUMBER
USER_ID           NUMBER
NAME              VARCHAR(128)
POSITION          NUMBER
TYPE              NUMBER
TYPE_NAME         VARCHAR(128)
PRECISION         NUMBER
SCALE             NUMBER
LENGTH            NUMBER
TIME_CAPTURED     DATE
VALUE             VARCHAR(4000)


Enable the Feature

Enable the BIND_VARIABLE_CAPTURE feature by changing the parameters.

SQL> ALTER SYSTEM SET BIND_VARIABLE_CAPTURE=Y;

System altered;

SQL> SHOW PARAM BIND;

NAME                                 TYPE     VALUE
------------------------------------ -------- --------------------------------
BIND_VARIABLE_CAPTURE                Y_N      YES
BIND_VARIABLE_CAPTURE_INTERVAL       UINT32   600
BIND_VARIABLE_CAPTURE_MAX_COUNT      UINT32   1000
BIND_VARIABLE_CAPTURE_MAX_VAR_COUNT  UINT32   512

 

Test the Feature

Declare Bind Variables and Assign Values
SQL> VAR num NUMBER;
SQL> EXEC :num := 123456789;

PSM completed.

SQL> VAR test VARCHAR2(20);
SQL> EXEC :test := 'TmaxData/Tibero';

PSM completed.

Directly Query Bind Variables
SQL> SELECT :test FROM dual;

TEST
-------------------------
TmaxData/Tibero

SQL> SELECT :num FROM dual;

NUM
----------
123456789
 
Check Bind Variable Values in the Views
SQL> SELECT name, value FROM v$sql_bind_capture;

NAME                 VALUE
-------------------- --------------------
test                TmaxData/Tibero
num                 123456789

SQL> SELECT name, value FROM v$sql_bind_capture_all;

NAME                 VALUE
-------------------- --------------------
test                TmaxData/Tibero
num                 123456789

 

 
 

Related to