ERROR: invalid byte sequence for encoding "UTF8": 0x00

[jschmidwgit]

Hello,

When i try to import a foreign table i get this encoding error:

ERROR:  invalid byte sequence for encoding "UTF8": 0x00
CONTEXT:  converting column "notiz_text" for foreign table scan of "v_notiz_t", row 1
SQL state: 22021

The table has a column notiz_text of type CLOB

My settings are like this:

postgres=# \dew+
                                                      List of foreign-data wrappers
    Name    |     Owner      |      Handler       |      Validator       | Access privileges | FDW options |         Description
------------+----------------+--------------------+----------------------+-------------------+-------------+-----------------------------
 oracle_fdw | intpepupg14023 | oracle_fdw_handler | oracle_fdw_validator |                   |             | Oracle foreign data wrapper
(1 row)

#  SHOW server_encoding;
 server_encoding
-----------------
 UTF8

# SELECT oracle_diag('oracleqa');
                                       oracle_diag
-----------------------------------------------------------------------------------------
 oracle_fdw 2.6.0, PostgreSQL 14.12, Oracle client 19.24.0.0.0, Oracle server 19.0.0.0.0
(1 row)

On the Oracle Server it looks like this:

SQL> SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

VALUE
--------------------------------------------------------------------------------
UTF8

SQL>

so everything is set to utf-8 but i still get this conversion error.

thanks,

Jan

[jschmidwgit]

Hello, ok i found a solution. I created a view where i filter out null characters.

CREATE OR REPLACE FORCE VIEW V_NOTIZ_T (NOTIZ_ID, AV_KEY, NOTIZ_TYP, NOTIZ_TEXT, LOCKING_VERSION) BEQUEATH DEFINER AS SELECT NOTIZ_ID, AV_KEY, NOTIZ_TYP, replace(DBMS_LOB.substr(notiz_text, 1000),chr(0),''), LOCKING_VERSION FROM notiz_t;

or is there another way, so that fdw is able to filter out null values?

[laurenz]

You could use the strip_zeros option, as documented:

ALTER FOREIGN TABLE v_notiz_t ALTER notiz_text OPTIONS (ADD strip_zeros 'on');

That should automatically strip all zero bytes from the column.

[jschmidwgit]

I did not see it before. Now it works perfectly.

Thanks for your quick reply.

Jan