The relationship between <length1> and <length2> in

SQL> var myvar varchar2(<length1>)
SQL> exec :myvar := rpad('X', <length2>)

is <length2> = 3 * <length1>. If "(<length1>)" is omitted, "varchar2" the same as "varchar2(1)".






The same SQL, say, SELECT * FROM BOOK WHERE DESCRIPTION = x:, has one entry
in v$sqlarea with version_count > 1, therefore multiple entries in v$sql. Why?
One common reason is bind variable bind length exceeding threshold.[Ref. James
Morle's "Scaling Oracle8i", pp.278-9] E.g., in one case :x is bound to 'age',
the other case to 'a string longer than 32 characters'.

The following is an experiment on 9i, Windows XP.

Oracle9i Enterprise Edition Release 9.0.1.3.1 - Production
With the Partitioning option
JServer Release 9.0.1.3.0 - Production

SQL> alter system flush shared_pool;

System altered.

SQL> print
SP2-0568: No bind variables declared.
SQL> var s varchar2(32)
SQL> print	<-- SQL*Plus print command can tell you what bind variable you have created even before you bind a value to it.

S
--------------------------------


SQL> exec :s := 'x'

PL/SQL procedure successfully completed.

SQL> select * from v$sql_bind_metadata where bind_name = 'S';

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
-------- ---------- ---------- ---------- ---------- ------------------------------
7D3D4708          1          1         32          0 S
7C92E34C          1          1         32          0 S

SQL> select sql_text from v$sql where child_address in ('7D3D4708','7C92E34C');

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT :s s FROM DUAL		<-- due to PRINT
BEGIN :s := 'x'; END;		<-- due to EXEC :S := 'x'

SQL> var s varchar2(33)
SQL> print

S
--------------------------------------------------------------------------------------------------------------------------------


SQL> exec :s := 'x'

PL/SQL procedure successfully completed.

SQL> select * from v$sql_bind_metadata where bind_name = 'S';

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
-------- ---------- ---------- ---------- ---------- ------------------------------
7D3D4708          1          1         32          0 S
7C96A454          1          1        128          0 S	<-- Oracle knows you potentially can put 33 characters in :s so bumps up bind length to the next level 128.
7C92E34C          1          1         32          0 S
7C9691F4          1          1        128          0 S

SQL> select sql_text from v$sql where child_address in ('7C96A454','7C9691F4');	<-- different child cursors

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT :s s FROM DUAL	<-- exactly the same SQL as before
BEGIN :s := 'x'; END;	<-- exactly the same SQL as before


The reason I choose varchar2(32) vs. (33) is I know 32 is a threshold, according to queries on some databases:

SQL> select max_length, count(*) from v$sql_bind_metadata group by max_length;

MAX_LENGTH   COUNT(*)
---------- ----------
         0        150
         1         68
         7        669
        11          4
        16         15
        22       3279
        32       2031
       128         37
      2000         21
      3876          2
      4000         74
      4001          1
     32512         51

13 rows selected.

On another DB,

MAX_LENGTH   COUNT(*)
---------- ----------
         0          4
         7         31
        16          1
        22        140
        32        144
       128          1
      2000          1
      4000          2

8 rows selected.

And I know 32 is the shortest length for a varchar2:

SQL> var varcharshort varchar2(1)
SQL> print varcharshort

VARCHARSHORT
--------------------------------


SQL>

It has 32 dashes. If NLS_LANG environment variable (or Windows registry key) is set 
to the same as the NLS setting of the database, dashes under column name in SQL*Plus 
can be used to measure the char/varchar2 type bind variable bind length upper bound 
(without creating a new child cursor) up to SQL*Plus linesize.


SQL> var testnum number
SQL> print testnum

   TESTNUM
----------	<-- 10 dashes, can set numwidth to change it

SQL> var testchar char
SQL> print testchar

TESTCHAR
--------------------------------	<-- 32 dashes, correct!
SQL> var testclob clob
SQL> print testclob
SP2-0625: Error printing variable "testclob"
SQL> exec :testclob := 'This is a test'

PL/SQL procedure successfully completed.

SQL> print testclob

TESTCLOB
--------------------------------------------------------------------------------	<-- 80 dashes, can set longchunksize to change it
This is a test

SQL> select * from v$sql_bind_metadata where bind_name like 'TEST%';

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
-------- ---------- ---------- ---------- ---------- ------------------------------
7CB86F28          1          2         22          0 TESTNUM
7CB85A98          1         96         32          0 TESTCHAR
7CB84248          1        112       3876          0 TESTCLOB	<-- Can accomodate 3876 bytes




Once a large bind length is created, it won't come down for that bind variable unless you flush shared pool:

SQL> var longstring varchar2(4000)
SQL> print longstring

LONGSTRING
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	<-- 179 dashes


SQL> show linesize
linesize 179
SQL> var longstring varchar2(178)
SQL> print longstring

LONGSTRING
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


SQL> var longstring varchar2(133)
SQL> print longstring

LONGSTRING
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


SQL> var longstring varchar2(132)
SQL> print longstring

LONGSTRING
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


SQL> var longstring varchar2(131)
SQL> print longstring

LONGSTRING
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


SQL> var longstring varchar2(1)
SQL> print longstring

LONGSTRING
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


SQL> select * from v$sql_bind_metadata where bind_name = 'LONGSTRING';

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
-------- ---------- ---------- ---------- ---------- ------------------------------
7C971A40          1          1       4000          0 LONGSTRING

SQL> alter system flush shared_pool;

System altered.

SQL> select * from v$sql_bind_metadata where bind_name = 'LONGSTRING';

no rows selected

SQL> print longstring

LONGSTRING
--------------------------------	<-- 32


SQL>





SQL*Plus PRINT command does SELECT FROM DUAL, which populates shared pool

SQL> print testprint
SP2-0552: Bind variable "TESTPRINT" not declared.
SQL> var testprint char
SQL> select * from v$sql_bind_metadata where bind_name = 'TESTPRINT';

no rows selected

SQL> print testprint	<-- SQL trace shows SELECT :testprint testprint FROM DUAL. Interesting, how does PRINT know there's a :testprint created?

TESTPRINT
--------------------------------


SQL> select * from v$sql_bind_metadata where bind_name = 'TESTPRINT';

ADDRESS    POSITION   DATATYPE MAX_LENGTH  ARRAY_LEN BIND_NAME
-------- ---------- ---------- ---------- ---------- ------------------------------
7CC6D62C          1         96         32          0 TESTPRINT

SQL>