Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORACLE.UTL_FILE problem

Posted on 2007-12-06
7
Medium Priority
?
828 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

715 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