@echo on

call oraenv.bat


if not exist %mainpath% goto mainpath_def
if not exist %scripts% goto scriptspath_def
if not exist %logs% goto logspath_def
if not exist %data% goto datapath_def



goto x1

:x1
sqlldr userid = scott/tiger@orcl control=%mainpath%\ldrtest.ctl data=%mainpath%\data\ldrtest.csv log=%logs%\ldrtest.log discard=%logs%\ldrtest.dsc bad=%logs%\ldrtest.bad skip=1 SILENT=(HEADER, FEEDBACK)
sqlplus -s scott/tiger@orcl @%scripts%\merge_stg.sql 

goto end

:mainpath_def
echo "mainpath not existing as specified %mainpath%, Please edit ORAENV with correct value"
goto exit 

:scriptspath_def
echo "scripts path not existing as specified %scripts%, Please edit ORAENV with correct value"
goto exit  

:logspath_def
echo "Logs path not existing as specified %logs%, Please edit ORAENV with correct value"
goto exit 

:datapath_def
echo "Data path not existing as specified %data%, Please edit ORAENV with correct value"
goto exit  


:end
EXIT

declare
	l_prg_name VARCHAR2(30):='chk_dup';
begin

	insert into errtable (id,code,row_name, trdate, error_desc)
	select id, code, row_name, sysdate, 'Duplicate rows for '||id|| '-'||code||'-'||row_name
	from	(select id, code, row_name, count(*) 
		from stg_ldrtest
		group by id, code, row_name
		having count(*) > 1
		);


	MERGE INTO ldrtest D
		   USING (SELECT 
				stg.code, stg.id, stg.row_name, stg.col1, stg.col2, stg.col3, 
				stg.crt_tm, stg.md_tm, stg.crt_user, stg.mod_usr 
			  FROM 
				stg_ldrtest stg
			  WHERE
				not exists (SELECT 
						 1 
					    FROM errtable err
					    WHERE
						err.id = stg.id
					    AND	err.code = stg.code
					    AND	err.row_name = stg.row_name
					    ) 
			  ) S
		      ON (d.code = s.code and d.id = s.id and d.row_name=s.row_name )
	 WHEN MATCHED THEN 
		UPDATE SET  d.col1 = s.col1,
			    d.col2 = s.col2,
			    d.col3 = s.col3,
			    d.crt_tm = s.crt_tm,
			    d.md_tm = s.md_tm,
			    d.crt_user = s.crt_user,
			    d.mod_usr = s.mod_usr
	  WHEN NOT MATCHED THEN 
		INSERT (d.code,d.id,d.row_name,d.col1,d.col2,d.col3,d.crt_tm,d.md_tm,d.crt_user,d.mod_usr)
	     VALUES (s.code,s.id,S.row_name, s.col1,s.col2,s.col3,s.crt_tm,s.md_tm,s.crt_user,s.mod_usr);

	  COMMIT;

	DBMS_OUTPUT.PUT_LINE('MERGE DONE');

exception
 	when others then
  	RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM||' IN '||l_Prg_Name);

end;

CODE,ID,ROW_NAME,COL1,COL2,COL3,CRT_TM,MD_TM,CRT_USER,MOD_USR
74,704,finagree,0,5,0,1/1/2008,2/24/2008,roger,smith
84,804,noagre,2,1,1,9/9/2008,4/11/2007,subbaram,father
24,0,finagree,2,2,2,2/15/2008,12/22/2008,joe,scott
74,704,agreement,5,5,5,10/10/2007,3/14/2008,scott,smith
54,540,wrow,1,7,7,5/14/2008,1/21/2008,SCOTT,PETER
24,0,finagree,2,2,2,2/15/2008,12/22/2008,joe,scott

load data
into table stg_ldrtest
replace
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
(code,
 id,
 row_name "upper(:row_name)",
 col1,
 col2,
 col3,
 crt_tm "to_date(:crt_tm,'MM/DD/YYYY')",
 MD_tm "to_date(:md_tm,'MM/DD/YYYY')",
 CRT_USER "UPPER(:CRT_USER)",
 MOD_USR "UPPER(:MOD_USR)"
 )

@echo on

call oraenv.bat


if not exist %mainpath% goto mainpath_def
if not exist %scripts% goto scriptspath_def
if not exist %logs% goto logspath_def
if not exist %data% goto datapath_def

echo "load for " %1

goto x1

:x1
sqlldr userid = rk/rk control=%mainpath%\%1.ctl data=%mainpath%\data\%1.csv log=%logs%\%1.log discard=%logs%\%1.dsc bad=%logs%\%1.bad skip=1 SILENT=(HEADER, FEEDBACK)
sqlplus -s rk/rk@orcl10g @%scripts%\merge_stg.sql 

