[#1011297] Invalid NUMBER to NUMERIC mapping (HV004 - fdw_invalid_data_type)

View Trackers | Bugs | Download .csv | Monitor

Date:
2013-01-22 23:23
Priority:
3
State:
Closed
Submitted by:
John Doe (thmykzamwb)
Assigned to:
Laurenz Albe (albe)
Category:
Runtime Error
Group:
v0.9.8
Resolution:
Rejected
 
Summary:
Invalid NUMBER to NUMERIC mapping (HV004 - fdw_invalid_data_type)

Detailed description
There seems to be an invalid NUMBER to NUMERIC mapping bug present in "oracle_fdw 0.9.8".

This query is not successfully executed:
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM ALL_TAB_COLUMNS WHERE OWNER NOT IN ('SYS','SYSTEM','WMSYS','EXFSYS','ORDSYS','XDB','MDSYS','ORACLE')

The following error is thrown:
HV004, fdw_invalid_data_type
DATA_LENGTH is the problematic part

Conclusions:
I suspect the problem might be connected to inconsistent numeric characters setting (NLS_NUMERIC_CHARACTERS), but I have jet to confirm that. Queries without number fields are successfully executed (e.g. record sets containing only text fields). Since regional settings that I am using are also used by various other applications I was trying to find a solution that does not require me to change them just for Oracle FDW. Nevertheless I would be very grateful for any recommendations on the minimal amount of change that might work (at least temporarily)

FDW setup procedure:
CREATE EXTENSION oracle_fdw;

CREATE FOREIGN DATA WRAPPER ora_utf8_fdw HANDLER oracle_fdw_handler VALIDATOR oracle_fdw_validator OPTIONS (nls_lang 'SLOVENIAN_SLOVENIA.EE8ISO8859P2');
--Tried without explicit "FOREIGN DATA WRAPPER" creation
--Tried various combinations EE8ISO8859P2, EE8MSWIN1250; AMERICAN_AMERICA, SLOVENIAN_SLOVENIA

CREATE SERVER oradb FOREIGN DATA WRAPPER ora_utf8_fdw OPTIONS (dbserver '//oraserver/oradb');
GRANT USAGE ON FOREIGN SERVER oradb TO postgres;
CREATE USER MAPPING FOR postgres SERVER oradb OPTIONS (user 'username', password 'password');
CREATE FOREIGN TABLE all_tab_columns (OWNER text not null, TABLE_NAME text not null, COLUMN_NAME text not null, DATA_TYPE text not null, DATA_LENGTH numeric not null) SERVER oradb OPTIONS (schema 'SYS', table 'ALL_TAB_COLUMNS');

Installation details:
Instance: Oracle 11g
NLS_LANG registry key value: SLOVENIAN_SLOVENIA.EE8MSWIN1250

PostgreSQL Client: JDBC (postgresql-9.2-1002.jdbc4.jar)
Oracle Client: JDBC (ojdbc6.jar - 11.2.0.2.0)

Oracle v$nls_parameters query results:
NLS_LANGUAGE: AMERICAN
NLS_TERRITORY: AMERICA
NLS_CHARACTERSET: EE8ISO8859P2
NLS_NUMERIC_CHARACTERS: .,

Oracle NLS_DATABASE_PARAMETERS query results:
NLS_LANGUAGE: SLOVENIAN
NLS_TERRITORY: SLOVENIA
NLS_CHARACTERSET: EE8ISO8859P2
NLS_NUMERIC_CHARACTERS: ,.

PostgreSQL "show" results:
SERVER_VERSION: 9.2.2
SERVER_ENCODING: UTF8
LC_COLLATE: Slovenian_Slovenia.1250
LC_CTYPE: Slovenian_Slovenia.1250

Followup

Message
Date: 2013-01-23 08:25
Sender: Laurenz Albe

Your problem is not connected with locale or encoding, but with the fact that the n-th column in an oracle_fdw foreign table corresponds to the n-th Oracle column, regardless of the name.
So your numeric column DATA_LENGTH actually corresponds to the VARCHAR2 column DATA_TYPE_MOD, and that is the reason for the error.

Try to define the foreign table like this:
CREATE FOREIGN TABLE all_tab_columns (
owner text NOT NULL,
table_name text NOT NULL,
column_name text NOT NULL,
data_type text NOT NULL,
data_type_mod varchar(3),
data_type_owner varchar(120),
data_length numeric NOT NULL
) SERVER oradb OPTIONS (schema 'SYS', table 'ALL_TAB_COLUMNS');

Then try
SELECT owner, table_name, column_name, data_type, data_length
FROM all_tab_columns;

oracle_fdw automatically sets NLS_NUMERIC_CHARACTERS to ".," and you cannot even override it.

Unless your PostgreSQL encoding is SQL_ASCII, I recommend that you do not set nls_lang on the foreign data wrapper.
oracle_fdw will automatically set the Oracle client character set to your PostgreSQL database encoding so that all characters are converted correctly.

Yours,
Laurenz Albe

Attached Files:

Changes:

Field Old Value Date By
ResolutionCould not Reproduce2013-02-22 15:35albe
status_idOpen2013-02-22 15:33albe
close_dateNone2013-02-22 15:33albe
assigned_tonone2013-02-22 15:33albe
ResolutionNone2013-02-22 15:33albe
GroupNone2013-01-23 08:28albe
Powered By FusionForge