Re: TEXT_IO - UTL_FILE

From: QuestionExchange <USENET_at_questionexchange.com>
Date: 26 Oct 1999 1:51:41 GMT
Message-ID: <2093qx@questionexchange.com>

> I have a form with a pl_sql procedure that writes a file
using the TEXT_IO
> package.
> The path of the file can be changed by the user before she/he
push the
> button to create the file.
>
> Now, we want to execute it from a stored procedure. So, I
can not use
> TEXT_IO to
> write the file and the UTL_FILE package do not allows to
change the path
> according
> user needs. And we do not want to specify UTL_FILE_DIR=*.
>
> Some body knows how to write a file from a stored procedure
having the
> possibility to specify the path each time it is called?
>
> Oracle7 Server Rel. 7.3.3.6.0
>
> Thanks for any idea.
>
>
>
>
>

here is a snippit of code that uses the utl_file io package to output a file to a location based on information in a table (the put_line statement can take a variable for the file name and path thus allowing you to place the file anywhere you wish) select ThePath into vThePath from PathRef where PathName='Emerald Online';
select to_char(sysdate,'RRDDDHH24MI') into vFeedDate from dual;
if (vofg_filename='') or (vofg_filename is null) then vfiletype:='';
else
vfiletype:=upper(substr(vofg_filename,1,3))||'_'; end if;
for c_op in cOnlineProviders loop
nTotalRows:=0;

vFeedFile:='fdc_'||c_op.manufacturer||'_'||vfiletype||vFeedDate ||'.dat';
fFeed := utl_file.fopen (vThePath, vFeedFile, 'w'); if utl_file.is_open (fFeed) = FALSE then raise xOpenError;
end if;
for c_oe in cOnlineEquipment (c_op.manufacturer) loop if upper(c_oe.accountstatus)='APPROVED' then utl_file.put_line (fFeed,lpad(c_oe.store_id,8,'0')||','||

            lpad(c_oe.clientnumber,12,'0')||','||
            lpad(c_oe.nashvilleclientnumber,11,'0')||','||
            lpad(c_oe.terminalnumber,16,'0'));

else
utl_file.put_line (fFeed, lpad(c_oe.store_id,8,'0')||','||

            lpad(c_oe.clientnumber,12,'0')||','||
            c_oe.accountstatus);

end if;
update ctms.online_equipment set status='Y', file_identifier=vFeedFile

    where onlineequipseqid=c_oe.onlineequipseqid;    nTotalRows:=cOnlineEquipment%rowcount;   end loop;
  utl_file.put_line (fFeed, '# Total rows in file:'||nTotalRows||' File generation date:'||vFeedDate);   if utl_file.is_open (fFeed) then
   utl_file.fclose (fFeed);
  else
   raise xCloseError;
  end if;
  updateonlineequipment(vFeedFile);
end loop;
if you need any further assistance please let me know. andre azaroff
aazaroff_at_redrose.net

--
This answer is courtesy of QuestionExchange.com http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=6391&cus_id=USENET&qtn_id=4521 Received on Mon Oct 25 1999 - 20:51:41 CDT

Re: TEXT_IO - UTL_FILE - ??