SQL & PL/SQL

I recently had to convert a bunch of text stored in Long fields to a new VarChar2(2000) field in the same table.

As you know if you've ever tried, you can't use a basic SQL the update statement will not do this, e.g. update table1 set field2 = field1 (where field2 is VarChar(2000) and field1 is Long) fails as does update table1 set field2 = substr(field1, 1, 2000).

Interestingly PL/SQL's implementation of SubStr does work with Long fields. I was able to put together the following anonymous PL./SQL block to accomplish the task (msgtext is varchar(2000), msgtext2 is Long):

DECLARE
CURSOR message_cur
IS
SELECT ROWID, msgtext, msgtext2
FROM tlrcmmessages
FOR UPDATE;
message_rec message_cur%ROWTYPE;
BEGIN

OPEN message_cur;

LOOP
FETCH message_cur
INTO message_rec;

EXIT WHEN message_cur%NOTFOUND;

UPDATE tlrcmmessages
SET msgtext = SUBSTR(message_rec.msgtext2, 1, 2000)
WHERE CURRENT OF message_cur;

END LOOP;

CLOSE message_cur;
END;

I hopes this helps others out there in similar circumstances.

Good luck,
Rob Bratton, Bratton Enterprises