Home

Moving columns of LONG datatype from one table to another

Wed, 2006.03.01 - 10:41 — müzso
The easiest method is to use the COPY command in SQLPLUS. Tom Kyte has a nice example on this.

The detailed syntax is like this:
SQL> copy from scott/tiger@server insert foo (the_whole_view, textlength, thetext) using select owner || '.' || view_name, text_length, text from all_views;

Note: this works only for LONG columns and not for LONG RAW columns.

Note2: in such problematic cases (like moving LONGs and so) I prefer to use the custom binary export/import feature of PL/SQL Developer. It's pretty robust and using the GUI makes selecting a set of tables and options a lot easier/faster. However sometimes it produces some strange errors ("unimplemented conversion requested") and in this case you've to revert back to the old, time-proven solutions like SQLPLUS and IMP/EXP.