[20221030]绑定变量的分配长度10.txt

来源：这里教程网时间：2026-03-03 18:04:59 作者：

[20221030]绑定变量的分配长度10.txt --//如果绑定变量中字符串分配占用空间的长度变化，oracle会建立子光标。 --//测定nvarchar2类型的情况. --//varchar2类型的情况. 链接 http://blog.itpub.net/267265/viewspace-2056695/ 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SCOTT@test01p> create table t (c1 nvarchar2(32767)); create table t (c1 nvarchar2(32767)) * ERROR at line 1: ORA-00910: specified length too long for its datatype SCOTT@test01p> @ calcx 32767/2 DEC HEX ----------------------------------- -------------------- 16383.500000 0000000000004000 SCOTT@test01p> create table t (c1 nvarchar2(16384)); create table t (c1 nvarchar2(16384)) * ERROR at line 1: ORA-00910: specified length too long for its datatype SCOTT@test01p> create table t (c1 nvarchar2(16383)); Table created. declare v_c1 nvarchar2(16383); begin for i in 1..16383 loop v_c1 := rpad('X',i); execute immediate 'select count(*) from t where c1=:instring' using v_c1 ; end loop; end; / commit; --//确定sql_id='9mrd273576n14' 2.看看结果: SCOTT@test01p> @ bind_cap 9mrd273576n14 '' SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING ------------- ------------ --- -------------------- ---------- ---------- ------------------- --------------- ------------ 9mrd273576n14 0 YES :INSTRING 1 32 2022-10-30 21:33:47 NVARCHAR2(32) X 1 YES :INSTRING 1 128 2022-10-30 21:33:47 NVARCHAR2(128) X 2 YES :INSTRING 1 2000 2022-10-30 21:33:47 NVARCHAR2(2000) X SYS@test> @ hide _cursor_bind_capture_area_size NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ------------------------------ -------------------------------------------- ------------- ------------- ---------------- _cursor_bind_capture_area_size maximum size of the cursor bind capture area TRUE 400 400 --//受隐含参数_cursor_bind_capture_area_size大小控制的影响,仅仅显示3个子光标. SCOTT@test01p> select sql_id, child_number, executions from v$sql where sql_id = '9mrd273576n14'; SQL_ID CHILD_NUMBER EXECUTIONS ------------- ------------ ---------- 9mrd273576n14 0 16 9mrd273576n14 1 48 9mrd273576n14 2 936 9mrd273576n14 3 1000 9mrd273576n14 4 2096 9mrd273576n14 5 4097 9mrd273576n14 6 8190 7 rows selected. --//因为nvarchar2类型一个英文字符占用2个字节. SCOTT@test01p> select sql_id, child_number,executions, sum(executions) over (partition by sql_id order by child_number) N20 from v$sql where sql_id = '9mrd273576n14' order by child_number; SQL_ID CHILD_NUMBER EXECUTIONS N20 ------------- ------------ ---------- ---------- 9mrd273576n14 0 16 16 9mrd273576n14 1 48 64 9mrd273576n14 2 936 1000 9mrd273576n14 3 1000 2000 9mrd273576n14 4 2096 4096 9mrd273576n14 5 4097 8193 9mrd273576n14 6 8190 16383 7 rows selected. --//如果varchar2类型按照 32,128,2000,4000,8192,16386来分段.这样对于我当前字符集环境nvarchar2就是在原来基础除以2. --//Nvarchar2类型按照 16,64,1000,2000,4096,8193. --//有一个矛盾的地方@ bind_cap 9mrd273576n14 '' 显示的是NVARCHAR2(32).而实际上这里的32标识DATA_LENGTH. SCOTT@test01p> select * from dba_tab_cols where owner='SCOTT' and table_name='T' 2 @ prxx ============================== OWNER : SCOTT TABLE_NAME : T COLUMN_NAME : C1 DATA_TYPE : NVARCHAR2 DATA_TYPE_MOD : DATA_TYPE_OWNER : DATA_LENGTH : 32766 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DATA_PRECISION : DATA_SCALE : NULLABLE : Y COLUMN_ID : 1 DEFAULT_LENGTH : DATA_DEFAULT : NUM_DISTINCT : LOW_VALUE : HIGH_VALUE : DENSITY : NUM_NULLS : NUM_BUCKETS : LAST_ANALYZED : SAMPLE_SIZE : CHARACTER_SET_NAME : NCHAR_CS CHAR_COL_DECL_LENGTH : 16383 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ GLOBAL_STATS : NO USER_STATS : NO AVG_COL_LEN : CHAR_LENGTH : 16383 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CHAR_USED : C V80_FMT_IMAGE : NO DATA_UPGRADED : YES HIDDEN_COLUMN : NO VIRTUAL_COLUMN : NO SEGMENT_COLUMN_ID : 1 INTERNAL_COLUMN_ID : 1 HISTOGRAM : NONE QUALIFIED_COL_NAME : C1 USER_GENERATED : YES DEFAULT_ON_NULL : NO IDENTITY_COLUMN : NO SENSITIVE_COLUMN : NO EVALUATION_EDITION : UNUSABLE_BEFORE : UNUSABLE_BEGINNING : COLLATION : USING_NLS_COMP COLLATED_COLUMN_ID : PL/SQL procedure successfully completed. --//注意看下划线内容. SCOTT@test01p> @ desc t Name Null? Type ------ -------- ---------------------------- C1 NVARCHAR2(16383) SCOTT@test01p> @ desczz t 1 eXtended describe of t DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT OWNER.TABLE_NAME <filters> SAMPLE : @ desczz TABLE_NAME column_name1,column_name2 IF NOT INPUT <filters> ,USE "" . Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value ---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ------------------ SCOTT T 1 C1 NVARCHAR2(32766) --//desczz脚本显示的就是dba_tab_cols.DATA_LENGTH信息. --//如果你使用toad的scahme broswer的script看表定义: CREATE TABLE SCOTT.DEPT ( DEPTNO NUMBER(2), DNAME VARCHAR2(14 BYTE), LOC VARCHAR2(13 BYTE) ) --//dname数字后跟一个byte. CREATE TABLE t1 AS SELECT object_id ,object_name ,CAST (object_name AS NVARCHAR2 (128)) vobject_name FROM all_objects where rownum<=3; --//toad的scahme broswer的script看表定义如下: CREATE TABLE SCOTT.T1 ( OBJECT_ID NUMBER NOT NULL, OBJECT_NAME VARCHAR2(30 BYTE) NOT NULL, VOBJECT_NAME NVARCHAR2(128) NOT NULL ) --//nvarchar2(没有带byte),实际上这个定义的是CHAR_LENGTH. SYS@book> select * from dba_tab_cols where owner='SCOTT' and table_name='T1' and column_name='VOBJECT_NAME' 2 @ pr ============================== OWNER : SCOTT TABLE_NAME : T1 COLUMN_NAME : VOBJECT_NAME DATA_TYPE : NVARCHAR2 DATA_TYPE_MOD : DATA_TYPE_OWNER : DATA_LENGTH : 256 DATA_PRECISION : DATA_SCALE : NULLABLE : N COLUMN_ID : 3 DEFAULT_LENGTH : DATA_DEFAULT : NUM_DISTINCT : 3 LOW_VALUE : 0043004F004E0024 HIGH_VALUE : 6D4B8BD54E2D6587 DENSITY : .333333333333333 NUM_NULLS : 0 NUM_BUCKETS : 1 LAST_ANALYZED : 2022-10-12 22:00:07 SAMPLE_SIZE : 3 CHARACTER_SET_NAME : NCHAR_CS CHAR_COL_DECL_LENGTH : 128 GLOBAL_STATS : YES USER_STATS : NO AVG_COL_LEN : 11 CHAR_LENGTH : 128 CHAR_USED : C V80_FMT_IMAGE : NO DATA_UPGRADED : YES HIDDEN_COLUMN : NO VIRTUAL_COLUMN : NO SEGMENT_COLUMN_ID : 3 INTERNAL_COLUMN_ID : 3 HISTOGRAM : NONE QUALIFIED_COL_NAME : VOBJECT_NAME PL/SQL procedure successfully completed. SYS@book> insert into scott.t1 values (1,'x',lpad('x',128,'x')); 1 row created. SYS@book> insert into scott.t1 values (1,'x',lpad('x',129,'x')); insert into scott.t1 values (1,'x',lpad('x',129,'x')) * ERROR at line 1: ORA-12899: value too large for column "SCOTT"."T1"."VOBJECT_NAME" (actual: 129, maximum: 128)