Home » SQL & PL/SQL » SQL & PL/SQL » Not able to write the data into output file (Oracle Apps)
Not able to write the data into output file [message #320102] Wed, 14 May 2008 01:50 Go to next message
sujithrt
Messages: 5
Registered: May 2008
Junior Member
Am using UTL_FILE.PUT_LINE for writing the data into the file....but my o/p file is showing zero bytes.My input file size is 293,250,833bytes.

If am using input file of 5,92,334 bytes it is writing the data into the o/p file...

So can anybody tel wat is the problem?
Re: Not able to write the data into output file [message #320104 is a reply to message #320102] Wed, 14 May 2008 02:00 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you are probably doing something wrong or don't have required privileges to write into a directory.

Regarding information you provided, it is difficult to guess what might be wrong. You didn't provide code you use, we don't know error code (if any) - basically, information you posted is far from enough.
Re: Not able to write the data into output file [message #320113 is a reply to message #320102] Wed, 14 May 2008 02:18 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Your description looks like you did not CLOSE the file after writing; if you do not want to close it, you shall make the changes visible by issuing FLUSH.
Re: Not able to write the data into output file [message #320126 is a reply to message #320113] Wed, 14 May 2008 02:49 Go to previous messageGo to next message
sujithrt
Messages: 5
Registered: May 2008
Junior Member
I have given the fclose also....It is generating o/p for all other input file of smaller size.my input file size is 293,250,345.Am put the code below..

BEGIN
l_filedirout := v_output_file_path;
/******* open the output file *********/
l_outfilehandle := UTL_FILE.FOPEN (
l_filedirout
,poutfile
,'W'
);

BEGIN
/****** getting data from the edi trans table ********/
FOR r_write_data_rec IN c_write_data_cur LOOP
l_outline :=
SUBSTR (
r_write_data_rec.interface_record
,1
,1024
);
/******* writing each line to the output file *********/
-- UTL_FILE.PUT_LINE (l_outfilehandle, l_outline);
UTL_FILE.FCLOSE (l_outfilehandle);
Re: Not able to write the data into output file [message #320157 is a reply to message #320102] Wed, 14 May 2008 03:35 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
In addition to Littlefoot: You shall provide at least syntactically correct code. If it is too large, extract the problem parts and create a testcase, which is executable without new errors and fully demonstrates the problem you are facing. Showing its execution in SQL*Plus within the clear description of the problem would be nice.
Another guess: do you have enough disk space on the disk you are writing?
By the way, what shall the commenting of PUT_LINE in that mess demonstrate?
Re: Not able to write the data into output file [message #320169 is a reply to message #320157] Wed, 14 May 2008 04:17 Go to previous messageGo to next message
sujithrt
Messages: 5
Registered: May 2008
Junior Member
The i had given below is of the same order....The problem here is that o/p file is not generating for my i/p file of the size mention below..There is no comment for the put_line...
I just wanted to know whether writing the data into o/p file by using put_line have any restriction or not...Like it will write only so much size of data or not....Because my code is working properly for the input file of size 5,393,358 bytes
Re: Not able to write the data into output file [message #320172 is a reply to message #320169] Wed, 14 May 2008 04:27 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The UTL_FILE.FOPEN command specifies a maximum linesize, but if you wrote more than that many bytes at once then you'd get an exception.

As you haven't mentioned any exceptions, I'll assume that this isn't your problem.

Judging by that sample code you posted, you seem to be closing the file after the first write, but that's obviously silly.

Any chance of you posting the actual code, including the exception block, and letting us know if you get any errors when you run the code?
Re: Not able to write the data into output file [message #320179 is a reply to message #320172] Wed, 14 May 2008 04:36 Go to previous messageGo to next message
sujithrt
Messages: 5
Registered: May 2008
Junior Member
Am not getting any exception....

BEGIN
l_filedirout := v_output_file_path;
/******* open the output file *********/
l_outfilehandle := UTL_FILE.FOPEN (
l_filedirout
,poutfile
,'W'
);

BEGIN
/****** getting data from the edi trans table ********/
FOR r_write_data_rec IN c_write_data_cur LOOP
l_outline :=
SUBSTR (
r_write_data_rec.interface_record
,1
,1024
); UTL_FILE.PUT_LINE(l_outfilehandle,l_outline);
END LOOP;


EXCEPTION
WHEN OTHERS THEN
Fnd_File.put_line
(Fnd_File.LOG,'error in opening the cursor c_write_data_cur'
|| ' '
|| SQLERRM);
g_prog_err := 'Y';
END;

UTL_FILE.FCLOSE (l_outfilehandle);
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE (l_outfilehandle);
l_error_message :=
'Invalid Operation with the file'
|| ' '
|| poutfile
|| 'error is'
|| ' '
|| SQLERRM;
Fnd_File.put_line (Fnd_File.LOG, l_error_message);
g_prog_err := 'Y';
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE (l_outfilehandle);
l_error_message :=
'Invalid Filehandle with the file'
|| ' '
|| poutfile
|| 'error is'
|| ' '
|| SQLERRM;
Fnd_File.put_line (Fnd_File.LOG, l_error_message);
g_prog_err := 'Y';
WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE (l_outfilehandle);
l_error_message :=
'Write Error with the file'
|| ' '
|| poutfile
|| 'error is'
|| ' '
|| SQLERRM;
Fnd_File.put_line (Fnd_File.LOG, l_error_message);
g_prog_err := 'Y';
WHEN OTHERS THEN
UTL_FILE.FCLOSE (l_outfilehandle);
l_error_message :=
'Others errors in the file'
|| ' '
|| poutfile
|| 'error is'
|| ' '
|| SQLERRM;
Fnd_File.put_line (Fnd_File.LOG, l_error_message);
g_prog_err := 'Y';
END;
Re: Not able to write the data into output file [message #320185 is a reply to message #320179] Wed, 14 May 2008 04:44 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:
Am not getting any exception....

No wonder you are not getting any exception because of this condition
Quote:

WHEN OTHERS THEN


How about commenting the when others block and re-run it.

Regards

Raj

[Updated on: Wed, 14 May 2008 04:45]

Report message to a moderator

Re: Not able to write the data into output file [message #320189 is a reply to message #320185] Wed, 14 May 2008 04:53 Go to previous messageGo to next message
sujithrt
Messages: 5
Registered: May 2008
Junior Member
there are two WHEN OTHERS THEN...which one you are talking about...Then wat else i have to give in exception after commenting WHEN OTHERS THEN.
Re: Not able to write the data into output file [message #320193 is a reply to message #320189] Wed, 14 May 2008 05:04 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Don't do anything allow it to fail.

Regards

Raj
Re: Not able to write the data into output file [message #320208 is a reply to message #320193] Wed, 14 May 2008 05:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, failing that, show us what has been written to whatever log file FND_FILE.PUT_LINE writes to.

It's (presumably) doing some sort of exception logging - show us what it's got.
Re: Not able to write the data into output file [message #320302 is a reply to message #320102] Wed, 14 May 2008 11:26 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
Format and use code tags!

BEGIN
     l_filedirout := v_output_file_path;
     /******* open the output file *********/
     l_outfilehandle := utl_file.fopen(l_filedirout, poutfile, 'W');

     BEGIN
          /****** getting data from the edi trans table ********/
          FOR r_write_data_rec IN c_write_data_cur LOOP
               l_outline := substr(r_write_data_rec.interface_record, 1, 1024);
               utl_file.put_line(l_outfilehandle, l_outline);
          END LOOP;
     
     EXCEPTION
          WHEN OTHERS THEN
               fnd_file.put_line(fnd_file.log, 'error in opening the cursor c_write_data_cur' || ' ' || SQLERRM);
               g_prog_err := 'Y';
     END;

     utl_file.fclose(l_outfilehandle);
EXCEPTION
     WHEN utl_file.invalid_operation THEN
          utl_file.fclose(l_outfilehandle);
          l_error_message := 'Invalid Operation with the file' || ' ' || poutfile || 'error is' || ' ' || SQLERRM;
          fnd_file.put_line(fnd_file.log, l_error_message);
          g_prog_err := 'Y';
     WHEN utl_file.invalid_filehandle THEN
          utl_file.fclose(l_outfilehandle);
          l_error_message := 'Invalid Filehandle with the file' || ' ' || poutfile || 'error is' || ' ' || SQLERRM;
          fnd_file.put_line(fnd_file.log, l_error_message);
          g_prog_err := 'Y';
     WHEN utl_file.write_error THEN
          utl_file.fclose(l_outfilehandle);
          l_error_message := 'Write Error with the file' || ' ' || poutfile || 'error is' || ' ' || SQLERRM;
          fnd_file.put_line(fnd_file.log, l_error_message);
          g_prog_err := 'Y';
     WHEN OTHERS THEN
          utl_file.fclose(l_outfilehandle);
          l_error_message := 'Others errors in the file' || ' ' || poutfile || 'error is' || ' ' || SQLERRM;
          fnd_file.put_line(fnd_file.log, l_error_message);
          g_prog_err := 'Y';
END;
Re: Not able to write the data into output file [message #320303 is a reply to message #320189] Wed, 14 May 2008 11:30 Go to previous messageGo to next message
drewsmith70
Messages: 22
Registered: April 2008
Location: New Hampshire
Junior Member
sujithrt wrote on Wed, 14 May 2008 05:53
there are two WHEN OTHERS THEN...which one you are talking about...


Add RAISE after any WHEN OTHERS action (you should always do this):

EXCEPTION
     WHEN OTHERS THEN
          <Do something here>;
          RAISE;
END;



Re: Not able to write the data into output file [message #320319 is a reply to message #320102] Wed, 14 May 2008 12:26 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Your solution awaits you.

Simply do as you have been instructed.

REMOVE ALL EXCEPTION HANDLERS AND RE-RUN THE JOB.

The error will pop out and tell you why. The issue is most likely that you are getting and error before you ever write to the file. FOPEN for example would raise an exception if you do not have privileges to open a file in the requested directory.

My guess is you are getting an error before you write to your rile, but you can't see the error because the exception handlers are hiding it.

This is ofcourse what everbody else has been guesing as well. Several people have already told you how to find the issue. The problem is not that there is some obscure bug in ORACLE, the problem is with your exception handlers and error trapping.

So...

REMOVE ALL EXCEPTION HANDLERS AND RE-RUN THE JOB.

Also, DO NOT POST CODE TO ORAFAQ FORUMS THAT DOES NOT COMPILE EVER AGAIN. We get very annoyed (as you can see from other posts), when people post non-working code, or code that is not the real code with the problem.

I am not trying to be mean here, but it is not unreasonable for us to expect you to think a little before you ask for our time, and to provide real components for us to examine.

Good luck, I am confident that once you remove your exception handlers, the error that is killing your job will pop out and you will know what to do.

Quote:
This is a good lesson for all newbies getting into PLSQL development. Exception Handling is important, but bad handlers mask errors and waste time. More specifically "WHEN OTHERS" causes the most grief until you get good at it so be careful.


Kevin

[Updated on: Wed, 14 May 2008 12:26]

Report message to a moderator

Previous Topic: utl_smtp and national charset
Next Topic: Concatenating the results
Goto Forum:
  


Current Time: Sun Dec 04 22:45:15 CST 2016

Total time taken to generate the page: 0.06000 seconds