drop table t;
create table t(id varchar2(4000));
insert into t select rownum from all_objects where rownum <= 10;
insert into t select lpad(rownum,4000) from all_objects where rownum <= 10;
commit;
--alter session set events '10503 trace name context forever, level 4000';
select address, max_length
from v$sql_bind_metadata b
where b.address in (select child_address from v$sql
where sql_text = 'select * from t where id = :x');
var x varchar2(30)
exec :x := 'asdf'
select * from t where id = :x;
var x varchar2(100)
exec :x := 'asdf'
select * from t where id = :x;
var x varchar2(2500)
exec :x := 'asdf'
select * from t where id = :x;
select address, max_length
from v$sql_bind_metadata b
where b.address in (select child_address from v$sql
where sql_text = 'select * from t where id = :x');
SQL> drop table t;
Table dropped.
SQL> create table t(id varchar2(4000));
Table created.
SQL> insert into t select rownum from all_objects where rownum <= 10;
10 rows created.
SQL> insert into t select lpad(rownum,4000) from all_objects where rownum <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> select address, max_length
2 from v$sql_bind_metadata b
3 where b.address in (select child_address from v$sql
4 where sql_text = 'select * from t where id = :x');
no rows selected
SQL>
SQL> var x varchar2(30)
SQL> exec :x := 'asdf'
PL/SQL procedure successfully completed.
SQL> select * from t where id = :x;
no rows selected
SQL>
SQL> var x varchar2(100)
SQL> exec :x := 'asdf'
PL/SQL procedure successfully completed.
SQL> select * from t where id = :x;
no rows selected
SQL>
SQL> var x varchar2(2500)
SQL> exec :x := 'asdf'
PL/SQL procedure successfully completed.
SQL> select * from t where id = :x;
no rows selected
SQL>
SQL> select address, max_length
2 from v$sql_bind_metadata b
3 where b.address in (select child_address from v$sql
4 where sql_text = 'select * from t where id = :x');
ADDRESS MAX_LENGTH
---------------- ----------
00000000F2EA2830 32
00000000F2EEBE40 128
00000000F2F1AEC8 4000
SQL> drop table t;
Table dropped.
SQL> create table t(id varchar2(4000));
Table created.
SQL> insert into t select rownum from all_objects where rownum <= 10;
10 rows created.
SQL> insert into t select lpad(rownum,4000) from all_objects where rownum <= 10;
10 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> alter session set events '10503 trace name context forever, level 4000';
Session altered.
SQL>
SQL> select address, max_length
2 from v$sql_bind_metadata b
3 where b.address in (select child_address from v$sql
4 where sql_text = 'select * from t where id = :x');
no rows selected
SQL>
SQL> var x varchar2(30)
SQL> exec :x := 'asdf'
PL/SQL procedure successfully completed.
SQL> select * from t where id = :x;
no rows selected
SQL>
SQL> var x varchar2(100)
SQL> exec :x := 'asdf'
PL/SQL procedure successfully completed.
SQL> select * from t where id = :x;
no rows selected
SQL>
SQL> var x varchar2(2500)
SQL> exec :x := 'asdf'
PL/SQL procedure successfully completed.
SQL> select * from t where id = :x;
no rows selected
SQL>
SQL> select address, max_length
2 from v$sql_bind_metadata b
3 where b.address in (select child_address from v$sql
4 where sql_text = 'select * from t where id = :x');
ADDRESS MAX_LENGTH
---------------- ----------
00000000F2ADF150 4000