Oracle Tips and Tricks — David Fitzjarrell

April 28, 2017

Adjusting Things

Filed under: General — dfitzjarrell @ 10:53 

"The only thing you can do easily is be wrong, and that's hardly worth the effort." 
-- Norton Juster, The Phantom Tollbooth

Query tuning is both an art and a science and, because of this, usually occurs on a case-by-case basis. There may be occasions, though, where a series of queries, similar in structure and differing in predicate values, need to be tuned. Rather than go through each and every query, setting up a ‘standard’ SQL Profile (where force_match=FALSE) and enabling it, it may be easier to alter the setting for optimizer_index_cost_adj (presuming indexes are in use) so the index scans/index access paths are more ‘favorable’ than a table scan. Yet on the other hand it might be worth the effort to create the profile with force_match=TRUE, to cover all queries with the same SQL text outside of the literal values supplied. Let’s look at examples of why these might be good plans of attack (and a case where it wouldn’t be).

The optimizer, in its infinite wisdom and using current statistics, computes the cost of access for every table and associated index touched by the problem query. Sometimes the index cost is ever so slightly greater than that for a table scan and, as a result, the index path gets scrapped in favor of the table scan. In such cases nudging the optimizer in the ‘proper’ direction is as simple as changing the value on the optimizer_index_cost_adj parameter, which defaults to 100. The value you need should be chosen carefully, hopefully so that the queries you want affected will be affected and most others won’t. In our example let’s look at a very small portion of the level 2 10053 trace; the names were changed to protect the ‘innocent’:


 ****** Costing Index PLORGENFLOTZ_PK
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
  Access Path: index (RangeScan)
    Index: PLORGENFLOTZ_PK
    resc_io: 3.000000  resc_cpu: 342602
    ix_sel: 0.954069  ix_sel_with_filters: 0.954069
    Cost: 3.014879  Resp: 3.014879  Degree: 1
...
  Best:: AccessPath: TableScan
         Cost: 2.006465  Degree: 1  Resp: 2.006465  Card: 228.000000  Bytes: 0.000000

Notice the cost of the index access is just slightly higher than the cost of a full table scan so the optimizer passes up that option and chooses the table scan. This is where optimizer_index_cost_adj can change things. If, for example, we set optimizer_index_cost_adj to 50 the cost of the index access will go down:


 ****** Costing Index PLORGENFLOTZ_PK
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_SCAN
  SPD: Return code in qosdDSDirSetup: NODIR, estType = INDEX_FILTER
  Access Path: index (IndexOnly)
    Index: PLORGENFLOTZ_PK
    resc_io: 1.000000  resc_cpu: 63786
    ix_sel: 0.954069  ix_sel_with_filters: 0.954069
    Cost: 1.001385  Resp: 1.001385  Degree: 0
    SORT ressource         Sort statistics
      Sort width:        5989 Area size:     1048576 Max Area size:  1046896640
      Degree:               1
      Blocks to Sort: 1 Row size:     21 Total Rows:            243
      Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
      Total IO sort cost: 0.000000      Total CPU sort cost: 23112595
      Total Temp space used: 0
...
  Best:: AccessPath: IndexRange
  Index: PLORGENFLOTZ_PK
         Cost: 1.507553  Degree: 1  Resp: 1.507553  Card: 34.346487  Bytes: 0.000000

The calculated cost of using this index has been cut in half (which should be expected when setting optimizer_index_cost_adj to 50) so now the optimizer elects to take the index range scan as the best possible path. Notice that the optimizer_index_cost_adj isn’t applied until the actual cost has been calculated; the total cost is adjusted by the percentage provided in the optimizer_index_cost_adj setting as the final step. Looking at the final execution plan we see the following steps:


...
| 44  |                   TABLE ACCESS BY INDEX ROWID BATCHED         | PLORGENFLOTZ_TBL              |    34 |  1326 |     2 |  00:00:01 |      |      |     |        |       |
| 45  |                    INDEX RANGE SCAN                           | PLORGENFLOTZ_PK               |   243 |       |     1 |  00:00:01 |      |      |     |        |       |
...

which replaced this step in the plan where optimizer_index_cost_adj was unmodified:


...
| 111 |                        TABLE ACCESS FULL                           | PLORGENFLOTZ_TBL            |    32 |  1248 |     2 |  00:00:01 |      |      |           |       |
...

