I can get long to clob for insert but NOT merge.
Any solution ? Is it a limitation of merge and to_lob ???
CREATE TABLE source_tab(
object_id number,
object_name long,
object_type varchar2(100)
)
insert into source_tab select object_id,object_name,object_type from user_objects
create table dest_tab(object_id number,object_name clob,object_type varchar2(100))
select * from dest_tab
insert into dest_tab a
select object_id,to_lob(object_name), object_type from source_tab
delete from dest_tab
MERGE INTO dest_tab a
USING ( select object_id,to_lob(object_name) as object_name, object_type from source_tab ) b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET
a.object_name = b.object_name||'_'||a.object_name,
a.object_type = b.object_type||'_'||a.object_type
WHEN NOT MATCHED THEN
INSERT (object_id, object_name, object_type)
VALUES (b.object_id, b.object_name, b.object_type);