Script to load data from csv file to oracle table : PLSQL script to load data from excel file to Oracle apps table

Script to load data from csv file to oracle table : PLSQL script to load data from excel file to Oracle apps table

In this post , we will be discuss about the PLSQL script , which helps to import the data directly from csv or excel file into oracle tables. We don't need to use the sql loader to import the data from external csv files into oracle table but we can use simply these scripts to upload the data from external csv file to oracle table. Here below i will share the complete steps and script to load data from csv file to oracle table. 

Using this script , we first need to put the external csv or excel file into the Oracle FTP server directory. 

We should have the Directory name for that Directory Path. For example Directory Path '/server/file_dir/infile/' directory name could be 'XX_FILE_DIR' 

We can find the Directory name from Table 'DBA_DIRECTORIES'.

Using this Directory name we can load the data from csv file to oracle tables. 

Sample Script to load data from csv file to oracle table

XX_LOAD_SUPPLIER_DATA is the Dummy Table which will store the Data from the external csv file.

XX_FILE_DIR is the Directory name used in this below script which we discussed in the above steps.

:P_FILE_NAME is the CSV file name from which this script load the data.

Note : csv file should be in the Directory path which directory name we have given in this PLSQL script.

execute immediate 'CREATE TABLE XX_LOAD_SUPPLIER_DATA

(

  SUPPLIER_NUMBER      VARCHAR2(4000 BYTE),

  SUPLIER_NAME            VARCHAR2(4000 BYTE),

  DATE_OF_BIRTH        VARCHAR2(400 BYTE),

  NATIONAL_IDENTIFIER  VARCHAR2(4000 BYTE),

  GENDER                VARCHAR2(400 BYTE), 

  EMAIL_ADDRESS        VARCHAR2(4000 BYTE),

  PHONE           VARCHAR2(4000 BYTE),

  FAX           VARCHAR2(4000 BYTE),

  CREATION_DATE            VARCHAR2(4000 BYTE))

Organization external

(type oracle_loader

default directory XX_FILE_DIR '||

'access parameters (records delimited by newline

 BADFILE '''|| :P_FILE_NAME||'.bad''' ||

'LOGFILE '''||:P_FILE_NAME||'.log''' ||

'fields terminated by ''|''

MISSING FIELD VALUES ARE NULL)

location ( ''' ||:P_FILE_NAME||''' ))

reject limit UNLIMITED';