This is not a full answer because it only works if your clob is less than 4000 chars. The clob version (with plain bar instead of to_char(bar) in the query) fails with ORA-00932: inconsistent datatypes: expected - got CLOB - perhaps someone else can explain why exactly?
testbed:
drop table foo;
create table foo(id integer, bar clob);
insert into foo(id, bar) values (1, 'Hello'||chr(10)||'there');
insert into foo(id, bar) values (2, 'Hello'||chr(10)||'there'||chr(10)||'again');
query:
with w(id, line#, line, rest) as (
select id, 1, regexp_substr(to_char(bar), '^.*?$', 1, 1, 'm'),
substr(to_char(bar), regexp_instr(bar, '$', 1, 1, 1, 'm')+1)
from foo
union all
select id, line#+1, regexp_substr(rest, '^.*?$', 1, 1, 'm'),
substr(rest, regexp_instr(rest, '$', 1, 1, 1, 'm')+1)
from w
where rest is not null ) cycle id, line# set is_cycle to '1' default '0'
select id, line#, line from w order by id, line#;
/*
ID LINE# LINE
---------------------- ---------------------- ----------------------------------------
1 1 Hello
1 2 there
2 1 Hello
2 2 there
2 3 again
*/
The solution of the problem is that table MCOUNTRY did not have the primary key. It was only configured with unique constraint that was called is the same way that primary keys. Even when the unique column was used in query Oracle could not provide expected result.
Something obvious but still lesson to learn, unique constraint is not the same as primary key where it comes to joins.
Have a nice day.
Best Answer
Remote database:
Local database:
The problem is, CHAR types are limited to 4000 bytes, so:
A possible workaround:
The above is from a 11.2 database.
Starting with 12.2, the limitation of selecting a LOB through a dblink was removed, no workaround needed.