LONG is still not gone

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.