How to load data in CSV in Oracle SEM SQL Loader?

Question

How to load data in CSV in Oracle SEM SQL Loader?

Navigation

#1 by (1 votes)

1

The data in a table has been exported to a CSV file and would like to import it into a table in the Oracle database. However I need it to be without the sqlldr command. Because this load will be done in Java, and I have restrictions on using SQL Loader applied by the database team.

Consider that this CSV can hold millions of records!

I think the Foreign Table option would not work, because I'm working with millions of records.

java oracle batch csv

asked by anonymous 04.01.2017 / 11:05

1 answer

Spring MVC + Spring Data Ignore http error code when using file_get_contents

score 1 · Answer 1

The Oracle database has a feature called ORACLE_LOADER, where you do not import the file into a database table. A table is created that points to a CSV file (in fact, it does not have to be a .CSV file), so you can have a process that exclusively loads the file without having to destroy and build the table object again. Here is a code example using ORACLE_LOADER:

    CREATE TABLE DE_PARA_PECAS
(
CD_PRODUTO_ANTERIOR  varchar2(20),
CD_PRODUTO_NOVO      varchar2(20),
VL_CUSTO             varchar2(20)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY NFE
     ACCESS PARAMETERS 
       ( records delimited  by newline
        fields  terminated by ';'
        missing field values are null
             )
     LOCATION (NFE:'de_para_pecas.csv')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

Follow the link to access the Oracle manual that features this feature: ORACLE_LOADER_HELP_CENTER