Re: UTL_FILE and Ampersand (&)

Thanks Ana and Anurag. The OS is HP UX 11 and Oracle version is 8.1.7.4. The problem is that UTL_FILE writes to multiple files whose name is dictated by the l_cust variable. After writing about 15 files the code fails as:
SQL> @t
DECLARE
*
ERROR at line 1:
ORA-20001: utl_file.other_error
ORA-06512: at line 307

If I remove the EXCEPTION part of the code (t.sql) then it creates the same 15 files as above and then I get
SQL> @t
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 116

But if I keep the EXCEPTION and just change the l_cust variable assigment from

l_cust:=cust_rec.cust_corp_nm||' Q.XLS';

to

l_cust:=cust_rec.cust_corp_id||' Q.XLS';

all the 1000 files are created fine. Also if I replace the "&" in the name from all the customer_nm (if it has an "&") and keep

l_cust:=cust_rec.cust_corp_nm||' Q.XLS';

then also I get all the 1000 files and no errors. Is there a way around this?

Thanks

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<9TQFa.80185$MJ5.42648_at_fed1read03>...
> 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 Thu Jun 12 2003 - 13:24:25 CDT