Surprises with RPAD

By | March 21, 2011

In putting together the test script for my last post, I noticed something I didn’t expect while using the RPAD function. My original script to populate my test table looked like:

insert into t1(c1, c2)
select rownum, rpad('test', 5000, '!')
from dual
connect by level <= 100;

However, when I thought about this I realized this should throw an error, since SQL can't return more than 4000 bytes per column. But this didn't error. So I checked what was happening:

select length(rp)
from (
    select rpad('test', 5000, '!') rp
    from dual
);
LENGTH(RP)
----------
      4000

So it looks like Oracle is silently only rpad'ing out to 4000 characters when using the rpad in SQL. So what happens if I manually concatenate an extra character?

select length(rp)
from (
    select rpad('test', 5000, '!')||'x' rp
    from dual
);
    from dual
         *
ERROR at line 4:
ORA-01489: result of string concatenation is too long

Strange. I would have thought the original case would have thrown an error as well.

Another strange behavior I noticed with rpad was in PL/SQL:

DECLARE
   c CLOB;
BEGIN
   c := rpad('test', 32767, '!');
END;
PL/SQL procedure successfully completed.

But if I add one more character (to make it more than the VARCHAR max of 32767):

DECLARE
   c CLOB;
BEGIN
   c := rpad('test', 32768, '!');
END;
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

The problem isn't with the length allowed in the clob:

SET SERVEROUTPUT ON

DECLARE
   c CLOB;
BEGIN
   c := rpad('test', 32767, '!')||rpad('test', 32767, '!');
   dbms_output.put_line('len: '||length(c));
END;
len: 65534

PL/SQL procedure successfully completed.

So it appears that the rpad function has limits that differ when using SQL or PL/SQL. And the more important difference is that in SQL, the result is just trimmed silently where in PL/SQL an error is raised. Nothing earth shattering, but good to keep in the back of your mind.

It should be noted that these tests were run on 10.2.0.4. If anyone has access to an 11g db I would be interested to see if anything has changed.

Update

After posting, I decided to read the documentation again for RPAD (I know, a novel idea!). Anyways, it does explain the PL/SQL behavior:

The string returned is of VARCHAR2 datatype if expr1 is a character datatype and a LOB if expr1 is a LOB datatype

So if I change the script slightly to convert the first input to a clob, it runs successfully:

DECLARE
   c CLOB;
BEGIN
   c := rpad(to_clob('test'), 32768, '!');
END;
PL/SQL procedure successfully completed.

That line from the documentation would also explain why in SQL I was only getting 4000 characters (since in SQL a VARCHAR2 has a max length of 4000). So I tried the same conversion to make sure it would work in SQL as well:

select length(rp)
from (
    select rpad(to_clob('test'), 5000, '!') rp
    from dual
);
LENGTH(RP)
----------
      5000

So that makes more sense, but I still don't understand why the original SQL query silently trims the field instead of raising an exception.

Leave a Reply

Your email address will not be published. Required fields are marked *

Turn on pictures to see the captcha *