Home » Other » Test » cm1.bat
cm1.bat [message #353431] Mon, 13 October 2008 21:17 Go to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
@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


Re: cm1.bat [message #353455 is a reply to message #353431] Mon, 13 October 2008 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good but keep your lines in 80 characters.

Regards
Michel

[Updated on: Mon, 13 October 2008 23:17]

Report message to a moderator

Re: cm1.bat [message #354698 is a reply to message #353431] Mon, 20 October 2008 21:53 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
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


Re: cm1.bat [message #354699 is a reply to message #353431] Mon, 20 October 2008 21:55 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
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;
/


Re: cm1.bat [message #354701 is a reply to message #353431] Mon, 20 October 2008 22:18 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
REM set mainpath=D:\cm
set mainpath=D:\cm
set scripts=%mainpath%\scripts
set logs=%mainpath%\logs
set data=%mainpath%\data
Re: cm1.bat [message #354709 is a reply to message #353431] Mon, 20 October 2008 23:08 Go to previous messageGo to next message
ora1980
Messages: 251
Registered: May 2008
Senior Member
last test


@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

Re: cm1.bat [message #354710 is a reply to message #354709] Mon, 20 October 2008 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same remark, keep your lines in 80 characters.

Regards
Michel
Re: cm1.bat [message #355986 is a reply to message #353431] Tue, 28 October 2008 22:09 Go to previous message
ora1980
Messages: 251
Registered: May 2008
Senior Member
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);

Previous Topic: Report Optimization
Next Topic: test
Goto Forum:
  


Current Time: Thu Mar 28 09:05:12 CDT 2024