Hello,
I have a Oracle Database 12.1 with a database link to a mySQL Database with the mysql-connector-odbc-5.3.4-win64. On a Microsoft Windows Server 2012 R2 machine.
In Oracle I have created a database link moreweb.
A PL-SQL Procedure synchronizes data between the table morewebpag and morewebpag@moreweb.
mySQL:
CREATE TABLE `WEBCMSPAG` (
`CMSID` DECIMAL(10,0) NOT NULL,
`PAGEID` DECIMAL(10,0) NOT NULL,
`PAGETEXT` MEDIUMTEXT NULL COLLATE 'utf8_unicode_ci',
`ORIGIN` VARCHAR(1) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`CMSID`, `PAGEID`)
)
COLLATE='utf8_unicode_ci'
Oracle:
create table WEBCMSPAG(
CMSID number(10) not null,
PAGEID number(10) not null,
ORIGIN varchar2(1),
PAGETEXT clob,
CONSTRAINT webcmspagtxv_prim PRIMARY KEY(cmsid,pageid)
)
;
DESC webcmspag@moreweb
Name Null? Typ
----------------------------------------------------- -------- ---------------
--------------------
CMSID NOT NULL NUMBER(10)
PAGEID NOT NULL NUMBER(10)
ORIGIN VARCHAR2(1)
PAGETEXT LONG
Now I have to syncronize the PAGETEXT from WEBCMSPAG to WEBCMSPAG@moreweb or from WEBCMSPAG@moreweb to WEBCMSPAG, depending on whether the origin is M or C. The max length of the PAGETEXT in mySQL is 53281 and I get only the first 32760 Character, because the max length for long columns in PL/SQL is 32760.
1) is there a possibility to get the MEDIUMTEXT column as CLOB then as LONG?
2) If not, how can I save the whole content of a LONG column in a CLOB column in a PL/SQL procedure?
Thanks a lot for help.
Bianca