During 'Auto Optimize' a sql, SQL Optimizer prompts for real value for some bind variables. With three Chinese characters as the real value and click the 'Auto Optimize' button, the Alternatives window shows the error message: ' ORA-06502: PL/SQL: numeric or value error: character string buffer too small '.
In the database definition the column which takes in the Chinese character is declared as VARCHAR(150). When some English characters are input as the real value, it's no problem.
Product defect.
When SQL Optimizer tests run a SQL and if the option is set to use "Run on server", SQL Optimizer will generate a PL/SQL script to run the SQL. In the script, a PL/SQL variable will be used to bind the value to the bind variables and when there were three Chinese characters, SQL Optimizer declares VARCHAR2(3) for the column. Since the input value of the bind variable has 3 characters, SQL Optimizer declared the VARCHAR2 to have length 3 and caused the error when NLS_LENGTH_SEMANTICS is not set to CHAR in an unicode encoding database.
To workaround, choose NVARCHAR2 as the datatype in the Bind Variable window so Oracle will know the length defined is the character length.
