How to upload data to CSV in oracle SEM SQL Loader?

Asked

Viewed 1,375 times

0

Data from a table was exported in a CSV file and I would like to import it into a table in the Oracle database. But I need it to be without the sqlldr command. Because this load will be done in Java, and I have SQL Loader usage restrictions applied by the database team.

Consider that this CSV may hold millions of records!

I believe that the External Table option would not meet, because I am working with millions of records.

java oracle csv batch

  • Something needs to interpret the CSV, needs to put them in an INSERT and rotate them in the bank. For example: you can make a program to do this, you can mount the Inserts in Excel and run them in SQL-Plus and so on. Given the amount of lines SQLDR would be the best option

  • You create Java Classes to read this file, there are websites for this in Java and PLSQL https://docs.oracle.com/javase/tutorial/essential/io/file.html

1 answer

1

The Oracle database has a feature called ORACLE_LOADER, where the file is not imported into a database table. A table is created that points to a CSV file (in fact it doesn’t have to be a file in .CSV format), so you can have a process that exclusively loads the file without the need to destroy and build the table object again. Following is 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 link to access the Oracle manual that presents this Feature: ORACLE_LOADER_HELP_CENTER

Browser other questions tagged java oracle csv batch

You are not signed in. Login or sign up in order to post.