Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ORACLE.UTL_FILE problem

Posted on 2007-12-06
7
Medium Priority
?
836 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 300 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 48

Accepted Solution

by:
schwertner earned 450 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 450 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 300 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

971 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