Migration tool problems

Hello everybody,

I'm trying to migrate my Jforum from Oracle 9i to Postgres but I face two problems:
Fisrt, the date are truncated: 'date' data type in Oracle containing "13/05/2008 09:05:40" results in "2008-05-13" (data type 'date') after migration.
Second, the data of type BLOB and CLOB after migration are displayed as hexadecimal in the forum:
\xfeff004f00750069002c002000e00020006c0027006f0063006300610.....
Il matches exactly what i see in table but I dont know why it can't be displayed correctly.
My Oracle DB parameter for NLS_CHARACTERSET is WE8MSWIN1252, and i think i made a mistake in the configuration of my Postgres DB:
CREATE DATABASE own_jforum
WITH OWNER = own_jforum_role
ENCODING = 'UTF8'
TABLESPACE = jforum_data
LC_COLLATE = 'French_France.1252'
LC_CTYPE = 'French_France.1252'
CONNECTION LIMIT = -1;
GRANT ALL ON DATABASE own_jforum TO own_jforum_role WITH GRANT OPTION;
GRANT ALL ON DATABASE own_jforum TO public;

Can someone help me ?

Thanks !!

Second, the data of type BLOB and CLOB after migration are displayed as hexadecimal in the forum:
\xfeff004f00750069002c002000e00020006c0027006f0063006300610.....

To get the exact result,modify "bytea_output" parameter value from "hex" to "escape" in postgresql.conf file located under data directory and reload the PostgreSQL instance.

--Raghu

This message was edited 1 time. Last update was at 28 Jun 2012 03:56:14

Thanks for the reply !

I tried changing this parameter but it didn't do the trick.

In my case, it was not a configuration problem. The creation script included with Jforum2 for Oracle shows columns of type BLOB and CLOB, so the Migration Toolkit converts it into "bytea". But the creation scripts for a Postgres DB indicates TEXT type column.

The data were correctly migrate, so I manualy rewrote the data with the appropriate format using this function:
credit goes to Jérôme Saboie our DBA.

After convertion, the content was displayed correctly.

Edit: In fact the conversion from Oracle DATE type (type that include hour, minute, second) to Postgres DATE type is wrong as DATE type in Postgres contains only the year, month, day. The wise way to work around this issue is to migrate the schema only (with -shemaOnly option) then update the type DATE to TIMESTAMP, and finaly migrate the data with -dataOnly option.