7 Replies Latest reply: Nov 21, 2012 12:55 AM by klaus.barthels RSS

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

    klaus.barthels
      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