Empty String in Oracle

[hbk3065]

Hi,
I have some problem when using oracle_fdw.
I installed oracle_fdw 1.4.0 few days ago.
In Oracle,

select stdy_desc,dump(stdy_desc,1016) from study where rownum<3

RESULT : 
stdy_desc    dump(stdy_desc,1016)
---------    -----------------------------------------------------------------
(null)       NULL
             Typ=1 Len=1 CharacterSet=AL32UTF8: 0

And setup oracle_fdw in postgresql 9.4 then error raised in postgresql.

select * from study_test limit 2;

RESULT : 
ERROR:  invalid byte sequence for encoding "UTF8": 0x00
CONTEXT:  converting column "stdy_desc" for foreign table scan of "study_test", row 2

Can you advice this problem?

Thanks,

Dong U.

[laurenz]

As DUMP shows you, the second row contains a zero byte in the stdy_desc column.

Zero bytes are not allowed in strings in PostgreSQL, and that's why oracle_fdw gives you that error message.

[volkmarbuehringer]

I patch the tables on the oracle side, after getting this error messages with the found
invalid utf codes:

update imptab set icol=translate(icol,chr(to_number('00','xx'))||chr(to_number('a4','xx')),' ' ) where instr( icol, chr(to_number('00','xx'))||chr(to_number('a4','xx'))) > 0;

[laurenz]

Does that solve your problem?

[hbk3065]

Yes, It worked very well.
Thank you very much!

But, I got another problem like #90 issue.

Here is a describe of the Oracle table :

STDY_MOD_DT     DATE
ACCS_DT     DATE
ANNO_RGS_DT     DATE
STDY_CMT        VARCHAR2(4000)

And postgresql table :

select column_name, data_type, character_maximum_length, is_nullable
from INFORMATION_SCHEMA.COLUMNS where table_name = 'study_test';

"stdy_mod_dt";"date";;"YES"
"accs_dt";"timestamp without time zone";;"YES"
"anno_rgs_dt";"timestamp without time zone";;"YES"
"stdy_cmt";"character varying";4000;"YES"

I execute select statement in postgresql then raise error.
ERROR: column "stdy_mod_dt" of foreign table "study_test" cannot be converted to or from Oracle data type

Oracle and PostgreSQL use same character set(UTF-8).

I tried change data type ("timestamp(0) without time zone" / "date" / "timestamp" ) in postgresql
but, it isn't work.

Is there any wrong in my progress?

[laurenz]

Maybe the columns are not in the same order in PostgreSQL and Oracle.
Column names are irrelevant in oracle_fdw, it is the column position that matters: The first Oracle column gets mapped to the first PostgreSQL foreign table column etc.

[hbk3065]

Oracle and PostgreSQL first columns have same name, data type and position.
Except date data type columns are working well.
And, not null date type column is working well too.

Is not null attribute related to the ERROR?

[laurenz]

I see.
To investigate this, I need the complete CREATE TABLE statement from Oracle and the CREATE FOREIGN TABLE statement from PostgreSQL.

[hbk3065]

I found problem in my progress.
I comment a part of my CREATE TABLE statement but I found it now.

Now, I fixed it and working well.
Thanks, laurenz. :)