More on Generating Strings

A while ago I blogged about how you could use the rpad function to generate a string of any length. Kristian suggested another way to do it, using the dbms_random.string function. In this post, I will write about what I have noticed when using the two approaches.

First, using rpad or dbms_random.string, you cannot generate a string greater than 4000 characters in size:

SQL> select length(dbms_random.string(null, 4000))
  2  from dual
  3  /

LENGTH(DBMS_RANDOM.STRING(NULL,4000)) 
------------------------------------- 
                                 4000                     

SQL> select length(dbms_random.string(null, 4001))
  2  from dual
  3  /

LENGTH(DBMS_RANDOM.STRING(NULL,4001))  
-------------------------------------  
                                 4000  

SQL> select length(rpad('*',4000, '*'))
  2  from dual
  3  /

LENGTH(RPAD('*',4000,'*'))  
--------------------------  
                      4000   

SQL> select length(rpad('*',4001, '*'))
  2  from dual
  3  /

LENGTH(RPAD('*',4001,'*'))    
--------------------------   
                      4000

Second, the concatenation of multiple strings that results in a concatenated string size of greater than 4000 characters will result in an error:

SQL> select
  2  length(dbms_random.string(null, 2000) ||
  3  dbms_random.string(null, 2000)) concatcol
  4  from dual
  5  /

 CONCATCOL   
----------  
      4000   

SQL> select
  2  length(dbms_random.string(null, 2000) ||
  3  dbms_random.string(null, 2001)) concatcol
  4  from dual
  5  /
from dual
     *
ERROR at line 4:
ORA-01489: result of string concatenation is too long 


SQL> select
  2  length(rpad('*',2000, '*') ||
  3  rpad('*',2000, '*')) concatcol
  4  from dual
  5  /

 CONCATCOL   
----------   
      4000  

SQL> select
  2  length(rpad('*',2000, '*') ||
  3  rpad('*',2001, '*')) concatcol
  4  from dual
  5  /
from dual
     *
ERROR at line 4:
ORA-01489: result of string concatenation is too long

So far, both rpad and dbms_random.string have similar behavior. However, using dbms_random.string to generate a string greater than 4000 characters and then insert it into a clob column in a table in the database will only insert a maximum of 4000 characters. This is not the case with rpad:

SQL> create table t (rundesc varchar2(10), x clob)
  2  /

Table created.

SQL> declare
  2  v1 clob := rpad('*',4001, '*');
  3  v2 clob := dbms_random.string(null,4001);
  4  begin
  5          insert into t values ('rpad', v1);
  6          insert into t values ('random', v2);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select rundesc, length(x) from t
  2  /

RUNDESC     LENGTH(X)   
---------- ----------  
rpad             4001   
random           4000

Related articles:

Tagged | Comments Closed | Trackbacks Closed