[#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
Resolution	Could not Reproduce	2013-02-22 15:35	albe
status_id	Open	2013-02-22 15:33	albe
close_date	None	2013-02-22 15:33	albe
assigned_to	none	2013-02-22 15:33	albe
Resolution	None	2013-02-22 15:33	albe
Group	None	2013-01-23 08:28	albe