RE: Help with UTL_FILE

My recommendation, when working, please use the standard formatting.
I've taken the time to adjust your layout.  This type of formatting
might take you an extra minute total, as you type, but will save you
hours of debugging.  

Now, I haven't done oracle in a couple years, but it appears you've
qualified your cursor variable with ( I ) and it should be ( ded  )?

set pages 10000 lines 600
SET SERVEROUTPUT ON SIZE 1000000;
declare l_last_filename varchar2(255) 	:= '00000';
	l_fso varchar2(25) 		:= '00000.csv';
	l_file UTL_FILE.FILE_TYPE;
	cursor ded is
		select	nvl(fsu.field_sales_org_id,'9999') 	c1
		, 	nvl(fsu.fs_organization_name,'910000 OPEN SALES
UNIT') c2
		, 	ded.fi_number 				c3
		,	nvl(ded.fi_name
		,	'UNIDENTIFIED') 			c4
		,	ded.fi_city 				c5
		,	ded.fi_state 				c6
		,	ded.DEDUCTION_LST_UPD_DT 		c28
		from	cso.field_sales_unit@cso_prod2 	fsu
		,	spot.deductions 		ded
		,	spot.fi_ppl_resolved 		fpr
		,	spot.security_ppl 		ppl
		where	fsu.field_sales_org_id(+) 	=
ppl.field_sales_org_id 
		and 	ded.fi_number 			=
ltrim(fpr.fi_number(+),'0') 
		and 	fpr.ppl_number 			=
ppl.sap_plannable_account_id(+) 
		order by fsu.field_sales_org_id
		, 	ded.fi_number
		, 	fpr.ppl_number
		, 	ded.deduction_cd; 

BEGIN 
	UTL_FILE.PUT_LINE(l_file,i.c28); 
	UTL_FILE.PUT_LINE(l_file,'Field Sales Org ID|Sales
Office|Customer Number|Customer Name|City|State');
	l_file 		:=
UTL_FILE.FOPEN('/spot/prod/app/process',l_fso,'w',32767);
	for i in ded loop
		l_fso := i.c1 || '.csv';
		if l_fso <> l_last_filename
		then
			if (UTL_FILE.IS_OPEN(l_file))
			then
				UTL_FILE.FCLOSE(l_file);
			end if;
			l_last_filename := l_fso;
		end if;
		UTL_FILE.PUT_LINE(l_file,i.c1||'|'|| i.c2
||'|'||i.c3||'|'||i.c4||'|'||i.c5 ||'|'||i.c6|| '|');
	end loop;

	if (UTL_FILE.IS_OPEN(l_file))
	then
		UTL_FILE.FCLOSE(l_file);
	end if;
end;

---
Change your mail options at http://p2p.wrox.com/manager.asp or 
to unsubscribe send a blank email to [email protected].