Solved

ORACLE.UTL_FILE problem

Posted on 2007-12-06
7
808 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 100 total points
Comment Utility
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.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now