Hi Techguys,

Can you post your current code, so we can take a look and suggest changes ?

Thanks,

Hello Carlosdl,
Thank you for responding to my question. Here below shown the code.
To clarify my question let me add some words. I am trying to read from two files and write onto the output file. To test how it works I chose to display on the screen using DBMS_OUTPUT.PUT_LINE program.
This program reads only one record from v4_input_file in the outer loop. It is not coming back to read the rest of the records after going through all lines inside v3_input_file in the inner loop.
What is the thing that I missing? why the outer loop reads only one line in the v4_input_file?

Thank you very much for your help.
set serveroutput on
declare

v3_input_file utl_file.file_type;
v4_input_file utl_file.file_type;
v4_output_file utl_file.file_type;

v3_input_buffer archaizer(4000);
v4_input_buffer varchar2(4000);
v4_output_buffer varchar2(4000);
v3_line_counter pls_integer:=1;
v4_line_counter pls_integer:=1;

Begin

v3_input_file := utl_file.fopen (‘ORALOAD’,'overview.html’, ‘R’,32767);
v4_input_file := utl_file.fopen (‘ORALOAD’,'basic_template.html’, ‘R’);
v4_output_file := utl_file.fopen(‘ORALOAD’, ‘outputfile.html’, ‘W’);

if (utl_file.is_open(v4_input_file)) then –template file

LOOP –outer loop
utl_file.get_line(v4_input_file,v4_input_buffer);

if (utl_file.is_open(v3_input_file)) then –input file
BEGIN
WHILE NOT end_of_file LOOP –inner loop
get_nextline (v3_input_file, tab_in(v3_line_counter), end_of_file);

— utl_file.put_line(v4_output_file,tab_in(v3_line_counter));
dbms_output.put_line(‘v4_line_counter :’||v4_line_counter);

END LOOP ;
EXCEPTION
WHEN no_data_found THEN
end_of_file := FALSE;

END;
end if;
v4_line_counter := v4_line_counter + 1;
END LOOP;
end if;

utl_file.fclose(v3_input_file);
utl_file.fclose(v4_output_file);

End;

Hi Techguys,

When you reach the end of v3_input_file, the reading pointer does not automatically move back to the beginning of the file.

You might want to open and close v3_input_file on each iteration of the outer loop.

Feel free to add any additional information you consider relevant.

Hi Carlosdl,

You said “When you reach the end of v3_input_file, the reading pointer does not automatically move back to the beginning of the file”. Before the pointer goes back to the beginning of the v3_input_file , it has to come out of the its loop which is an inner loop and go to the outer loop to read the second line from v4_input_file to the buffer. But it does not do that.

Here are how the execution takes place as I understand it:
1. First the pointer enters the outer loop and reads one line from the file.
2. After reading the first line in the outer loop (v4_input_file)the pointer enters the inner loop.
3. The pointer reads all lines from v3_input_file and exits its loop.

After going through the above steps the pointer comes out of the inner loop as well as the outer loop which must not be… at least according to my understanding.
I expect the pointer has to go back to the v4_input_file inside the outer loop and read the remaining lines. Unfortunately it does not do that. I suspect I am missing something. I do not know what I am missing. This works fine with text files but not I guess with files type utl_file.
Thank you for looking into theis
.

The outer loop at the beginning of the execution reads one line from the v4_input_file and the pointer goes to the inner It Here is my problem… the outer loop reads only one time…at the initial execution and the pointer

I would have to test the procedure to be sure, but I think it is successfully reading all lines from v4_input_file but you don’t see any output because after reading the whole v3_input_file the first time, the line where you call dbms_output.put_line is never executed again, as every execution of get_nextline (v3_input_file, tab_in(v3_line_counter), end_of_file) produces an error and makes the execution go to the exception block.

I would try adding some more debug messages.

Additionally, I don’t see where you manage the no_data_found exception for the outer loop, which is needed to exit the loop when the end of 4_input_file is reached.

Also, if you are getting some error message please let us know, and it would be nice to take a look at the code of your get_nextline procedure, so we don’t have to assume what it does (if it handles the no_data_found exception internally, then part of my previous post becomes invalid).

Hi Carlosdl,

The following is a slightly improved code. I have added the exception block for the outer block. The error message I am getting now is:
“29283. 00000 – “invalid file operation”
*Cause: An attempt was made to read from a file or directory that does
not exist, or file or directory access was denied by the
operating system.
*Action: Verify file and directory access privileges on the file system,
and if reading, verify that the file exists.
invalid_operation
” which is captured by the outer exception block. All files and directory exist. I do not know what else this error message indicates.

set serveroutput on
declare

v3_input_file utl_file.file_type;
v3_output_file utl_file.file_type;
v4_input_file utl_file.file_type;
v4_output_file utl_file.file_type;

v3_input_buffer varchar2(32767);
v4_input_buffer varchar2(32767);

end_of_file BOOLEAN := FALSE;
myEOF char(1);
BEGIN

v3_input_file := utl_file.fopen (‘ORALOAD’,'input_file.html’, ‘R’,32767);
v3_output_file := utl_file.fopen (‘ORALOAD’,'v3_output.html’, ‘W’);– open in read mode
v4_input_file := utl_file.fopen (‘ORALOAD’,'template.html’, ‘R’, 32767);
v4_output_file := utl_file.fopen (‘ORALOAD’,'v4_output.html’, ‘W’);

if (utl_file.is_open(v4_input_file)) then
v4_line_counter := 1;
myEOF := ‘N’;

WHILE myEOF = ‘N’ loop
end_of_file := FALSE;
utl_file.get_line(v4_input_file,v4_input_buffer);

— WHILE NOT end_of_file
BEGIN
LOOP

utl_file.get_line(v3_input_file,v3_input_buffer);
–utl_file.put_line(v4_output_file,v3_input_buffer);
dbms_output.put_line(‘v4_line_counter :’||v4_line_counter||’ : ‘||v4_input_buffer);
END LOOP ;
EXCEPTION
WHEN no_data_found THEN
end_of_file := TRUE;
EXIT;
–dbms_output.put_line(‘Reached end-of-file.’);
END;

v4_line_counter := v4_line_counter + 1;

END LOOP;
end if;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE (‘invalid_path’); RAISE;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE (‘invalid_mode’); RAISE;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE (‘invalid_filehandle in outer loop’); RAISE;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE (‘invalid_operation’); RAISE;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE (‘read_error’); RAISE;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE (‘write_error’); RAISE;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE (‘internal_error’); RAISE;
when NO_DATA_FOUND then
myEOF := ‘Y’;
utl_file.fclose(v4_input_file);
WHEN OTHERS THEN
raise_application_error(-20001,’An error was encountered – ‘||SQLCODE||’ -ERROR- ‘||SQLERRM);
END ; –MULTIPLE_CURSORS_PROC;

Carlosdl,

You said on Nov. 2 “When you reach the end of v3_input_file, the reading pointer does not automatically move back to the beginning of the file.
You might want to open and close v3_input_file on each iteration of the outer loop.”

It was a perfect solution for the problem I had. Finally I did the way you suggested and works fine now. Thank you very much. You are great!

You are welcome, Techguys.
I’m glad it worked.