Home » Non-English Forums » French » error in executing ODCIEXTTABLEFETCH callout (AIX v.5)
|
|
|
|
| Re: error in executing ODCIEXTTABLEFETCH callout [message #362817 is a reply to message #362699] |
Thu, 04 December 2008 03:36   |
ibtissamr
Messages: 2
Registered: December 2008
Location: maroc
|
Junior Member |
|
|
salut
je pense que l'erreur vient du format d'un des colonnes ou bien celle des champs du fichier
pour tester j'ai mis une seule ligne dans mon fichier
8298;STE D ALIL.MOERN;LE AVREI -BANGER;16;45;RUE FDERIBC BKANR;14600;LE HAVRE;01-JAN-2005;8328;POPB45;8118;8118;8118;D ALIM.MODERNE;D CLIM.MODERNE;2;LE HAMEAU DU MPPIL;17300;COURBEPINE;SAS;FR 11 111 111 111;B 111 111 111;2
j'ai testé les 4 premieres colonnes j'ai pu selectionner les données se la table tomporaire, mais quand j'ajoute les autres colonnes les deux messages d'erreurs apparaissent encore
voila le code de mon etl:
SET ECHO ON
SET TIMING ON
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
-- suppression de la table externe
DROP TABLE SQLLDR_FURST_MARKET_TBL;
WHENEVER OSERROR EXIT 255
WHENEVER SQLERROR EXIT 255
-- creation et parametrage de la table externe
CREATE TABLE SQLLDR_FURST_MARKET_TBL
(
ID_MAGASIN_INTERNE_BOFI VARCHAR2(4),
ID_SOCIETE VARCHAR2(20),
LIBELLE_MAGASIN VARCHAR2(30),
CODE_REGION VARCHAR2(4),
MAG_NUM_VOIE VARCHAR2(10),
MAG_ADRESSE VARCHAR2(100),
MAG_CODE_POSTAL VARCHAR2(5),
MAG_VILLE VARCHAR2(50),
DATE_DEMARRAGE_FID DATE,
ID_MAGASIN_PS VARCHAR2(20),
ID_MAGASIN_NANCI VARCHAR2(20),
ID_MAGASIN_DWH VARCHAR2(20),
ID_MAGASIN_BCP VARCHAR2(20)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader DEFAULT DIRECTORY SQLLDR_FMARKET_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
LOAD WHEN (
(ID_MAGASIN_INTERNE_BOFI != BLANKS)
AND (ID_SOCIETE != BLANKS)
)
BADFILE SQLLDR_FMARKET_TMPDIR_00001:'&1..bad'
LOGFILE SQLLDR_FMARKET_TMPDIR_00002:'&1..log'
DISCARDFILE SQLLDR_FMARKET_TMPDIR_00001:'&1..dsc'
FIELDS TERMINATED BY ";"
(
ID_MAGASIN_INTERNE_BOFI CHAR(4),
ID_SOCIETE CHAR(20),
LIBELLE_MAGASIN CHAR(30),
CODE_REGION CHAR(4),
MAG_NUM_VOIE CHAR(10),
MAG_ADRESSE CHAR(100),
MAG_CODE_POSTAL CHAR(5),
MAG_VILLE CHAR(50),
DATE_DEMARRAGE_FID CHAR(10),
ID_MAGASIN_PS CHAR(1),
ID_MAGASIN_NANCI CHAR(20),
ID_MAGASIN_DWH CHAR(20) ,
ID_MAGASIN_BCP CHAR(20)
)
)
LOCATION (SQLLDR_FMARKET_TMPDIR_00000:'&1'))
REJECT LIMIT UNLIMITED;
BEGIN
INSERT INTO TBL_TMP_FURST_MARKET
( TTFM_ID_MAGASIN_INTERNE_BOFI,
TTFM_ID_SOCIETE,
TTFM_LIBELLE_MAGASIN,
TTFM_CODE_REGION,
TTFM_MAG_NUM_VOIE,
TTFM_MAG_ADRESSE,
TTFM_MAG_CODE_POSTAL,
TTFM_MAG_VILLE,
TTFM_DATE_DEMARRAGE_FID,
TTFM_ID_MAGASIN_PS,
TTFM_ID_MAGASIN_NANCI,
TTFM_ID_MAGASIN_DWH,
TTFM_ID_MAGASIN_BCP
)
SELECT
ID_MAGASIN_INTERNE_BOFI,
ID_SOCIETE,
LIBELLE_MAGASIN,
CODE_REGION,
MAG_NUM_VOIE,
MAG_ADRESSE,
MAG_CODE_POSTAL,
MAG_VILLE,
to_date(DATE_DEMARRAGE_FID,'DD-MM-RRRR'),
ID_MAGASIN_PS,
ID_MAGASIN_NANCI,
ID_MAGASIN_DWH,
ID_MAGASIN_BCP
FROM SQLLDR_FURST_MARKET_TBL;
END;
/
EXIT
|
|
|
|
|
|
| Re: error in executing ODCIEXTTABLEFETCH callout [message #362967 is a reply to message #362817] |
Thu, 04 December 2008 12:56 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_11g> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'
2 /
Session altered.
SCOTT@orcl_11g> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'
2 /
Session altered.
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY sqlldr_fmarket_tmpdir_00000 AS 'c:\oracle11g'
2 /
Directory created.
SCOTT@orcl_11g> CREATE TABLE tbl_extrn
2 (
3 ID_MAGASIN_INTERNE_BOFI VARCHAR2(4),
4 ID_SOCIETE VARCHAR2(20),
5 LIBELLE_MAGASIN VARCHAR2(30),
6 CODE_REGION VARCHAR2(4),
7 MAG_NUM_VOIE VARCHAR2(10),
8 MAG_ADRESSE VARCHAR2(100),
9 MAG_CODE_POSTAL VARCHAR2(5),
10 MAG_VILLE VARCHAR2(50),
11 DATE_DEMARRAGE_FID DATE,
12 ID_MAGASIN_PS VARCHAR2(20),
13 ID_MAGASIN_NANCI VARCHAR2(20),
14 ID_MAGASIN_DWH VARCHAR2(20),
15 ID_MAGASIN_BCP VARCHAR2(20)
16 )
17 ORGANIZATION EXTERNAL
18 (
19 TYPE oracle_loader DEFAULT DIRECTORY SQLLDR_FMARKET_TMPDIR_00000
20 ACCESS PARAMETERS
21 (
22 RECORDS DELIMITED BY NEWLINE
23 LOAD WHEN (
24 (ID_MAGASIN_INTERNE_BOFI != BLANKS)
25 AND (ID_SOCIETE != BLANKS)
26 )
27 BADFILE SQLLDR_FMARKET_TMPDIR_00000:'test.bad'
28 LOGFILE SQLLDR_FMARKET_TMPDIR_00000:'test.log'
29 DISCARDFILE SQLLDR_FMARKET_TMPDIR_00000:'test.dsc'
30 FIELDS TERMINATED BY ";"
31 MISSING FIELD VALUES ARE NULL
32 (
33 ID_MAGASIN_INTERNE_BOFI CHAR(4),
34 ID_SOCIETE CHAR(20),
35 LIBELLE_MAGASIN CHAR(30),
36 CODE_REGION CHAR(4),
37 MAG_NUM_VOIE CHAR(10),
38 MAG_ADRESSE CHAR(100),
39 MAG_CODE_POSTAL CHAR(5),
40 MAG_VILLE CHAR(50),
41 DATE_DEMARRAGE_FID CHAR(11),
42 ID_MAGASIN_PS CHAR(4),
43 ID_MAGASIN_NANCI CHAR(20),
44 ID_MAGASIN_DWH CHAR(20) ,
45 ID_MAGASIN_BCP CHAR(20)
46 )
47 )
48 LOCATION (SQLLDR_FMARKET_TMPDIR_00000:'test.dat'))
49 REJECT LIMIT UNLIMITED
50 /
Table created.
SCOTT@orcl_11g> select * from tbl_extrn
2 /
ID_M ID_SOCIETE LIBELLE_MAGASIN CODE MAG_NUM_VO
---- -------------------- ------------------------------ ---- ----------
MAG_ADRESSE
--------------------------------------------------------------------------------
MAG_C MAG_VILLE DATE_DEMARR
----- -------------------------------------------------- -----------
ID_MAGASIN_PS ID_MAGASIN_NANCI ID_MAGASIN_DWH
-------------------- -------------------- --------------------
ID_MAGASIN_BCP
--------------------
8298 STE D ALIL.MOERN LE AVREI -BANGER 16 45
RUE FDERIBC BKANR
14600 LE HAVRE 01-JAN-2005
8328 POPB45 8118
8118
SCOTT@orcl_11g>
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 11:40:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
