Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, bin.

Asked: September 14, 2016 - 8:37 pm UTC

Last updated: October 09, 2016 - 1:21 am UTC

Version: sqldeveloper

Viewed 1000+ times

You Asked

create or replace procedure myfun(schemaname varchar2)
as
Filename VARCHAR2(200) :=&1; --32767
DirectoryName UTL_FILE.FILE_TYPE := &2;
schemaname varcahr2(20) := &3;
BEGIN
DirectoryName := UTL_FILE.FOPEN(DirectoryName,Filename,'R');
Loop
BEGIN
UTL_FILE.GET_LINE(DirectoryName,Filename);
dbms_output.put_line(Filename);
EXCEPTION WHEN No_Data_Found THEN EXIT; END;
end loop;

IF UTL_FILE.IS_OPEN(Filename) THEN
dbms_output.put_line('--File is Open');
CASE n
WHEN (schemaname like 'ABC') Then REPLACE(schemaname, 'ABC', '&4');
WHEN (schemaname like 'XYZ') Then REPLACE(schemaname, 'XYZ', '&5');
else
dbms_output.put_line('No Schema name defined');
end case;

end if;

UTL_FILE.FCLOSE(F1);
END;
/


I want to change the schema name in every file dynamically. So i wanted to create a stored procedure which would read each line of a file and if it see a schemaname 'XYZ' then it should replace it with something else.

I wrote above code for that but i didn't understand how to read each and every line in the file, identify a pattern and replace that pattern.

Any suggestions?

and Chris said...

What exactly are you trying to do? Just read the file contents and replace text for later processing? Or write the changes back to the original file?

If it's the former, just read the file line by line, replacing the contents as needed. For example, if you have the following in test.txt:

chris-blah1
chris-blah2
chris-blah3
connor-blah4


And you want to replace "chris" with CHRIIIIS, read the contents into a variable. Then run a replace on it:

create directory tmp as '/tmp';
declare
  f utl_file.file_type;
  buffer varchar2(100);
begin
  f := utl_file.fopen('TMP', 'test.txt', 'r');
  loop
    begin
      utl_file.get_line(f, buffer, 100);
      buffer := replace (buffer, 'chris', 'CHRIIIIS');
      dbms_output.put_line(buffer);
    exception
      when no_data_found then exit;
    end;
  end loop;
  utl_file.fclose(f);
end;
/

CHRIIIIS-blah1
CHRIIIIS-blah2
CHRIIIIS-blah3
connor-blah4


If you want to update the file itself things are trickier. There isn't an in-place overwrite option in utl_file. Instead, what you could do is:

- Open the file and also create a temporary file
- Read the file line-by-line
- Replace the text as needed, then write this to the temp file
- Once finished, rename the temp file to the original, overwriting the source

declare
  f utl_file.file_type;
  ftmp utl_file.file_type;
  buffer varchar2(100);
begin
  f := utl_file.fopen('TMP', 'test.txt', 'r');
  ftmp := utl_file.fopen('TMP', 'test.txt.tmp', 'w');
  loop
    begin
      utl_file.get_line(f, buffer, 100);
      buffer := replace (buffer, 'chris', 'CHRIIIIS');
      dbms_output.put_line(buffer);
      utl_file.put_line(ftmp, buffer);
    exception
      when no_data_found then exit;
    end;
  end loop;
  utl_file.fclose(f);
  utl_file.fclose(ftmp);
  utl_file.frename('TMP', 'test.txt.tmp', 'TMP', 'test.txt', true);
end;
/

CHRIIIIS-blah1
CHRIIIIS-blah2
CHRIIIIS-blah3
connor-blah4


You should now have a single test.txt file with the contents shown above.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

What about Microsoft WORD or EXCEL documents?

Joe Huang, October 08, 2016 - 2:05 am UTC

Good example!! It works fine at pure text documents.
In some case, there could be WORD/EXCEL documents instead of pure text, and I wonder, can UTL_FILE handle with WORD/EXCEL docs perfectly? I mean, let alone the tabulations or fonts, just *UPDATE* the text in WORD/EXCEL.
Connor McDonald
October 09, 2016 - 1:21 am UTC

Not natively, but the people at AMIS have done cool work in this area

https://technology.amis.nl/tag/as_xlsx/

https://technology.amis.nl/2013/01/19/read-a-excel-xlsx-with-plsql/

Cheers,
Connor

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library