Oracle: convert nclob to nvarchar2 (part 2)

I needed to convert a column in a database from NCLOB to NVARCHAR2(2000) which is the max you can store in NVARCHAR2. The problem was that some columns in the table where larger then 2000 chars. To solve this i needed to use the oracle length function to select only rows who's lenth was less then 2000. In this example sql, the table that has the NCLOB is called THETABLE, and the column that needs to become NVARCHAR2 is called THECOLUMN.

<br />CREATE TABLE THETABLE_TEMP as select * from THETABLE;<br />alter table THETABLE drop COLUMN THECOLUMN;<br />alter table THETABLE add (THECOLUMN NVARCHAR2(2000));<br />UPDATE THETABLE p SET p.THECOLUMN = <br />(SELECT t.THECOLUMN FROM THETABLE_TEMP t <br />WHERE t.PRIMEKEY = p.PRIMEKEY <br />and dbms_lob.getlength(t.THECOLUMN) <= 2000) <br />

dbms_lob.getlength(column) is the key to success here