Hi all,
I am trying to write multiple files to the utl_file_dir which is set
to /tmp on my server. I am trying to generate multiple files - one
file per customer and there are 1000 customers. One of the customers
is A&P Stores. My code fails because if the ampersand. Is there a
solution to this? In SQL*Plus I could say "set define off" but in
PL/QSL ....Here is my code
set pages 10000;
set lines 600;
-- The following set command will allow to enter AMPERSAND (&)
set define off
set serveroutput on size 1000000;
DECLARE
l_file UTL_FILE.file_type;
l_last_filename varchar2(255) := '0000000';
l_cust varchar2(35) := '00000_Q.XLS';
l_custid varchar2(6) :=' ';
l_custnm varchar2(35) := ' ';
l_custid_prev varchar2(6) := ' ';
l_custid_curr varchar2(6) := ' ';
Begin
FOR header_rec in (select distinct to_char(run_dt,'YYYY') h1,
QTR_DS h2,
sysdate h3
from cso.QTR_CALD_LKP
where run_dt= (select min(run_dt) from cso.qtr_cald_lkp where
proc_ind='N'))
LOOP
FOR cust_rec in
( select distinct cust_corp_nm,cust_corp_id
from cso.cust_corp_ppl_vw)
LOOP
l_cust:=cust_rec.cust_corp_nm||' Q.XLS';
l_file := UTL_FILE.FOPEN('/tmp',l_cust,'w',32767);
IF l_cust <> l_last_filename THEN
UTL_FILE.PUT_LINE(l_file,'Customer Scorecard ');
UTL_FILE.PUT_LINE(l_file,'|');
UTL_FILE.PUT_LINE(l_file,'VOLUME (Quota and Forecast)');
UTL_FILE.PUT_LINE(l_file,'|'||header_rec.h2);
UTL_FILE.PUT_LINE(l_file,'PROMOTIONAL EXECUTION');
END IF;
FOR promo_rec in
( select
c.brd_alt_ds c79,
to_char(c.event_start_dt,'yyyy-mm-dd') c80,
to_char(c.price_amt,'999.99') c81
from cso.PROMO_QTRLY_TMP c
where c.cust_corp_nm =cust_rec.CUST_corp_nm)
LOOP
UTL_FILE.PUT_LINE(l_file, '|'||nvl(promo_rec.c79,0) ||'|');
END LOOP;
IF (UTL_FILE.IS_OPEN(l_file)) THEN
UTL_FILE.FCLOSE(l_file);
END IF;
End Loop;
End Loop;
EXCEPTION
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_path');
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_mode');
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_filehandle');
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.invalid_operation');
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.read_error');
WHEN utl_file.write_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.write_error');
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.internal_error');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'utl_file.other_error');
END;
/
Thanks in advance
Received on Wed Jun 11 2003 - 16:01:35 CDT
