Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How to load xl sheet data into oracle database ? and how to write script for insert and update ? oracle external table concept

Load the  excel data into oracle database if row is already exists  script will perform update of that particular rows, if row is not exists in main table that row will insert on main table.this will be done with the help of external table
 

After created external table merge procedure will compare both table main table as well as external table each and every rows if row exists in main table that rows only will be update on main table not external table,when comparing if rows is not exists in main table that external tables rows will be update on main table.

Here I  used here oracle client as well as sql developer tools.

PREREQUEST:
1.Also ensure that the directory is on the DB server not client
2.Oracle user group is not able to write the access in the ETL_DATA_DIRECTORY folder.
3.External tables should only read from the DB server. There should be absolutely no dependency on the client file system, there should be absolutely no dependency on being able to connect and access a directory on your PC.

4.LINUX DIRECTORY PERMISSION SHOULD BE FOLLOWED 775,I GIVEN CLIENT DIRECTORY PATH
[oracle@ol5-11gr2 ~]$ ls -l /home/oracle/
total 1
drwxrwxr-x 2 oracle oracle  4096 Apr 22 03:25 data_load
I.Following mentioned data's are excel sheet and main table data's
In "/home/oracle/data_load/data.csv" I have a file that looks like this:

cat /home/oracle/data_load/data.csv
id,name
2,rumaisha
7,kathar
8,ram
9,mohad
4,kaja
18,ram
5,jamal
database Source table look like following:
select * from data;
1 nijam
2 junaith
3 abu
4 umar
5 appa
II.For external tables, we need to use a directory object -- we'll start with that, mapping to the "/home/oracle/data_load/data.csv" directory

grant create directory privillege to u1 user:

GRANT CREATE ANY DIRECTORY,DROP ANY DIRECTORY TO u1;
Note: if you create wrong directory ,drop any directory privilege useful for droping wrong directory
connect as u1 user:

create or replace directory u1_def_dir1 as '/home/oracle/data_load/'; 
create or replace directory u1_def_dir2 as '/home/oracle/data_load/';
create or replace directory u1_def_dir3 as '/home/oracle/data_load/';
III.Now, we'll create the external table, this following script will be helpful to create external table and load the excel data values to  external table.
dbk1.sh
CREATE TABLE P_LOAD
(
id NUMBER(20),
name VARCHAR2(100)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY u1_def_dir1
ACCESS PARAMETERS 
(
RECORDS DELIMITED by NEWLINE
badfile u1_def_dir2:'p_load_%a_%p.bad'
logfile u1_def_dir2:'p_load_%a_%p.log'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
id  CHAR(20),
name CHAR(100)
)
)
LOCATION ('data.csv')
)
parallel 5 
REJECT LIMIT UNLIMITED;
IV.Now you can confirm whether external table created or not: 
SQL> select * from  u1.P_LOAD;

        ID NAME
---------- --------------------
         2 rumaisha
         7 kathar
         8 ram
         9 mohad
         4 kaja
        18 ram
         5 jamal

7 rows selected.
V.You could sync up the flat file with the database table using this single command:
Thi script compare both table if the values is matched then that rows will be updated if rows is not matched rows  will be inserted on source table.

PREREQUEST:
BEFORE issue this command  take the backup of data(main table) table.

merge into data e1 
using P_LOAD e2 
on ( e2.id = e1.id ) 
when matched then 
update set e1.name = e2.name 
when not matched then 
insert ( id,name ) 
values ( e2.id, e2.name); 
Post Request:
issue commit after checking the some sample datas of main table whether is changed or not.

commit;
VI.check the data whether merged or not
SQL> select * from u1.data;
        ID NAME
---------- ------------------------------
         1 nijam
         2 rumaisha
         3 abu
         4 kaja
         5 jamal
         7 kathar
         8 ram
         9 mohad
        18 ram

9 rows selected.
Wow! basic oracle external table concept is over next we will see  external table clob & blob trasformation

No comments:

Post a Comment