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
Solved

ORACLE.UTL_FILE problem

Posted on 2007-12-06
7
818 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 48

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
exp/imp 25 84
oracle differnce between two timestamps 5 31
return value in based on value passed 6 26
Help with Oracle IF statment 5 22
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

790 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