goto end

:mainpath_def
echo "mainpath not existing as specified %mainpath%, Please edit ORAENV with correct value"
goto exit 

:scriptspath_def
echo "scripts path not existing as specified %scripts%, Please edit ORAENV with correct value"
goto exit  

:logspath_def
echo "Logs path not existing as specified %logs%, Please edit ORAENV with correct value"
goto exit 

:datapath_def
echo "Data path not existing as specified %data%, Please edit ORAENV with correct value"
goto exit  


:end
EXIT

declare
	l_prg_name VARCHAR2(30):='chk_dup';
begin

	insert into errtable (id,code,row_name, trdate, error_desc)
	select id, code, row_name, sysdate, 'Duplicate rows for '||id|| '-'||code||'-'||row_name
	from	(select id, code, row_name, count(*) 
		from stg_ldrtest
		group by id, code, row_name
		having count(*) > 1
		);


	MERGE INTO ldrtest D
		   USING (SELECT 
				stg.code, stg.id, stg.row_name, stg.col1, stg.col2, stg.col3, 
				stg.crt_tm, stg.md_tm, stg.crt_user, stg.mod_usr 
			  FROM 
				stg_ldrtest stg
			  WHERE
				not exists (SELECT 
						 1 
					    FROM errtable err
					    WHERE
						err.id = stg.id
					    AND	err.code = stg.code
					    AND	err.row_name = stg.row_name
					    ) 
			  ) S
		      ON (d.code = s.code and d.id = s.id and d.row_name=s.row_name )
	 WHEN MATCHED THEN 
		UPDATE SET  d.col1 = s.col1,
			    d.col2 = s.col2,
			    d.col3 = s.col3,
			    d.crt_tm = s.crt_tm,
			    d.md_tm = s.md_tm,
			    d.crt_user = s.crt_user,
			    d.mod_usr = s.mod_usr
	  WHEN NOT MATCHED THEN 
		INSERT (d.code,d.id,d.row_name,d.col1,d.col2,d.col3,d.crt_tm,d.md_tm,d.crt_user,d.mod_usr)
	     VALUES (s.code,s.id,S.row_name, s.col1,s.col2,s.col3,s.crt_tm,s.md_tm,s.crt_user,s.mod_usr);

	  COMMIT;

	DBMS_OUTPUT.PUT_LINE('MERGE DONE');

exception
 	when others then
  	RAISE_APPLICATION_ERROR(-20000, 'DB Error: '||SQLERRM||' IN '||l_Prg_Name);

end;
/
exit;
/

REM set mainpath=D:\cm
set mainpath=D:\cm
set scripts=%mainpath%\scripts
set logs=%mainpath%\logs
set data=%mainpath%\data

@echo on

call oraenv.bat


if not exist %mainpath% goto mainpath_def
if not exist %scripts% goto scriptspath_def
if not exist %logs% goto logspath_def
if not exist %data% goto datapath_def

echo "load for " %1

goto x1

:x1
sqlldr userid = gautam/gautam@orcl control=%mainpath%\%1.ctl data=%mainpath%\data\%1.csv log=%logs%\%1.log discard=%logs%\%1.dsc bad=%logs%\%1.bad skip=1 SILENT=(HEADER, FEEDBACK)
move %mainpath%\data\%1.csv %mainpath%\bkp\%1.csv
sqlplus -s gautam/gautam@orcl @%scripts%\merge_stg.sql 

 goto end

:mainpath_def
echo "mainpath not existing as specified %mainpath%, Please edit ORAENV with correct value"
goto exit 

:scriptspath_def
echo "scripts path not existing as specified %scripts%, Please edit ORAENV with correct value"
goto exit  

:logspath_def
echo "Logs path not existing as specified %logs%, Please edit ORAENV with correct value"
goto exit 

:datapath_def
echo "Data path not existing as specified %data%, Please edit ORAENV with correct value"
goto exit  


:end
EXIT

Merge into emp e
using load ld
on (e.emp_id = ld.emp_id)
when matched then
  update set 
    e.dept_id = nvl(ld.dept_id, e.dept_id), 
    e.ename = nvl (ld.ename, e.ename), 
    e.job = nvl (ld.job, e.job),
    e.sal = nvl (ld.sal, e.sal)
when not matched then
  insert  (emp_id, dept_id, ename, job, sal)
  values (ld.emp_id, ld.dept_id, ld.ename, ld.job, ld.sal);