Oracle SQL: Converting Long to VARCHAR2

Problem

It seems silly but there is not function for converting between these 2 formats. LONG is not widely used anymore, but there are times when we need to work with it, and if you need to manipulate it, you might want to convert this to a string.

Solution

There are a number of people offering advice, some of it seems a bit complicated.
This was the simplest method that I could find.

1. Convert to a clob
2. Convert the clob to string.

The caveat is that step 1 can only be done as part of an insert statement.
So you need to create a temporary table for holding your data.

create table xxgns_lt_tickets_interface_rpt
(fieldx clob);

insert into xxgns_lt_tickets_interface_rpt
(fieldx)
(select to_lob(question_text) from  xxgns_lt_tickets_interface);

select dbms_lob.substr( fieldx, 3900, 1 ) from xxgns_lt_tickets_interface_rpt;

With SQL (above) you can only select around 4000 bytes (I gave a margin for multibyte).
If putting the third statement aboue to PL/SQL, you can substr to 32k.

In my case I only wanted to report the first bit of the column anyway.

Acknowledgment

http://stackoverflow.com/questions/12010902/oracle-unable-to-convert-from-long-datatype-to-clob-datatype-incosistent-dataty
http://psoug.org/reference/long2clob.html