Hi,
I've created a database link in my Oracle Database 11g Express Edition connecting to our SQL Server 2005 Enterprise Database. Now, I'm having issues on migrating some fields. Mostly Text field in SQL Server to CLOB in Oracle. I've created a PL/SQL Procedure to do this but somehow these columns don't get all the value.
For example:
In my sql server, I have a table SQLTBL with a field REMARKS of TEXT data type. It has the following value.
This is line 1.
This is line 2.
This is line 3.
Now I created a table ORCLTBL in oracle with field REMARKS of CLOB data type.
In my procedure I do something like.
begin
for i in ( select "remarks" rem from "dbo"."sqltbl"@sqldb )
loop
insert into orcltbl ( remarks ) values (i.rem);
end loop;
commit;
end;
/
When I select the data from oracle it will only show "This is line 1" and disregard the values after the new line character.
Is there a way to make this work?
Thanks,
Allen