Import large database tables from CSV files using oracle_loader driver in Oracle External Table

After trying various techniques to import csv data to oracle tables, I found this as the simplest and the fastest way to import large databases into oracle tables. This technique uses oracle_loader driver in Oracle External Table. The basic is that external table displays data by reading from a physical file.

[dfads params=’groups=-1′]

Steps:
1. Create a physical directory in one of your drive. Paste your csv data into the same directory.

2. Execute the script below on sqldeveloper.

3. Congratulations, play with your data tables.

[code]
create or replace directory my_data_app as ‘D:my_data_app’;

CREATE TABLE BULK_KYC_UPLOAD_BY_EMPLOYER_AI
( office_id number(11) ,
uan varchar2(12) ,
cur_mid varchar2(25) ,
document_type varchar2(1) ,
document_name varchar2(85) ,
document_no varchar2(25) ,
ifsc_code varchar2(11) ,
employee_name varchar2(100) ,
expiry_date varchar2(50) ,
edu_quali_flag varchar2(1) ,
phy_hand_flag varchar2(1) ,
phy_hand_cat_flag varchar2(1) ,
gender_flag varchar2(1) ,
number_worker_flag varchar2(1) ,
martial_status_flag varchar2(1) ,
establishment_id varchar2(15) ,
scan_document_flag number(11) ,
alredy_veryfied_flag number(11) ,
document_scan_image varchar2(100) ,
dob varchar2(50) ,
doj varchar2(50) ,
doe varchar2(50) ,
father_husband_name varchar2(85) ,
fs_flag varchar2(1) ,
bulk_kyc_tracking_id varchar2(15) ,
text_file_upload_date varchar2(50) ,
online_verification number(1),
ts_online_verification varchar2(50) ,
employer_verification number(1) ,
ts_employer_verification varchar2(50) ,
field_office_verification number(1),
ts_field_office_verification varchar2(50) ,
added_by varchar2(1) ,
missing_detail_source_flag varchar2(1) ,
ts varchar2(50),
duplicate_flag varchar2(2),
duplicate_ts varchar2(50)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY my_data_app
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
SKIP 1
LOGFILE my_data_app:’data.log’
BADFILE my_data_app:’data.bad’
DISCARDFILE my_data_app:’data.disc’
fields terminated by ‘,’
OPTIONALLY ENCLOSED BY ‘"’
MISSING FIELD VALUES ARE NULL
(
office_id,
uan ,
cur_mid ,
document_type ,
document_name,
document_no,
ifsc_code,
employee_name,
expiry_date,
edu_quali_flag,
phy_hand_flag,
phy_hand_cat_flag,
gender_flag,
number_worker_flag,
martial_status_flag,
establishment_id,
scan_document_flag,
alredy_veryfied_flag,
document_scan_image,
dob,
doj,
doe,
father_husband_name,
fs_flag,
bulk_kyc_tracking_id,
text_file_upload_date,
online_verification,
ts_online_verification,
employer_verification,
ts_employer_verification,
field_office_verification,
ts_field_office_verification,
added_by,
missing_detail_source_flag,
ts,
duplicate_flag,
duplicate_ts
)
)
LOCATION (my_data_app:’kyc_16012015.csv’)
)reject limit unlimited;

select * from BULK_KYC_UPLOAD_BY_EMPLOYER_AI;

select * from BULK_KYC_UPLOAD_BY_EMPLOYER_AF rownum < 200;

drop table BULK_KYC_UPLOAD_BY_EMPLOYER_AI;

insert into bulk_kyc_upload_by_employer_af select * FROM bulk_kyc_upload_by_employer_ai;
commit;
[/code]

In order to Export large database table into a dump file using oracle_datapump driver in Oracle External Table, Click Here
[dfads params=’groups=-1′]

Export large database table into a dump file using oracle_datapump driver in Oracle External Table

[dfads params=’groups=-1′]

There are various ways to export oracle database tables. Here, I was dealing with large database tables when I was working at one of the india’s biggest government company. I had to import and export crores of tuples from and to csv files.
Following is the simplest example to unload data from huge oracle database to a dump file using oracle_datapump driver in Oracle External Table.

[code]create or replace directory my_data_app as ‘D:my_data_app’;

create table bulk_kyc_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY my_data_app
LOCATION (‘bulk_kyc_xt.dmp’)
)
AS SELECT * FROM bulk_kyc_upload_by_employer_af;

select * from bulk_kyc_xt;

drop table bulk_kyc_xt;

[/code]

In order to import oracle table from a csv file using oracle external table, click here.

[dfads params=’groups=-1′]