Solved

ORACLE.UTL_FILE problem

Posted on 2007-12-06
7
817 Views
Last Modified: 2013-12-19
Hi, I'm facing a strange problem using UTL_FILE Oracle package.
I have a PL/SQL procedure that has to performe a simple query and write the result in a file.
Now, in my enviroment, if I execute the procedure, I wrote a single file with 300.000 records, no problem.
In another enviroment, the procedure has this behaviour:
- it writes the file header;
- it writes an amount of data equals to total_record_number%10.000; as example: if total_record_number = 79.234, the number of record written in the file is 9.234.
I think that it must be a configuration parameter that is set in order to limit max row number in a file, o smt like this...
Any clues?
0
Comment
Question by:hanoirules
7 Comments
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 100 total points
ID: 20418956
Well the problem must be with your code.
Are you opening the file every time in a loop?

The file must be closed at the end also. Make sure that your code takes the following form

.
open file;
loop
 write to file;
end loop;
close file;
.
0
 

Author Comment

by:hanoirules
ID: 20419087
Excuse me, how can it be that the problem is in my procedure, if in my enviroment everithing works fine?
I can write a file with 300.000 records; in the other enviroment the problem that I described is on 79.000 records.. am I missing smt?
Anyway, the operations you listed are those I perfome..
0
 
LVL 47

Accepted Solution

by:
schwertner earned 150 total points
ID: 20419297
You have to add to your procedure an EXCEPTION section that will show you
what happens:

BEGIN
    dbms_output.enable(100000);
.....
  EXCEPTION
  WHEN OTHERS THEN
     dbms_output.put_line('Errors  encountered.');
     dbms_output.put_line(substr(sqlerrm,1,254));
END;
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:hanoirules
ID: 20419389
I already did it.
No exceptions are thrown (and they are correctly catched).
Not to be annoying, but the procedure works.
The problem is to identify if there is a parameter that manages max row number for file, or if it is possible that, if there is not enought space on the disk, Oracle manages files in such a way.
Thanks for your help
0
 
LVL 2

Assisted Solution

by:ysd
ysd earned 150 total points
ID: 20419515
I had similar situation some time ago, where in one machine was working without any problems and on another it was encountering a problem.....A fter I trapped the error with an exception handler, I saw that I was getting buffer ran out, or something like that.  What I did was that every 5000 records I was calling the FFLUSH procedure which is physically writting the data to the file and it clears the buffer, then you continue writting until you finish. Just include this in the loop for a small number of recs first just to see if it solves your problem, and then you can increase the number of recs before you fllush in order to be more efficient.  

I hope it helps
0
 
LVL 2

Expert Comment

by:ysd
ID: 20419516
I had similar situation some time ago, where in one machine was working without any problems and on another it was encountering a problem.....A fter I trapped the error with an exception handler, I saw that I was getting buffer ran out, or something like that.  What I did was that every 5000 records I was calling the FFLUSH procedure which is physically writting the data to the file and it clears the buffer, then you continue writting until you finish. Just include this in the loop for a small number of recs first just to see if it solves your problem, and then you can increase the number of recs before you fllush in order to be more efficient.  

I hope it helps
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
ID: 20420637
If the FFLUSH command doesn't help, can you give us some more information, like:
1. Are both databases the same version of Oracle?  Which version(s)?
2. Are both servers on the same O/S?  Which O/S(es)?
3. Are the disk systems the same (local SCSI, or SAN or NAS, etc.)?
4. Do you use physical directories for UTL_FILE or logical directories?  Are they the same in both systems?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question