Other path steps were changed in addition to those listed here and the overall execution plan was shortened, as evidenced by the step numbers from the included plan excerpts.

Careful planning and testing needs to be done before settling on a value for optimizer_index_cost_adj as it will affect all index access calculations and could change acceptable plans using table scans to less-than-desirable plans forcing index access. The value of 50 used here was chosen after several runs using smaller and smaller settings until the desired plans were obtained. Being aggressive isn’t necessarily best when setting optimizer_index_cost_adj as extremely small settings, such as 20 or lower, may make some queries run very fast and make some others very slow (because index access isn’t always the best path to choose). Never make such changes on a production system without first investigating the effects in your test environment. The user community does not like unpleasant surprises.

Yet another way to tune a set of queries that differ only in literal values is by using a SQL Profile with force_match set to TRUE. This works by replacing the literal values with system-generated bind variables before the signature is generated; any similar query with different literal values will be associated with the same profile as the original query and thus will use the same execution plan. This presumes that ONLY literal values are in the source query statement; any additional bind variables present will generate a new signature that won’t match the signature associated with the profile and the known ‘good’ plan won’t be selected.

Let’s look at an example of that in action:


SQL>
SQL>--
SQL>-- Create table
SQL>--
SQL>create table plan_test(
  2  id      number,
  3  class   number,
  4  data    varchar2(45),
  5  cr_dt   date);

Table created.

SQL>
SQL>--
SQL>-- Load table
SQL>--
SQL>begin
  2  	     for i in 1..500000 loop
  3  		     insert into plan_test
  4  		     values(i, mod(i,337)+1, 'Value '||i, sysdate+mod(i,337));
  5  	     end loop;
  6
  7  	     for i in 500001..1000000 loop
  8  		     if mod(i,2)=0 then
  9  			     insert into plan_test
 10  			     values(3999, 3999, 'Value '||i, sysdate+mod(i,37));
 11  		     else
 12  			     insert into plan_test
 13  			     values(7734, 1234, 'Value '||i, sysdate+mod(i,37));
 14  		     end if;
 15  	     end loop;
 16
 17  	     commit;
 18  end;
 19  /

PL/SQL procedure successfully completed.

SQL>
SQL>--
SQL>-- Add an index
SQL>--
SQL>create index plan_test_idx on plan_test(class);

Index created.

SQL>
SQL>--
SQL>-- Compute stats and histograms
SQL>--
SQL>exec dbms_stats.gather_table_stats(user, 'PLAN_TEST', method_opt=>'for all columns size skewonly', cascade=>true);

PL/SQL procedure successfully completed.

SQL>
SQL>--
SQL>-- Run a query to get an index-access plan
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 1;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
       337          1 Value 337                                     28-APR-17
       674          1 Value 674                                     28-APR-17
      1011          1 Value 1011                                    28-APR-17
...
    483932          1 Value 483932                                  28-APR-17
    487302          1 Value 487302                                  28-APR-17
    477529          1 Value 477529                                  28-APR-17
    480899          1 Value 480899                                  28-APR-17
    484269          1 Value 484269                                  28-APR-17
    487639          1 Value 487639                                  28-APR-17

1483 rows selected.

SQL>
SQL>--
SQL>-- Display the plan
SQL>--
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  19vnyya8kzzsw, child number 0
-------------------------------------
select * from plan_test where class = 1

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLASS"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]


32 rows selected.

SQL>
SQL>--
SQL>-- Run a query to get an full scan plan
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 3999;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
      3999       3999 Value 500682                                  02-JUN-17
      3999       3999 Value 500684                                  28-APR-17
      3999       3999 Value 500686                                  30-APR-17
...
      3999       3999 Value 997392                                  18-MAY-17
      3999       3999 Value 997394                                  20-MAY-17
      3999       3999 Value 997396                                  22-MAY-17

250000 rows selected.

SQL>
SQL>--
SQL>-- Display the plan
SQL>--
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g41z4n4rnvbqc, child number 0
-------------------------------------
select * from plan_test where class = 3999

