Closed fnicollet closed 8 years ago
It works here (on 32-bit Windows and Linux) with the latest version from git, and I don't think that has changed since 1.4.0.
In Oracle:
SQL> CREATE TABLE datetest (
id NUMBER(5) CONSTRAINT datetest_pkey PRIMARY KEY,
d DATE
) SEGMENT CREATION IMMEDIATE;
SQL> INSERT INTO datetest VALUES (1, CURRENT_DATE);
SQL> INSERT INTO datetest VALUES (2, NULL);
SQL> INSERT INTO datetest VALUES (3, to_date('2100-01-01', 'YYYY-MM-DD'));
SQL> INSERT INTO datetest VALUES (4, to_date('100-01-01 BC', 'YYYY-MM-DD BC'));
SQL> COMMIT;
In PostgreSQL:
test=> CREATE FOREIGN TABLE datetest (
id integer OPTIONS (key 'true') NOT NULL,
d date
) SERVER oracle OPTIONS (table 'DATETEST');
test=> SELECT * FROM datetest;
id | d
----+---------------
1 | 2016-05-20
2 |
3 | 2100-01-01
4 | 0100-01-01 BC
(4 Zeilen)
NULLs should come across as NULLs.
Thanks for the quick answer!
While during your tests, I found out that it doesn't fail on records with NULL values, it failes on some records which even have a date value.
Here is a describe of the Oracle table:
CODE_ITIN NOT NULL NUMBER(11)
NOM VARCHAR2(250)
L_ESI_ITIN_TYPE_ESI VARCHAR2(100)
L_ESI_CTRL_SOUS_TYPE_ESI VARCHAR2(100)
NUMERO_DOSSIER VARCHAR2(20)
NOM_PROPRIETAIRE VARCHAR2(100)
L_ESI_CTRL_PROPRIETAIRE VARCHAR2(100)
COMMUNE_ESI VARCHAR2(100)
NUMERO_INSEE NUMBER(5)
CANTON_ESI VARCHAR2(100)
COMMUNAUTE_DE_COMMUNES VARCHAR2(100)
NUMERO_SIREN_COMCOM VARCHAR2(100)
PAYS_ESI VARCHAR2(100)
L_ESI_CTRL_FEDERATION VARCHAR2(100)
L_ESI_CTRL_ETAPE_INSCRIPTION VARCHAR2(100)
DATE_INSCRIPTION_PDESI DATE
DATE_PASSAGE_CDESI DATE
L_ESI_CTRL_NIVEAU_INSCR VARCHAR2(100)
NOTATION_ENVIRONNEMENT NUMBER(10,1)
L_ESI_CTRL_AVIS_ENV VARCHAR2(100)
COMMENTAIRE_ENVIRONNEMENT VARCHAR2(250)
DATE_AVIS_ENVIRONNEMENT DATE
DOCUMENT_ENVIRONNMENT VARCHAR2(250)
NOTATION_SPORT NUMBER(10,1)
L_ESI_CTRL_AVIS_SPORT VARCHAR2(100)
COMMENTAIRE_SPORT VARCHAR2(250)
DATE_AVIS_SPORT DATE
DOCUMENT_SPORT VARCHAR2(250)
NOTATION_TOURISME NUMBER(10,1)
L_ESI_CTRL_AVIS_TOURISME VARCHAR2(100)
COMMENTAIRE_TOURISME VARCHAR2(250)
DATE_CTRL_AVIS_TOURISME DATE
DOCUMENT_TOURISME VARCHAR2(250)
NOTATION_TRANSPORT NUMBER(10,1)
L_ESI_CTRL_AVIS_TRANSPORT VARCHAR2(100)
COMMENTAIRE_TRANSPORT VARCHAR2(250)
DATE_AVIS_TRANSPORT DATE
DOCUMENT_TRANSPORT VARCHAR2(250)
NOTATION_ROUTE NUMBER(10,1)
L_ESI_CTRL_AVIS_ROUTE VARCHAR2(100)
COMMENTAIRE_ROUTE VARCHAR2(250)
DATE_AVIS_ROUTE DATE
DOCUMENT_ROUTE VARCHAR2(250)
NOTATION_FINALE NUMBER(10,1)
LONGUEUR NUMBER(10,2)
L_ESI_ITIN_CTRL_VOCATION VARCHAR2(100)
L_ESI_ITIN_CTRL_VOCATION_SECND VARCHAR2(100)
L_ESI_ITIN_CTRL_LABEL VARCHAR2(100)
L_ESI_CTRL_MO VARCHAR2(100)
L_ESI_ITIN_CTRL_PRAT_HIV VARCHAR2(100)
L_ESI_CTRL_RAND_MOT VARCHAR2(100)
L_ESI_CTRL_HANDICAP_MOTEUR VARCHAR2(5)
L_ESI_CTRL_HANDICAP_AUDITIF VARCHAR2(5)
L_ESI_CTRL_HANDICAP_VISUEL VARCHAR2(5)
L_ESI_CTRL_HANDICAP_MENTAL VARCHAR2(5)
COMMUNE_DEPART VARCHAR2(250)
DESCRIPTION_DEPART VARCHAR2(250)
COMMUNE_ARRIVEE VARCHAR2(250)
DESCRIPTION_ARRIVEE VARCHAR2(250)
L_ESI_ITIN_CTRL_PARCOURS VARCHAR2(100)
L_ESI_CTRL_DIFFICULTE VARCHAR2(100)
TEMPS_PARCOURS VARCHAR2(50)
PROXIMITE_BASE_SPORT_NATURE VARCHAR2(250)
PROX_ETABLISSEMENT_SCOLAIRE VARCHAR2(250)
L_ESI_CTRL_ACCES_SCOLAIRE VARCHAR2(100)
EVENEMENT_SPORTIF VARCHAR2(250)
DENIVELEE NUMBER(5)
AUTORIS_DE_PASSAGE VARCHAR2(5)
L_ESI_CTRL_BALISAGE VARCHAR2(100)
L_ESI_ITIN_CTRL_BAL_TYPE VARCHAR2(100)
L_ESI_CTRL_BAL_GEST VARCHAR2(100)
L_ESI_CTRL_BAL_ETAT VARCHAR2(100)
SUBVENTION_DEP VARCHAR2(100)
PROMOTION VARCHAR2(100)
NOM_TOPOGUIDE VARCHAR2(100)
COMMENTAIRE VARCHAR2(250)
L_ESI_CTRL_NOM_PROJET VARCHAR2(100)
APB VARCHAR2(10)
RESERVE_NATURELLE VARCHAR2(10)
PNR VARCHAR2(10)
NATURA2000 VARCHAR2(10)
SAGE VARCHAR2(10)
ENS VARCHAR2(10)
ZNIEFF1 VARCHAR2(10)
ZNIEFF2 VARCHAR2(10)
ZICO VARCHAR2(10)
ZPPAUP VARCHAR2(10)
SITE_CLASSE VARCHAR2(10)
SITE_INSCRIT VARCHAR2(10)
ZPENS VARCHAR2(10)
L_ESI_CTRL_EXISTENCE_ESI VARCHAR2(50)
L_ESI_CTRL_NATURE_ESI VARCHAR2(50)
ACCES_ESI VARCHAR2(250)
L_ESI_CTRL_MOD_EXPLOIT VARCHAR2(50)
L_ESI_CTRL_PERIODE_OUV VARCHAR2(200)
L_ESI_CTRL_MODALITE_OUV VARCHAR2(50)
L_ESI_CTRL_ORGANISATION_ESI VARCHAR2(50)
L_ESI_CTRL_MODALITE_ACCES VARCHAR2(50)
L_ESI_CTRL_PERIODE_FREQ VARCHAR2(50)
L_ESI_CTRL_FREQ_TOUR VARCHAR2(50)
L_ESI_CTRL_IMP_TOUR VARCHAR2(50)
L_ESI_CTRL_FREQ_SPORTIVE VARCHAR2(50)
L_ESI_CTRL_IMP_SPORTIVE VARCHAR2(50)
L_ESI_CTRL_FREQ_SOCIALE VARCHAR2(50)
L_ESI_CTRL_IMP_SOCIALE VARCHAR2(50)
ATTRAIT_ESI VARCHAR2(250)
L_ESI_CTRL_ATTRACTIVITE VARCHAR2(50)
EQUIPEMENTS_ADAPTES VARCHAR2(10)
APPEL_SECOURS VARCHAR2(10)
ACCES_SECURISE VARCHAR2(10)
DESC_ACCES_SECOURS VARCHAR2(250)
DECRIPTIF_SECURITE VARCHAR2(250)
GESTIONNAIRE_ESI VARCHAR2(250)
L_ESI_CTRL_CONVENTION_ESI VARCHAR2(100)
PRESENCE_STATIONNEMENT VARCHAR2(10)
ETAT_AIRE_STATIONNEMENT VARCHAR2(250)
CONTRAINTES_REGLEMENTAIRES VARCHAR2(10)
DENIVELEE_PLUS NUMBER(5)
DENIVELEE_MOINS NUMBER(5)
LONGUEUR_PLUS NUMBER(10)
LONGUEUR_MOINS NUMBER(10)
TEMPS_PLUS VARCHAR2(20)
TEMPS_MOINS VARCHAR2(20)
USER_MAINTENANCE VARCHAR2(250)
DATE_MAINTENANCE DATE
STATIONNEMENT_CONVENTIONNE VARCHAR2(200)
LONGUEUR_ZERO NUMBER(10)
TEMPS_ZERO NUMBER(10)
DESCRIPTION_ITIN VARCHAR2(254)
DATE_DEBUT DATE
DATE_FIN DATE
ETAT NUMBER(10)
ID_EVENEMENT NUMBER(11)
GEOMETRIE MDSYS.SDO_GEOMETRY
TEMPS_PLUS_HMN VARCHAR2(50)
TEMPS_MOINS_HMN VARCHAR2(50)
TEMPS_ZERO_HMN VARCHAR2(50)
TEMPS_PARCOURS_HMN VARCHAR2(50)
CROQUIS1 VARCHAR2(250)
CROQUIS2 VARCHAR2(250)
EXPORT_INTERNET VARCHAR2(5)
CLASSE_1 VARCHAR2(10)
CLASSE_2 VARCHAR2(10)
CLASSE_3 VARCHAR2(10)
CLASSE_4 VARCHAR2(10)
CLASSE_5 VARCHAR2(10)
CLASSE_6 VARCHAR2(10)
COTATION VARCHAR2(50)
CLASSEMENT VARCHAR2(50)
L_ESI_CTRL_BAL_GEST2 VARCHAR2(100)
ANNEE_ENTRETIEN VARCHAR2(4)
COMMENTAIRE_FR VARCHAR2(3000)
COMMENTAIRE_EN VARCHAR2(3000)
INFO_COMPLEMENTAIRE VARCHAR2(3000)
Note sure how you do it for postgresql, I just ran:
select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'esi_itin_itineraire2'
and got
"code_itin";"bigint";
"nom";"character varying";250
"l_esi_itin_type_esi";"character varying";100
"l_esi_ctrl_sous_type_esi";"character varying";100
"numero_dossier";"character varying";20
"nom_proprietaire";"character varying";100
"l_esi_ctrl_proprietaire";"character varying";100
"commune_esi";"character varying";100
"numero_insee";"integer";
"canton_esi";"character varying";100
"communaute_de_communes";"character varying";100
"numero_siren_comcom";"character varying";100
"pays_esi";"character varying";100
"l_esi_ctrl_federation";"character varying";100
"l_esi_ctrl_etape_inscription";"character varying";100
"date_inscription_pdesi";"timestamp without time zone";
"date_passage_cdesi";"timestamp without time zone";
"l_esi_ctrl_niveau_inscr";"character varying";100
"notation_environnement";"numeric";
"l_esi_ctrl_avis_env";"character varying";100
"commentaire_environnement";"character varying";250
"date_avis_environnement";"timestamp without time zone";
"document_environnment";"character varying";250
"notation_sport";"numeric";
"l_esi_ctrl_avis_sport";"character varying";100
"commentaire_sport";"character varying";250
"date_avis_sport";"timestamp without time zone";
"document_sport";"character varying";250
"notation_tourisme";"numeric";
"l_esi_ctrl_avis_tourisme";"character varying";100
"commentaire_tourisme";"character varying";250
"date_ctrl_avis_tourisme";"timestamp without time zone";
"document_tourisme";"character varying";250
"notation_transport";"numeric";
"l_esi_ctrl_avis_transport";"character varying";100
"commentaire_transport";"character varying";250
"date_avis_transport";"timestamp without time zone";
"document_transport";"character varying";250
"notation_route";"numeric";
"l_esi_ctrl_avis_route";"character varying";100
"commentaire_route";"character varying";250
"date_avis_route";"timestamp without time zone";
"document_route";"character varying";250
"notation_finale";"numeric";
"longueur";"numeric";
"l_esi_itin_ctrl_vocation";"character varying";100
"l_esi_itin_ctrl_vocation_secnd";"character varying";100
"l_esi_itin_ctrl_label";"character varying";100
"l_esi_ctrl_mo";"character varying";100
"l_esi_itin_ctrl_prat_hiv";"character varying";100
"l_esi_ctrl_rand_mot";"character varying";100
"l_esi_ctrl_handicap_moteur";"character varying";5
"l_esi_ctrl_handicap_auditif";"character varying";5
"l_esi_ctrl_handicap_visuel";"character varying";5
"l_esi_ctrl_handicap_mental";"character varying";5
"commune_depart";"character varying";250
"description_depart";"character varying";250
"commune_arrivee";"character varying";250
"description_arrivee";"character varying";250
"l_esi_itin_ctrl_parcours";"character varying";100
"l_esi_ctrl_difficulte";"character varying";100
"temps_parcours";"character varying";50
"proximite_base_sport_nature";"character varying";250
"prox_etablissement_scolaire";"character varying";250
"l_esi_ctrl_acces_scolaire";"character varying";100
"evenement_sportif";"character varying";250
"denivelee";"integer";
"autoris_de_passage";"character varying";5
"l_esi_ctrl_balisage";"character varying";100
"l_esi_itin_ctrl_bal_type";"character varying";100
"l_esi_ctrl_bal_gest";"character varying";100
"l_esi_ctrl_bal_etat";"character varying";100
"subvention_dep";"character varying";100
"promotion";"character varying";100
"nom_topoguide";"character varying";100
"commentaire";"character varying";250
"l_esi_ctrl_nom_projet";"character varying";100
"apb";"character varying";10
"reserve_naturelle";"character varying";10
"pnr";"character varying";10
"natura2000";"character varying";10
"sage";"character varying";10
"ens";"character varying";10
"znieff1";"character varying";10
"znieff2";"character varying";10
"zico";"character varying";10
"zppaup";"character varying";10
"site_classe";"character varying";10
"site_inscrit";"character varying";10
"zpens";"character varying";10
"l_esi_ctrl_existence_esi";"character varying";50
"l_esi_ctrl_nature_esi";"character varying";50
"acces_esi";"character varying";250
"l_esi_ctrl_mod_exploit";"character varying";50
"l_esi_ctrl_periode_ouv";"character varying";200
"l_esi_ctrl_modalite_ouv";"character varying";50
"l_esi_ctrl_organisation_esi";"character varying";50
"l_esi_ctrl_modalite_acces";"character varying";50
"l_esi_ctrl_periode_freq";"character varying";50
"l_esi_ctrl_freq_tour";"character varying";50
"l_esi_ctrl_imp_tour";"character varying";50
"l_esi_ctrl_freq_sportive";"character varying";50
"l_esi_ctrl_imp_sportive";"character varying";50
"l_esi_ctrl_freq_sociale";"character varying";50
"l_esi_ctrl_imp_sociale";"character varying";50
"attrait_esi";"character varying";250
"l_esi_ctrl_attractivite";"character varying";50
"equipements_adaptes";"character varying";10
"appel_secours";"character varying";10
"acces_securise";"character varying";10
"desc_acces_secours";"character varying";250
"decriptif_securite";"character varying";250
"gestionnaire_esi";"character varying";250
"l_esi_ctrl_convention_esi";"character varying";100
"presence_stationnement";"character varying";10
"etat_aire_stationnement";"character varying";250
"contraintes_reglementaires";"character varying";10
"denivelee_plus";"integer";
"denivelee_moins";"integer";
"longueur_plus";"bigint";
"longueur_moins";"bigint";
"temps_plus";"character varying";20
"temps_moins";"character varying";20
"user_maintenance";"character varying";250
"date_maintenance";"timestamp without time zone";
"stationnement_conventionne";"character varying";200
"longueur_zero";"bigint";
"temps_zero";"bigint";
"description_itin";"character varying";254
"date_debut";"timestamp without time zone";
"date_fin";"timestamp without time zone";
"etat";"bigint";
"id_evenement";"bigint";
"geometrie";"text";
"temps_plus_hmn";"character varying";50
"temps_moins_hmn";"character varying";50
"temps_zero_hmn";"character varying";50
"temps_parcours_hmn";"character varying";50
"croquis1";"character varying";250
"croquis2";"character varying";250
"export_internet";"character varying";5
"classe_1";"character varying";10
"classe_2";"character varying";10
"classe_3";"character varying";10
"classe_4";"character varying";10
"classe_5";"character varying";10
"classe_6";"character varying";10
"cotation";"character varying";50
"classement";"character varying";50
"l_esi_ctrl_bal_gest2";"character varying";100
"annee_entretien";"character varying";4
"commentaire_fr";"character varying";3000
"commentaire_en";"character varying";3000
"info_complementaire";"character varying";3000
So MDSYS.SDO_GEOMETRY for Oracle and "text" in postgresql, using the IMPORT FOREIGN SCHEMA;
I tried your lines and it worked. I also tried creating the FOREIGN table with the type "timestamp(0) without time zone" instead of date but it also works.
For your last questions, I ran this query in sqlplus:
select count(1) from ESI_ITIN_ITINERAIRE2 where date_inscription_pdesi is null;
and got a count of 643, so the lines have null dates.
But it turns out that selectind a row which has a valid date value:
select date_inscription_pdesi from esi."esi_itin_itineraire2" where code_itin = 127;
Also brings out the same error So the issue must be with date interpretation or something
Couple of question:
INSERT INTO datetest VALUES (4, to_date('100-01-01 BC', 'YYYY-MM-DD BC')); It fails with an error in french: Il faut préciser BC/B.C. ou AD/A.D.
But there is no AD / BC in my database just dates like "30/11/02". So maybe there is a confusion between time and date? Like my dates don't have a time so extraction goes wrong?
I am trying to find a better way to reproduce this
Weird thing, if I run:
select to_char(date_inscription_pdesi, 'dd/MM/YYYY') from ESI.ESI_ITIN_ITINERAIRE2 where date_inscription_pdesi is not null;
I get dates with 0:
All of the lines are zeros
I am really not good at Oracle, so maybe I am doing something wrong but it is correctly displayed in SQLDeveloper:
IMPORT FOREIGN SCHEMA maps Oracle DATE to "timestamp(0) without time zone" because Oracle dates have a time part, so this is the best mapping.
You can ALTER FOREIGN TABLE to change the type to "date" if your Oracle dates have zero time.
I guess that the problem you observe is due to the fact that the Oracle DATEs are NOT NULL, but look like 0000-00-00. PostgreSQL will correctly reject such a date.
To solve this it would be important to know what really is in these DATE columns. Try the following Oracle query:
SELECT dump(date_inscription_pdesi, 16) FROM esi_itin_itineraire2 WHERE code_itin = ...;
where the WHERE clause identifies one of the rows with 0000-00-00. That will help me figure out what is in those columns.
Again, thanks a lot for your help on this :) Here is what I ran:
SQL> SELECT dump(date_inscription_pdesi, 16) FROM esi_itin_itineraire2 WHERE cod
e_itin = 127;
DUMP(DATE_INSCRIPTION_PDESI,16)
--------------------------------------------------------------------------------
Typ=12 Len=7: 64,64,0,0,1,1,1
Hopefully, it makes sense to you !
Here is a good description of what the internal representation means.
In your case that should be: year 3636 before Christ, month 0, day 0 at midnight. But month or day must be at least 1!
So I'd say you got corrupt data in your Oracle database. How did those strange values get there? I don't think that oracle_fdw is to blame in this case.
Well I imported a dump from a customer's database into my own oracle installation, maybe something messed up in between. Anyway, I think that you are right, oracle_fdw is probably not to blame here. Should this issue be closed then?
Sure. But I'd be interested in any information how these values got there.
I'll leave a comment if I ever find out.
And again, thanks a lot for your help/patience !
Hello,
First of all, thanks a lot for your work on this project, it is incredibly useful :)
I am running latest PG 9.5 (9.5.3), using oracle_fdw-1.4.0-pg95-win64 that I downloaded from Github, with Oracle instantclient-basic-windows.x64-12.1.0.2.0. This is all running on my machine on Windows 10 64-Bits. I successfully used IMPORT FOREIGN SCHEMA but when I try to select one of the tables, I get the following error:
Means (in french) that the value is out of date/time bounds. The field of type DATE, nullable. And it does indeed contain some null values:![image](https://cloud.githubusercontent.com/assets/1240481/15425114/7b12bdf0-1e86-11e6-8076-a44e54d8ee08.png)
I looked at the issues and found some related to date encoding but fixed in the 1.4.0 changelog. I think it might be related to the fact that the value is null and not considered null during the "transfer" or something.
Thanks for your help, Fabien