Change strings in Oracle

PL/SQL with the utl_file package allows us to manipulate strings in Oracle. One may read a string from a file, modify this and subsequently write it to another file. This opens nice possibilities.

Let me show some code how to do this:

DECLARE
v_file_ptr utl_file.file_type;
v_file_handler utl_file.file_type;
v_line varchar2(2000);
BEGIN
v_file_ptr := utl_file.fopen('EXTERN','namen.txt','r');
v_file_handler:= utl_file.fopen('EXTERN','uitvoer.txt','w');
LOOP 
BEGIN
utl_file.get_line(v_file_ptr,v_line);
utl_file.put_line(v_file_handler,'uitvoer >> ' || v_line);
dbms_output.put_line(v_line);
EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; 
END ;
END LOOP;
utl_file.fclose(v_file_ptr);
utl_file.fclose(v_file_handler);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(sqlcode||sqlerrm);
END;
/

First, we open two file handlers: one to file from which we read and another to which some will be written.

Subsequently, a loop starts that reads a file, line by line. Each line is manipulated and subsequently written to another file.

Finally the file handlers are closed.

This technique can also be used to create a simple HTML report:

The actual query is “select first_name, last_name from employees”. The results are outputted is a for i loop. The results are written to file with HTML mark ups.

For more information, see https://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS70913