This discussion is archived
7 Replies Latest reply: Nov 20, 2012 10:55 PM by klaus.barthels RSS

How to load external table with number-field with trailing minus

klaus.barthels Newbie
Currently Being Moderated
Hi I try to load an external table with a textfile that contains numeric-fields (KB_UMSATZ) with trailing minus (" 1234,56-").
The DLL I use is the following:
*************************************************
CREATE TABLE MIRATOOL.EXT_KBTZ010
+(+
KB_GRDNR   INTEGER,
KB_UK      INTEGER,
KB_KA      INTEGER,
KB_VALDAT  DATE,
KB_UMSATZ  NUMBER(12,2),
KB_TZ      INTEGER
+)+
ORGANIZATION EXTERNAL
+( TYPE ORACLE_LOADER+
DEFAULT DIRECTORY PFAD_CSV_IN_HOST
ACCESS PARAMETERS

+( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY "|"+

LDRTRIM
+(KB_GRDNR+

+,KB_UK+
+,KB_VALDAT DATE "YYYYMMDD"+
+,KB_KA+

+,KB_UMSATZ char "DECODE(substr(:KB_UMSATZ, 13, 1), '-', -to_number(substr(:KB_UMSATZ, 1, 12)), to_number(substr(:KB_UMSATZ, 1, 12)))"+
+,KB_TZ+

+)+

+)+
LOCATION (PFAD_CSV_IN_HOST:'LO.SDS.KBTZ010')
+)+
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
*************************************************

If I try to view the data, I've got the following error:
---------------------------------------------------------------------
ORA-29913: Fehler bei der Ausführung von Aufruf ODCIEXTTABLEOPEN
ORA-29400: Data Cartridge-Fehler
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "double-quoted-string": expecting one of: "comma, date_format, defaultif, enclosed, (, ltrim, lrtrim, ldrtrim, notrim, nullif, optionally, ), rtrim, terminated"
KUP-01007: at line 7 column 30
ORA-06512: in "SYS.ORACLE_LOADER", Zeile 19
---------------------------------------------------------------------

Can someone give me a hint how to solve my problem??

Regards from germany

Klaus

Edited by: klaus.barthels on Nov 20, 2012 3:38 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points