CLOB via DB Link using CREATE TABLE AS SELECT...
I need to access a CLOB column from Database 1 and mirror it in Database 2. Unfortunately there is the limitation that LOBs are not transferable across DB links.However, it appears that I would be able to copy the CLOB file over if I use
CREATE TABLE new_table AS SELECT * FROM old_table@db1_link
I'd be grateful if somebody could explain briefly how it is possible for Oracle to be able to communicate a CLOB in this way, but not as a straight SELECT via the DB link. Even if I use the TO_CHAR function, it won't let me pull the CLOB over as text.
It's particularly annoying that I cannot use a
CREATE VIEW new_view AS SELECT * FROM old_table@db1_link
so that I didn't need to keep refreshing (dropping/recreating) the table.