Plan hash value: 534695957

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |  1304 (100)|          |
|*  1 |  TABLE ACCESS FULL| PLAN_TEST |   244K|  6916K|  1304   (1)| 00:00:01 |
-------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("CLASS"=3999)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22],
       "PLAN_TEST"."DATA"[VARCHAR2,45], "PLAN_TEST"."CR_DT"[DATE,7]


29 rows selected.

SQL>

SQL>--
SQL>-- Create script to create profile
SQL>--
SQL>-- Profile uses force_match=TRUE
SQL>--
SQL>@coe_xfr_sql_profile 19vnyya8kzzsw 2494389488
SQL>--
SQL>-- Create the profile
SQL>--
SQL>@coe_xfr_sql_profile_19vnyya8kzzsw_2494389488
SQL>--
SQL>-- Test the profile
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 1;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
       337          1 Value 337                                     28-APR-17
       674          1 Value 674                                     28-APR-17
      1011          1 Value 1011                                    28-APR-17
...
    483932          1 Value 483932                                  28-APR-17
    487302          1 Value 487302                                  28-APR-17
    477529          1 Value 477529                                  28-APR-17
    480899          1 Value 480899                                  28-APR-17
    484269          1 Value 484269                                  28-APR-17
    487639          1 Value 487639                                  28-APR-17

1483 rows selected.

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  19vnyya8kzzsw, child number 0
-------------------------------------
select * from plan_test where class = 1

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLASS"=1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>
SQL>select *
  2  from plan_test
  3  where class = 107;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
       443        107 Value 443                                     12-AUG-17
       780        107 Value 780                                     12-AUG-17
      1117        107 Value 1117                                    12-AUG-17
...
    487071        107 Value 487071                                  12-AUG-17
    477298        107 Value 477298                                  12-AUG-17
    480668        107 Value 480668                                  12-AUG-17
    484038        107 Value 484038                                  12-AUG-17
    487408        107 Value 487408                                  12-AUG-17
    477635        107 Value 477635                                  12-AUG-17
    481005        107 Value 481005                                  12-AUG-17

1484 rows selected.

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  93zcxckzy9g3f, child number 0
-------------------------------------
select * from plan_test where class = 107

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLASS"=107)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>
SQL>select *
  2  from plan_test
  3  where class = 391;

no rows selected

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7n1ab3tyk1f33, child number 0
-------------------------------------
select * from plan_test where class = 391

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLASS"=391)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>
SQL>select *
  2  from plan_test
  3  where class = 1044;

no rows selected

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  52r213wp9sr9a, child number 0
-------------------------------------
select * from plan_test where class = 1044

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |  1136 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |  2236 | 64844 |  1136   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |  2236 |       |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLASS"=1044)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>

All of the queries shown above return about the same number of rows, presuming they return rows, and all used the created profile, which is good. What isn’t so good is the next query, returning 250000 rows, also uses the profile:


SQL>
SQL>--
SQL>-- This one probably shouldn't use the profile but it does
SQL>--
SQL>-- Result of force_match=TRUE
SQL>--
SQL>select *
  2  from plan_test
  3  where class = 3999;

        ID      CLASS DATA                                          CR_DT
---------- ---------- --------------------------------------------- ---------
      3999       3999 Value 500682                                  02-JUN-17
      3999       3999 Value 500684                                  28-APR-17
      3999       3999 Value 997388                                  14-MAY-17
...
      3999       3999 Value 997392                                  18-MAY-17
      3999       3999 Value 997394                                  20-MAY-17
      3999       3999 Value 997396                                  22-MAY-17

250000 rows selected.

SQL>
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g41z4n4rnvbqc, child number 0
-------------------------------------
select * from plan_test where class = 3999

