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.