Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Problem copying data from postgres to Oracle via dg4odbc

IvorFeb 11 2014 — edited Feb 23 2014

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 23 2014
Added on Feb 11 2014
15 comments
7,752 views