Plan hash value: 2494389488

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |       |       |   123K(100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| PLAN_TEST     |   244K|  6916K|   123K  (1)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN                  | PLAN_TEST_IDX |   244K|       |   510   (1)| 00:00:01 |
-----------------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / PLAN_TEST@SEL$1
   2 - SEL$1 / PLAN_TEST@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CLASS"=3999)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "PLAN_TEST"."ID"[NUMBER,22], "CLASS"[NUMBER,22], "PLAN_TEST"."DATA"[VARCHAR2,45],
       "PLAN_TEST"."CR_DT"[DATE,7]
   2 - "PLAN_TEST".ROWID[ROWID,10], "CLASS"[NUMBER,22]

Note
-----
   - SQL profile coe_19vnyya8kzzsw_2494389488 used for this statement


36 rows selected.

SQL>

Compare the cost of the index plan (123K) and the full table scan plan (1304) and you can see using the SQL Profile when returning a quarter of the table data is not the preferred path to take. Fixing the majority of the queries can ‘fix’ queries that don’t need fixing, and that’s the major issue with tuning with a broad brush.

It’s usually best to tune queries on an individual basis, but sometimes applications generate a set of queries that need attention. Judicious setting of optimizer_index_cost_adj could be the answer to such a tuning task, as could creating a SQL Profile with force_match=TRUE. Remember that making such changes at the database level can affect more than you had bargained for so test, test, test to verify minimal impact outside of the set of queries you are targeting. With respect to setting force_match=TRUE for a SQL Profile you may inadvertently set an inefficient plan using an index when a table scan would be preferable, as shown in the provided example.

Sometimes the “easy button” can be too easy to press.

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.

April 5, 2017

GrACE Period

Filed under: General — dfitzjarrell @ 12:27 

“You can swim all day in the Sea of Knowledge and not get wet.” 
― Norton Juster, The Phantom Tollbooth

The time is fast approaching where it will be three years since I was graced with the status of Oracle ACE, and I’ve enjoyed every minute of it. I can’t speak highly enough of the program and its steadily growing list of members. But don’t think for a minute that once someone becomes an Oracle ACE or Oracle ACE Director the work stops; nothing could be further from the truth.

Sharing my knowledge got me here and that hasn’t stopped or slowed down. I still blog, still write two articles each month for http://www.databasejournal.com and still contribute to the Oracle technical forums and I wouldn’t change a thing. I’ve said it before, sharing what you know may not seem important to you at the time, but others who you may not know or ever see will find it useful and will be grateful that you took the time and effort to pass it on. It’s not about the laurels and praise, it’s about contributing knowledge to the Oracle community in order to help others.

Allow me to quote from my original post on being an Oracle ACE:


Being an Oracle ACE is an honor, but it's also a responsibility. What got me
here was writing and participating in discussion groups, and that won't change.
Knowledge is to be shared, not hoarded. What you know does no 
one else any good if you don't share that knowledge and experience. If Da Vinci 
had kept his notes to himself, if Newton hadn't published his Laws of Motion, if 
Copernicus has kept quiet our world may have been quite different. It's because 
these people had the foresight to look deeper into our world and then tell us 
what they found that puts us where we are today. It's only right that we, as 
beneficiaries of the knowledge others before us shared, share our knowledge no 
matter how unimportant it may seem. Someone, somewhere, will find it useful and 
will be grateful that we did.

That still holds true almost three years later; I keep that thought in mind every time I post to a forum, write an article or create a blog post because I do those things to add to the knowledge base provided by members of the Oracle community. And even though others may post more often it doesn’t mean my contributions are diminished in any way, since I (hopefully) have a unique voice and viewpoint that adds to, not detracts from or duplicates, the contributions made by others. The world is a vast place and everyone’s voice should be heard. It’s incumbent upon you to make that so; raise your voice and be heard.

Oracle isn’t just a product line, it’s also a community and it takes more than one person to keep a community going. Others may be blogging and sharing but don’t let that stop you from doing the same. There is no rule that each contribution be unique; sometimes a different view on the same topic can turn the light of understanding on and provide insight and knowledge to clear the confusion. Your voice is special; don’t deprive others of your contributions simply because you can’t think of a unique topic. You may provide understanding by approaching the topic from a different direction. Sometimes a change of perspective is all it takes.

Again from that previous blog post:


I love sharing what I know; I've been doing it for over 16 years now, in various 
forums, some that are no longer as popular as they once were.  I never realized 
how far my commentary reached until I became an Oracle ACE; I have received 
congratulations and comments that I never expected, mostly of the 'it's about 
time' sentiment.  Simply because you don't see the gratitude of others who 
benefit from your knowledge doesn't mean that gratitude doesn't exist.  I see 
now that it does, and I am humbled by it.

It’s still great to be an Oracle ACE, and to me it always will be. But it is good to remember that being an ACE isn’t the destination, it’s just the start of the journey.

Head ’em up, move ’em out.

Create a free website or blog at WordPress.com.