Oracle教程 2023-03-21

Oracle 视图 V$SQL_BIND_DATA 官方解释，作用，如何使用详细说明

Oracle视图V$SQL_BIND_DATA记录了定义变量、DBMS_SQL.BIND_VARIABLE绑定变量和DBMS_SQL.BIND_VARIABLE_CHAR、BIND_VARIABLE_RAW等函数操作的信息，用于监控行为SQL语句是否用正确的参数绑定变量。

使用该视图可以查看绑定变量和它们的值，以及被绑定变量SQL中所用的类型和长度。如果有入参或者出参，可以在这里面看到。

使用V$SQL_BIND_DATA视图的方法及示例:

1. 查看绑定的变量以及它们的值:

SELECT b.SQL_TEXT, a.name, a.datatype_string, a.value
FROM v$sql_bind_data a, v$sql b
WHERE a.SQL_ID = b.SQL_ID
AND a.name = ‘varname’;

2. 查看存储过程里面绑定变量所使用的参数类型和长度:

SELECT name, datatype_string, length
FROM v$sql_bind_data
WHERE SQL_ID IN
(
SELECT SQL_ID
FROM v$sql
WHERE SQL_TEXT LIKE ‘%PROC_NAME%’
);

V$SQL_BIND_DATA describes information related to bind variables.

V$SQL_BIND_DATA describes, for each distinct bind variable in each cursor owned by the session querying this view:

Actual bind data, if the bind variable is user defined
The underlying literal, if the CURSOR_SHARING parameter is set to FORCE and the bind variable is system generated. (System-generated binds have a value of 256 in the SHARED_FLAG2 column.)

Column	Datatype	Description
`CURSOR_NUM`	`NUMBER`	Cursor number for this bind
`POSITION`	`NUMBER`	Bind position
`DATATYPE`	`NUMBER`	Internal identifier for the bind data type. Beginning in Oracle Database 12c, a number representing a PL/SQL data type can appear in this column.
`SHARED_MAX_LEN`	`NUMBER`	Shared maximum length for this bind from the shared cursor object associated with this bind
`PRIVATE_MAX_LEN`	`NUMBER`	Private maximum length for this bind sent from the client
`ARRAY_SIZE`	`NUMBER`	Maximum number of array elements (for array binds only)
`PRECISION`	`NUMBER`	Precision (for numeric binds)
`SCALE`	`NUMBER`	Scale (for numeric binds)
`SHARED_FLAG`	`NUMBER`	Shared bind data flags
`SHARED_FLAG2`	`NUMBER`	Shared bind data flags (continued)
`BUF_ADDRESS`	`RAW(4 \| 8)`	Bind buffer memory address
`BUF_LENGTH`	`NUMBER`	Bind buffer length
`VAL_LENGTH`	`NUMBER`	Actual bind value length
`BUF_FLAG`	`NUMBER`	Bind buffer flags
`INDICATOR`	`NUMBER`	Bind indicator
`VALUE`	`VARCHAR2(4000)`	Contents of the bind buffer
`CON_ID`	`NUMBER`	The ID of the container to which the data pertains. Possible values include: `0`: This value is used for rows containing data that pertain to the entire CDB. This value is also used for rows in non-CDBs. `1`: This value is used for rows containing data that pertain to only the root n: Where n is the applicable container ID for the rows containing data