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
In "/home/oracle/data_load/data.csv" I have a file that looks like this:
grant create directory privillege to u1 user:
connect as u1 user:
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.
issue commit after checking the some sample datas of main table whether is changed or not.
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_loadI.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,jamaldatabase Source table look like following:
select * from data; 1 nijam 2 junaith 3 abu 4 umar 5 appaII.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
Comments
Post a Comment