0

--the preprocessor is showing as an error when in sql developer oracle 12G

CREATE TABLE WPP_EXT (
    ACCOUNT_ID  VARCHAR2(50),
    SER_NUM     VARCHAR2(100)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  default directory EXT_DIR
  access parameters (
      RECORDS DELIMITED BY NEWLINE
      PREPROCESSOR EXEC_DIR: 'firstfilerename.bat'
    NOLOGFILE
    skip 1
    FIELDS TERMINATED by ',' 
           ( 
            ACCOUNT_ID, 
            DUMMY,
           DUMMY,
            SER_NUM
          )
  )
  LOCATION ('data_1.csv')
)
reject limit unlimited;
eaolson
  • 14,717
  • 7
  • 43
  • 58
Nick
  • 117
  • 1
  • 9
  • Specifically what error are you gettting? Are you getting the error when you create the table or when you select from it? The fact that you have two `DUMMY` columns is a red flag to me, but I'd have to look up the syntax. – eaolson Jul 17 '18 at 00:40
  • it works fine without the preprocessor.. is it because my bat file is located on a network drive? though it is also defined in all directories – Nick Jul 17 '18 at 00:49
  • the table was successfully created, and it shows me this error when i select it ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file data_1.csv in EXT_DIR not found 29913. 00000 - "error in executing %s callout" *Cause: The execution of the specified callout caused an error. *Action: Examine the error messages take appropriate action. – Nick Jul 17 '18 at 00:51
  • Show the `CREATE DIRECTORY` command for EXEC_DIR, plus grants please? – Matthew McPeak Jul 17 '18 at 01:45
  • CREATE OR REPLACE DIRECTORY EXEC_DIR AS '/REPORT/WPP_EXT_DIR/EXEC'; GRANT ALL ON DIRECTORY EXEC_DIR TO PUBLIC; – Nick Jul 17 '18 at 02:46
  • the preprocessor line shows as an error in sql developer too – Nick Jul 17 '18 at 02:47
  • "EXEC_DIR" it should be execdir not exec_dir – Gaj Jul 17 '18 at 03:42
  • CREATE OR REPLACE DIRECTORY EXECDIR AS '/REPORTBAU/WPP_EXT_DIR/EXEC'; grant all on directory EXECDIR to public; PREPROCESSOR EXECDIR: 'firstfilerename.bat' .. still in error – Nick Jul 17 '18 at 05:07
  • Your file is a .bat, so I assume Windows, but the path is using Linux syntax. Is that the right syntax to create a directory object under Windows? – eaolson Jul 18 '18 at 02:42

0 Answers0