Hi All,
I am trying to migrate data from a postgres moodle database to an oracle one. I have set up dg4odbc and it is mainly working, except when I try to insert select for some tables only.
Eg.
SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 12 13:28:37 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> insert into M_ASSIGNFEEDBACK_COMMENTS(
assignment
,commenttext
,commentformat
,id
,grade
)
select
"assignment"
,"commenttext"
,"commentformat"
,"id"
,"grade"
from "mdl_assignfeedback_comments"@"to_moodle";
2 3 4 5 6 7 8 9 10 11 12 13 14 insert into M_ASSIGNFEEDBACK_COMMENTS(
*
ERROR at line 1:
ORA-28502: internal communication error on heterogeneous database link
ORA-02063: preceding line from TO_MOODLE
But the select part of the statement works.
SQL> select "assignment" ,"commenttext" ,"commentformat" ,"id" ,"grade" from "mdl_assignfeedback_comments"@"to_moodle";
2 3 4 5 6 7 assignment
----------
commenttext
--------------------------------------------------------------------------------
commentformat id grade
------------- ---------- ----------
4
Excellent work
1 1 1 5 1 2 2 10
Excellent work
1 3 3 11 1 4 4
SQL>
And it works if I leave out the commenttext field which is of postgres type text
1 insert into M_ASSIGNFEEDBACK_COMMENTS(
2 assignment
3 ,commentformat
4 ,id
5 ,grade
6 )
7 select
8 "assignment"
9 ,"commentformat"
10 ,"id"
11 ,"grade"
12* from "mdl_assignfeedback_comments"@"to_moodle"
SQL> /
4 rows created.
Other text fields in other tables seem to work fine however.
My database and the postgres database are Unicode character set
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
My initmdl_dev23.ora config
HS_FDS_CONNECT_INFO = MDL_DEV23
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME=/tmp/ora_hs_trace.log
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_LANGUAGE=american_america.we8mswin1252 # this had to be set due to some bug with the postgres connection?
HS_NLS_NCHAR=UCS2
HS_FDS_REMOTE_DB_CHARSET=AL32UTF8
HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL
#
# ODBC specific environment variables #
set ODBCINI=/etc/odbc.ini
Table Desc Oracle
SQL> desc M_ASSIGNFEEDBACK_COMMENTS
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(10)
ASSIGNMENT NOT NULL NUMBER(10)
GRADE NOT NULL NUMBER(10)
COMMENTTEXT CLOB
COMMENTFORMAT NOT NULL NUMBER(4)
Table Desc Postgres
mdl_dev23=> \d mdl_assignfeedback_comments
Table "public.mdl_assignfeedback_comments"
Column | Type | Modifiers
---------------+----------+----------------------------------------------------------------
----------
id | bigint | not null default nextval('mdl_assignfeedback_comments_id_seq'::
regclass)
assignment | bigint | not null default 0
grade | bigint | not null default 0
commenttext | text |
commentformat | smallint | not null default 0