Home » SQL & PL/SQL » SQL & PL/SQL » Writing file from random position using UTL_FILE (11gR1, Server 2003)
Writing file from random position using UTL_FILE [message #428942] Sat, 31 October 2009 01:15 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi everybody,

I have some problem regarding writing file from random position in it using UTL_FILE package.

Let's say I have file TEMP.TXT containing 5000 chars. in it.
Now I want to add some content from char 2000.
Is it possible using UTL_FILE package?
If the content from char 2001 gets over written, then also there is no problem.
If possible, please help me by providing example.

regards,
Delna
Re: Writing file from random position using UTL_FILE [message #428953 is a reply to message #428942] Sat, 31 October 2009 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use UTL_FILE.FSEEK procedure to point to the place you want to write.

Regards
Michel
Re: Writing file from random position using UTL_FILE [message #428959 is a reply to message #428953] Sat, 31 October 2009 02:48 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Michel sir,
As suggested by you, I was already using FSEEK method.

Please see the calling code
...
    V_HANDLE := UTL_FILE.FOPEN(V_DIR_NAME, V_FILE_NAME, 'w', 32000);
    
    IF (UTL_FILE.IS_OPEN(V_HANDLE)) THEN
        
        SEO_AUTO_EXCHANGE.APPEND_LINK(V_HANDLE, 2000, 'tHIS IS THE TITLE','aND THIS IS THE DESCRIPTION');
        
        UTL_FILE.FCLOSE(V_HANDLE);
    END IF;
...


And following is the called module.
PROCEDURE APPEND_LINK(P_HANDLE_IO IN OUT UTL_FILE.FILE_TYPE, 
P_POSITION_I IN INTEGER, P_TITLE_I IN VARCHAR2, P_DESCRIPTION_I IN VARCHAR2)
    AS
    BEGIN
        UTL_FILE.FSEEK(P_HANDLE_IO, P_POSITION_I, NULL);
        
        UTL_FILE.PUTF(P_HANDLE_IO,'%s\n', P_TITLE_I);
        
        UTL_FILE.PUTF(P_HANDLE_IO, '%s\n', P_DESCRIPTION_I);
        
        UTL_FILE.FFLUSH(P_HANDLE_IO);
    END;


But I am getting
ORA-29284: file read error


on first line of APPEND_LINK procedure, that is where I am using FSEEK method.
Moreover, the file gets blank. All the content is removed from the file.

What can be the problem?

regards,
Delna

[Updated on: Sat, 31 October 2009 03:05] by Moderator

Report message to a moderator

Re: Writing file from random position using UTL_FILE [message #428962 is a reply to message #428959] Sat, 31 October 2009 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a complete test case that shows the error and we can reproduce.
Keep your lines in 80 characters width.

Regards
Michel
Re: Writing file from random position using UTL_FILE [message #428963 is a reply to message #428942] Sat, 31 October 2009 03:28 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
UTL_FILE.FOPEN(V_DIR_NAME, V_FILE_NAME, 'w', 32000);

As OPEN_MODE parameter is assigned 'w', the file is overwritten. Using 'a' (append) could at least not drop the file content.
As you use ABSOLUTE_OFFSET in FSEEK function, it could work as required if PUTF is able to write there (which I doubt). You may try it at least.
Re: Writing file from random position using UTL_FILE [message #428965 is a reply to message #428962] Sat, 31 October 2009 03:39 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Michel sir,

Whatever I have provided is almost complete test case.
What extra you required to do is,

Create 1 text file,
CREATE DIRECTORY,
GRANT READ, WRTIE on that directory,
CREATE PROCEDURE APPEND_LINK (I have provided code),
Write calling module with extra declaration part, and that only I haven't provided,
And run that code.

That's all.

regards,
Delna
Re: Writing file from random position using UTL_FILE [message #428966 is a reply to message #428965] Sat, 31 October 2009 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Whatever I have provided is almost complete test case.
What extra you required to do is,

Put a test case that we just have TO COPY AND PASTE.
Why do you think we want to make the effort to help you if you don't want to make the effort to help us to help you. The time I spend to make the test case is the time I don't spend to search a solution to help you.
You post here for so long that you should know that.

Regards
Michel
Re: Writing file from random position using UTL_FILE [message #428967 is a reply to message #428963] Sat, 31 October 2009 03:52 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
flyboy sir,

If I write file in APPEND mode, without using FSEEK, it works fine as expected.

And if I use PUT or PUT_LINE instead of PUTF method without changing anything else, same error occurs.

regards,
Delna
Re: Writing file from random position using UTL_FILE [message #428970 is a reply to message #428966] Sat, 31 October 2009 04:07 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I created one file temp.txt in 'D:\test'.

Content of temp.txt is
Quote:
This is line 1...
This is line 2...
This is line 3...
This is line 4...
This is line 5...
This is line 6...
This is line 7...
This is line 8...
This is line 9...
This is line 10...


SQL>create directory dir_temp as 'D:\test';

Directory created.

SQL>create or replace procedure APPEND_LINK
  2  (P_HANDLE_IO IN OUT UTL_FILE.FILE_TYPE, P_POSITION_I IN INTEGER,
  3  P_TITLE_I IN VARCHAR2, P_DESCRIPTION_I IN VARCHAR2)
  4  AS
  5      BEGIN
  6          UTL_FILE.FSEEK(P_HANDLE_IO, P_POSITION_I, NULL);
  7          
  8          UTL_FILE.PUT_LINE(P_HANDLE_IO, P_TITLE_I);
  9          
 10          UTL_FILE.PUT_LINE(P_HANDLE_IO, P_DESCRIPTION_I);
 11          
 12          UTL_FILE.FFLUSH(P_HANDLE_IO);
 13      END;
 14  /

Procedure created.

SQL>R
  1  declare
  2      v_handle utl_file.file_type;
  3  begin
  4      V_HANDLE := UTL_FILE.FOPEN('DIR_TEMP', 'TEMP.TXT', 'w', 32000);
  5      IF (UTL_FILE.IS_OPEN(V_HANDLE)) THEN
  6          APPEND_LINK(V_HANDLE, 20, 'tHIS IS THE TITLE','aND THIS IS THE DESCRIPTION');
  7          UTL_FILE.FCLOSE(V_HANDLE);
  8      END IF;
  9* end;
declare
*
ERROR at line 1:
ORA-29284: file read error
ORA-06512: at "SYS.UTL_FILE", line 219
ORA-06512: at "SYS.UTL_FILE", line 1145
ORA-06512: at "XLNTSEO.APPEND_LINK", line 6
ORA-06512: at line 6


And now see the content of file. It is blank...

regards,
Delna
Re: Writing file from random position using UTL_FILE [message #428971 is a reply to message #428967] Sat, 31 October 2009 04:12 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
delna.sexy wrote on Sat, 31 October 2009 09:52
If I write file in APPEND mode, without using FSEEK, it works fine as expected.

Without using FSEEK, you just append content after current file end.
delna.sexy wrote on Sat, 31 October 2009 09:52
And if I use PUT or PUT_LINE instead of PUTF method without changing anything else, same error occurs.

So, PUTF is able to write to the previous file content? I have no idea what exactly you are doing and why/how you introduced PUT and PUT_LINE methods. Anyway, maybe it is their limit. Hard to tell without specifying which method raised that exception. Only FSEEK is supposed to do so.
Re: Writing file from random position using UTL_FILE [message #429113 is a reply to message #428942] Mon, 02 November 2009 03:45 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Does anybody know the solution of this problem?
Or any hint please.......

regards,
Delna
Re: Writing file from random position using UTL_FILE [message #429198 is a reply to message #429113] Mon, 02 November 2009 08:53 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
delna.sexy wrote on Mon, 02 November 2009 10:45
Does anybody know the solution of this problem?

Which problem? As you open the file in 'w' mode, it is overwritten.
Firstly I was thinking that code failed because of invalid offset (ORA-29290). However, the error message is different. Its description taken from http://ora-29284.ora-code.com/ tells this: Quote:
ORA-29284: file read error
Cause: An attempt to read from a file failed.
Action: Verify that the file exists, and that it is accessible, and that it is open in read mode.

Conclusion: FSEEK requires the file to be opened in read mode. For changes, the file must be opened in write/append mode. If it is not possible to do it at the same time (as I guess), you are out of luck with the direct approach. If you insist on using UTL_FILE, open the source file for read and write its content (the first N characters) to the new file. Then, write there anything you want; alternatively append the rest of the source file. Rename any file in any reasonable place where you like it most.
Or, have a look at another (probably non-Oracle) utilities. In Oracle, you might try loading file content into CLOB, change it as you want and save it back. I have never done this, so take it as an alternative idea only.

[Edit: Last paragraph changed]

[Updated on: Mon, 02 November 2009 09:03]

Report message to a moderator

Re: Writing file from random position using UTL_FILE [message #429282 is a reply to message #429198] Tue, 03 November 2009 00:55 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes flyboy sir,

Quote:
Which problem?

Quote:
Moreover, the file gets blank. All the content is removed from the file.


And,
Quote:
As you open the file in 'w' mode, it is overwritten.

Quote:
If the content from char 2001 gets over written, then also there is no problem.


By the way, I have followed alternative approach.

Create one temporary file.
Read from source file.
Write content into temp file.
Add required content to temp file.
Append remaining content from source to temp file.
Move temp file to source file with renaming as same as source file.

But, if it is possible directly to add some content to source file, it would be advisable and will be completed faster.

regards,
Delna
Re: Writing file from random position using UTL_FILE [message #429295 is a reply to message #429282] Tue, 03 November 2009 01:36 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
I thought I was clear enough; but your answers do not indicate that. So I will summarize it again.
UTL_FILE.FILE_OPEN offers two modes for opening files for writing. 'w' mode starts writing from its beginning (= overwrites its content), 'a' mode appends text after current content. There is no option for opening the file somewhere in the middle and start writing there. The file is empty immediately after opening in 'w' mode (as you discovered) - why should the first call of UTL_FILE.PUT* treat it?
Quote:
But, if it is possible directly to add some content to source file, it would be advisable and will be completed faster.
I am afraid, that the answer is no for any approach using UTL_FILE package (if it was not enhanced in 11g, but I doubt it).
Previous Topic: ORA-30926: unable to get a stable set of rows in the source tables
Next Topic: Interview questions
Goto Forum:
  


Current Time: Thu Sep 29 10:49:27 CDT 2016

Total time taken to generate the page: 0.17331 seconds