Re: UTL_FILE and Ampersand (&)

Pawan wrote:
> 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

Once again no OS is/was specified.

At the OS level can you create a file called "A&P Stores"; without using double quote marks or similar special characters or delimiters?
I suspect that your OS will spit an error message if you were to $ touch A&P Stores
So I suspect the problem is with the OS & not Oracle itself. Received on Wed Jun 11 2003 - 20:41:24 CDT