Oracle Tips and Tricks — David Fitzjarrell

April 7, 2017

You Bet Your ASCII

Filed under: General — dfitzjarrell @ 10:58

"Why, did you know that if a beaver two feet long with a tail a foot and a half long can build a dam twelve 
feet high and six feet wide in two days, all you would need to build Boulder Dam is a beaver sixty-eight
feet long with a fifty-one-foot tail?"
"Where would you find a beaver that big?" grumbled the Humbug as his pencil point snapped.
"I'm sure I don't know," he replied, "but if you did, you'd certainly know what to do with him."
-- Norton Juster, The Phantom Tollbooth 

International character sets, such as AL32UTF8, can solve a host of problems when non-ASCII characters need to be stored in the database. This, unfortunately, can create problems when having to convert those characters to ASCII-compatible text using Oracle’s built-in function ASCIISTR(). Let’s look at an example and see what might occur.

Two databases exist, one 11.2.0.4, the other 12.1.0.2, and both use the AL32UTF8 character set. Let’s create a table in both databases and load the CLOB column with non-ASCII characters (characters that will print on the screen but will be processed by the ASCIISTR() function):


SQL> create table yumplerzle(
  2  smarg   number,
  3  weebogaz	     clob);

Table created.

SQL> 
SQL> begin
  2  	     for i in 1..1000 loop
  3  		     insert into yumplerzle
  4  		     values(i, rpad(i, 8000, chr(247)));
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 

Query the table absent the ASCIISTR() function to see what character we’ve chosen:


...
SUBSTR(WEEBOGAZ,1,4000)
--------------------------------------------------------------------------------
991ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
992ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
993ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
994ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
995ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
996ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
997ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
998ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
999ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷
1000ÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷ÎáÎáÎ÷

1000 rows selected.

Interesting data, to be sure. Now let’s try to use the ASCIISTR() function on the output:


SQL> select asciistr(substr(weebogaz,1,4000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,4000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 

Unfortunately the character ‘conversion’ replaces the non-ASCII characters with their HEX codes and that can expand the line length considerably. Since this is 11.2.0.4 the length limit for VARCHAR2 columns is 4000 (characters or bytes depending on how your database or table column is configured). Given that restriction it’s impossible to use ASCIISTR() on any longer line than 1000 characters/bytes as shown below:


SQL> select asciistr(substr(weebogaz,1,32767)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,32767)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,16000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,16000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,4000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,4000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,3000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,3000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,2000)) from yumplerzle
  2  /
select asciistr(substr(weebogaz,1,2000)) from yumplerzle
                *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set 
conversion. 


SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,1000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,1000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,1000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...

It’s apparent how the line length has expanded based on the output from ASCIISTR(). If you’re using any release older than 12.1 you’re stuck with this restriction. Thankfully Oracle 12.1 and later versions offer the possibility of extended string length for text fields, configured using the max_string_size parameter. Setting this to EXTENDED and running the utl32k.sql script in $ORACLE_HOME/rdbms/admin (on UNIX and Linux systems, %ORACLE_HOME%\rdbms\admin on Windows) can fix this error. This requires a shutdown of the database and starting in UPGRADE mode. The exact steps are shown below:


SQL>
SQL> alter system set max_string_size = EXTENDED scope=spfile;
SQL> shutdown immediate
...
SQL> startup upgrade
...
SQL> @?/rdbms/admin/utl32k.sql
...
SQL> shutdown immediate
...
SQL> startup
...
SQL>

The script makes necessary changes to the data dictionary that allow Oracle to utilize this expanded string length and, in turn, indirectly modify functions like ASCIISTR() so their string buffer lengths are increased. Moving over to the database running under 12.1.0.2 that has had this modification completed the error experienced in 11.2.0.4 is gone:


SQL> select asciistr(substr(weebogaz,1,32767)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,32767))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,32767))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,16000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,16000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,16000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,4000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,4000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,4000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,3000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,3000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,3000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,2000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,2000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,2000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...
SQL> 
SQL> pause

SQL> 
SQL> select asciistr(substr(weebogaz,1,1000)) from yumplerzle
  2  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,1000))                                               
--------------------------------------------------------------------------------
1\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF

ASCIISTR(SUBSTR(WEEBOGAZ,1,1000))                                               
--------------------------------------------------------------------------------
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
D\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFF
...

No “buffer too small” errors were thrown with the extended string length configured in 12.1.0.2, even passing a substring length of 32767. Using even longer sub-strings, and even eliminating the substr() call entirely, also seems to pose no problems:


SQL> 
SQL> select asciistr(substr(weebogaz,1,64000)) from yumplerzle
  2  where rownum < 2
  3  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,64000))                                              
--------------------------------------------------------------------------------
305\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
...
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD                                     
                                                                                

SQL> 
SQL> select asciistr(substr(weebogaz,1,128000)) from yumplerzle
  2  where rownum < 2
  3  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,128000))                                             
--------------------------------------------------------------------------------
305\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
...
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD                                     
                                                                                

SQL> 
SQL> select asciistr(substr(weebogaz,1,256000)) from yumplerzle
  2  where rownum < 2
  3  /

ASCIISTR(SUBSTR(WEEBOGAZ,1,256000))                                             
--------------------------------------------------------------------------------
305\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
...
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD                                     
                                                                                

SQL> 
SQL> select asciistr(weebogaz) from yumplerzle
  2  where rownum < 2
  3  /

ASCIISTR(WEEBOGAZ)                                                              
--------------------------------------------------------------------------------
305\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
...
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\F
FFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD\FFFD                                     
                                                                                

SQL>  

When using character sets like WE8MSWIN1252 and US7ASCII these issues aren’t present as the data is converted to something ASCII can handle during the insert; only extended character sets seem to produce this error on conversion, something to remember since, if your database is NOT using a UTF8/UTF16 character set such problems won’t occur. There will be no need to increase the max_string_size when ASCII-centric character sets are used unless, of course, you want the extended length to store longer pieces of text.

Notice that the character set of the database was NOT changed during this process, only the maximum declarable length of a VARCHAR2/NVARCHAR2 column was affected. Also remember that this should be done in a test environment first, to ensure that such a change doesn’t adversely affect existing applications and code. I have not experienced any issues of that sort but mine isn’t the only database in the world and there could be exceptions in other environments. Only after you are reasonably certain this change doesn’t break anything can you move this into a production environment.

Fill ‘er up.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: