Hi,

Please help!
I need to write a stored procedure that will replace a word in a number of files in a directory.

I am new to PL/SQL and will really appreciate the help. I have just learned about UTL_FILE.FOPEN thing and is not able to write it properly.

Thanks in advance for your help.

Regards,
Arun

Hi arun1581,

First of all: using PL/SQL for manipulating texts in an plain text-file is not the tool I would use. Much better (and easier to handle) form my expierinece is unsing Perl.

But if you prefere to use PL/SQL you must first make sure sure, that your have set the init.ora-parameter 'utl_file_dir' to a directory, which you can use. This is not dynamic, so you have to reboot the Instance after changing it.

furthermore read the documtenation of how to use the built-in package utl_file: http://tahiti.oracle.com/pls/db901/d...?section=33316

basically you need to take care for:

1. havind a utl_file_dir defined and acces-rights to it
2. a file-handle
3. the open/close functions of utl_file package
4. the read/write operations from that package

hope it helps LaoDe

Hi,

Example :
------------

set echo on
!mkdir /tmp/public_access

connect sys/change_on_install as sysdba;
drop user tcopy01 cascade;
grant connect, resource to tcopy01 identified by tcopy01;
grant select_catalog_role to tcopy01;

create or replace directory public_access as '/tmp/public_access';
grant read on directory public_access to public;

connect tcopy01/tcopy01
create table tcopy01_out (line varchar2(500), i number);
create procedure tcopy01_p as errbuf varchar2(50);
dir varchar2(512) := 'PUBLIC_ACCESS';
f1 utl_file.file_type;
type t_files is table of utl_file.file_type index by binary_integer;
files t_files;
i number := 0;
ok boolean := TRUE;
pos number;
len number;
blk number;

procedure insertoutput (line varchar2) is
begin
insert into tcopy01_out values (line, i);
i := i+1;
end insertoutput;

begin
f1 := utl_file.fopen('PUBLIC_ACCESS', 'tcopy01.dat', 'w'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 1.'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 2.'); utl_file.put_line(f1, 'Copy tcopy01.dat to tcopy01c.dat, line 3.'); utl_file.fclose(f1);