How can I generate an output file from two input files choosing data from each files using utl_file package or any other way?

90 pts.
Tags:
Oracle 10g
Oracle 10g administration
Oracle PL/SQL
PL/SQL
I have two html files one is a template with certain data that must be transferred to the output file and the other file is also an html file which contains the most data that must be transferred to the same out put file. I have tried to write from both files to the output file inside two loops. The outer loop reads from the template file and the inner loop reads from the input file and writes to the output file.  My problem is the inner loop for some reason does not loop through the input file for each iteration of the outer loop.

One file has 235 lines of records and the second file has 148 lines. The total looping that the two loops have to go through must be 235 x 148 which is about 34780 loops.  But mine has only 235 + 148 which is about 383.   

Please, help me out. Thanks

Software/Hardware used:
Oracle 10g

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi Techguys,

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

Thanks,

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Hi Techguys, Can you post your current code, so we can take a look and suggest changes ? Thanks,
    69,175 pointsBadges:
    report
  • Techguys
    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;
    90 pointsBadges:
    report
  • carlosdl
    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.
    69,175 pointsBadges:
    report
  • Techguys
    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
    90 pointsBadges:
    report
  • carlosdl
    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.
    69,175 pointsBadges:
    report
  • carlosdl
    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).
    69,175 pointsBadges:
    report
  • Techguys
    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;
    90 pointsBadges:
    report
  • Techguys
    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!
    90 pointsBadges:
    report
  • carlosdl
    You are welcome, Techguys. I'm glad it worked.
    69,175 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following