best approach to import CSV file from FTP

We are on apps9. I would use a file data migration job to load the csv into the database and then use plsql procedure and api’s to update the relevant objects in IFS. You would need something to copy the file from FTP to the utl_file_dir.

I've found using Oracle Loader external tables is easier than using migration jobs. They're far easier to reuse for multiple purposes.

You'll need to edit this if you want more than 10 columns or more than 256 characters in your data. You'll also need to cast strings to numbers or dates as needed downstream.

Reading from a CSV file sitting in Oracle directory is a lot like reading from a view into that file.

-- AS SYS:
CREATE OR REPLACE DIRECTORY c_external_csv_dir AS '\\your\server\location';
GRANT read, write, execute ON DIRECTORY c_external_csv_dir TO ifsapp;
GRANT read, write, execute ON DIRECTORY c_external_csv_dir TO ifssys;

-- AS IFSAPP:
CREATE TABLE c_external_csv_tab
(
   a1   VARCHAR2(256),
   a2   VARCHAR2(256),
   a3   VARCHAR2(256),
   a4   VARCHAR2(256),
   a5   VARCHAR2(256),
   a6   VARCHAR2(256),
   a7   VARCHAR2(256),
   a8   VARCHAR2(256),
   a9   VARCHAR2(256),
   a10  VARCHAR2(256)
)
ORGANIZATION EXTERNAL
(
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY c_external_csv_dir
   ACCESS PARAMETERS 
   (
      RECORDS DELIMITED BY newline
      BADFILE 'c_external_csv_tab.bad'
      DISCARDFILE 'c_external_csv_tab.dis'
      LOGFILE 'c_external_csv_tab.log'
      SKIP 1
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
      LTRIM
      MISSING FIELD VALUES ARE NULL
      (
         a1   CHAR(256),
         a2   CHAR(256),
         a3   CHAR(256),
         a4   CHAR(256),
         a5   CHAR(256),
         a6   CHAR(256),
         a7   CHAR(256),
         a8   CHAR(256),
         a9   CHAR(256),
         a10  CHAR(256)
      )
  )
  LOCATION (c_external_csv_dir:'c_external_csv_tab.csv')
)
REJECT LIMIT UNLIMITED;

GRANT SELECT ON c_external_csv_tab TO ifsinfo WITH GRANT OPTION;
GRANT SELECT ON c_external_csv_tab TO ifssys WITH GRANT OPTION;
GRANT SELECT ON c_external_csv_tab TO ifsprint;


-- C_EXTERNAL_CSV_TAB now acts like a view to read directly from the CSV.
-- Drop in a CSV file into that directory, then read from it like this:

SELECT * FROM c_external_csv_tab;