Monthly Archives: March 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.

Today I was asked by a colleague how to solve this problem, so I thought I would share. While the LONG datatype should no longer be used going forward, on occassion you do still run into it from time to time. My colleague was given the task of creating an archive process for a table, copying all data older than 6 months into an archive table and deleting those rows from the main table.

To illustrate, first we create some example tables:

create table t1(c1 number, c2 long);

create table t2(c1 number, c2 long, c3 date);

And populate t1 with some data:

DECLARE
    v LONG := rpad('test', 10000, '!');
BEGIN
    FOR i IN 1..5000
    LOOP
        insert into t1(c1, c2)
        values (i, v);
    END LOOP;
END;

This is equivalent to what my colleague was trying to do:

insert into t2
select t1.*, sysdate
from t1;

ORA-00997: illegal use of LONG datatype

So how do you do a simple insert using a LONG without giving up too much in performance? One way is to take advantage of PL/SQL and bulk collect / forall insert:

DECLARE
    TYPE tab_t
    IS
        TABLE OF T2%ROWTYPE;

    tab TAB_T;
BEGIN
    select t1.*, sysdate
    BULK COLLECT INTO tab
    from t1;

    FORALL i IN INDICES OF tab
    insert into t2
    values tab(i);
END;

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.10

select count(*)
from t2;

  COUNT(*)
----------
      5000

So 2 seconds for 5000 rows isn’t great, but is fast enough for a process that will run once a week for ~5-10K rows.