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

x
?
Solved

Problem using utl_file.frename ORA-29292

Posted on 2014-03-27
15
Medium Priority
?
4,496 Views
Last Modified: 2014-04-16
Hi experts, to perform this procedure (see the attached please):
read_demo exec ('test.txt');

Open in new window

I see the following error:
Error at line 1
ORA-29292: file rename operation failed
ORA-06512: at "SYS.UTL_FILE", line 340
ORA-06512: at "SYS.UTL_FILE", line 1186
ORA-06512: at "XAJTDB.READ_DEMO", line 41
ORA-06512: at line 1

Open in new window

Line 41 says:
utl_file.frename ('ORALOAD', 'test.txt', 'CORRECTOS', 'correcto_test.txt', TRUE);

Open in new window

The directories exist:
CREATE OR REPLACE DIRECTORY
CORRECTOS AS
'C: \ temp \ test_utl_file \ CORRECTOS';

GRANT READ, WRITE ON DIRECTORY CORRECTOS TO XAJTDB WITH GRANT OPTION;

CREATE OR REPLACE DIRECTORY
AS ORALOAD
'C: \ temp \ test_utl_file \';

GRANT READ, WRITE ON DIRECTORY ORALOAD TO XAJTDBWITH GRANT OPTION;

Open in new window


I have also tried to change the name in the same directory, with the same error.
They could tell me some diagnosis and solution know?
Thanks and regards
read-demo.sql
0
Comment
Question by:carlino70
[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
  • 8
  • 7
15 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39959878
your ORALOAD create directory is invalid  you have the AS in the wrong place


Also, it's not correct to have all those spaces in your path
CREATE OR REPLACE DIRECTORY
CORRECTOS AS
'C:\temp\test_utl_file\CORRECTOS';


CREATE OR REPLACE DIRECTORY
ORALOAD AS
'C:\temp\test_utl_file\';

Open in new window

0
 

Author Comment

by:carlino70
ID: 39960114
sorry, but when I read the documentation:
http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5007.htm
 I saw:
Example
Creating a Directory: Examples
The following statement creates a directory database object that points to a directory on the server:
CREATE DIRECTORY admin AS 'oracle/admin';
The following statement redefines directory database object bfile_dir to enable access to BFILEs stored in the operating system directory /private1/lob/files:
CREATE OR REPLACE DIRECTORY bfile_dir AS '/private1/LOB/files';

Open in new window


Could you tell me please why the sentence 'AS' is in the wrong place?
The space between words in path definition, was introduced by this format, thats sentences are correct.

The directories are working well with other options, for example:
utl_file.fcopy, utl_file.fclose


Regards.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39960127
You have

CREATE OR REPLACE DIRECTORY
AS ORALOAD

not

CREATE OR REPLACE DIRECTORY
ORALOAD AS


you have the name after the AS
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:carlino70
ID: 39961319
sdstuber, I tried:
CREATE OR REPLACE DIRECTORY
AS TEST 'C:\TEMP';

Open in new window

changing the position of the keyword 'AS', but doesn't work.

I wrote in the original question:
CREATE OR REPLACE DIRECTORY
CORRECTOS AS
'C: \temp\test_utl_file\CORRECTOS\';

CREATE OR REPLACE DIRECTORY
AS ORALOAD
'C: \temp\test_utl_file\';

Open in new window

Again, directories are created fine, and work fine with options like:
utl_file.fcopy, utl_file.fclose

Open in new window

and do not work with these other options:
utl_file.frename, utl_file.fremove

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39961325
why did you try this?

CREATE OR REPLACE DIRECTORY
AS TEST 'C:\TEMP';

Open in new window


that's the error I was telling you to correct
you used the AS in the wrong place.  Put "AS" AFTER the name, not before.

Look more carefully at my examples above, that's what I was trying to show.
You can even see it in your own original code.  Notice how you use AS differently in the two different create statements.


As for your other directories, if you are claiming they work, then
what you are posting and what you are executing must not be the same thing.

There is no way the ORALOAD directory was created successfully using the syntax you have posted

SQL> CREATE OR REPLACE DIRECTORY
  2  AS ORALOAD
  3  'C: \temp\test_utl_file\';
AS ORALOAD
*
ERROR at line 2:
ORA-22929: invalid or missing directory name

Open in new window


but, if you fix the AS like I've shown in posts above

SQL> CREATE OR REPLACE DIRECTORY
  2  ORALOAD AS
  3  'C: \temp\test_utl_file';

Directory created.

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39961396
Note, I created the directory with the space as shown in your code but with the AS corrected.

That directory is not usable though
SQL> DECLARE
  2      vsfile UTL_FILE.file_type;
  3  BEGIN
  4      vsfile := UTL_FILE.fopen('ORALOAD', 'test.txt', 'r');
  5  END;
  6  /
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 4

Open in new window


but, if I recreate the directory without the space in it then it works

SQL> CREATE OR REPLACE DIRECTORY ORALOAD AS 'C:\temp\test_utl_file';

Directory created.

SQL> DECLARE
  2      vsfile UTL_FILE.file_type;
  3  BEGIN
  4      vsfile := UTL_FILE.fopen('ORALOAD', 'test.txt', 'r');
  5  END;
  6  /

PL/SQL procedure successfully completed.

Open in new window


Using fclose as an example of success isn't particularly helpful for checking success.

For example, you can close a file you never opened and it won't return an exception.

SQL> DECLARE
  2      vsfile UTL_FILE.file_type;
  3  BEGIN
  4      UTL_FILE.fclose(vsfile);
  5  END;
  6  /

PL/SQL procedure successfully completed.

Open in new window


So, if your open failed because of invalid directories, checking the result of fclose won't help.


FCOPY doesn't work with spaces in the directory path either
SQL> CREATE OR REPLACE DIRECTORY ORALOAD AS 'C: \temp\test_utl_file';

Directory created.

SQL> BEGIN
  2      UTL_FILE.fcopy('ORALOAD', 'test.txt', 'ORALOAD','test2.txt');
  3  END;
  4  /
BEGIN
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 270
ORA-06512: at "SYS.UTL_FILE", line 1243
ORA-06512: at line 2

Open in new window


but, fcopy will work with a correct path

SQL> CREATE OR REPLACE DIRECTORY ORALOAD AS 'C:\temp\test_utl_file';

Directory created.

SQL> BEGIN
  2      UTL_FILE.fcopy('ORALOAD', 'test.txt', 'ORALOAD','test2.txt');
  3  END;
  4  /

PL/SQL procedure successfully completed.

Open in new window


I know in your code you used fcopy to write to a different directory; but what I'm trying to show is the problem with using an invalid path.  We don't need to create 2 invalid directories to illustrate that problem.  One invalid path is sufficient to demonstrate the failure.


If you have counter examples where invalid create directory succeeds or where a directory is created with an invalid path but you can use it successfully for fcopy or fopen please post them; because what you have described and what you have posted don't seem to reconcile.
0
 

Author Comment

by:carlino70
ID: 39961458
Look again please.

Copying and pasting in the original question lines creating directories, was introduced involuntarily separation spaces between the words in the path, not corrected for me before post and I apologize for this.

But the correct paths of creation was always good in my database.

In the test you performed, enter a space after 'C: \'
'C:\ temp\test_utl_file';

This space does not exist in my original statement.

See please a complete test:

sql>CREATE OR REPLACE DIRECTORY ORALOAD AS 'C:\temp\test_utl_file\';
sql>Directory created.

sql>CREATE OR REPLACE DIRECTORY CORRECTOS AS 'C:\temp\test_utl_file\CORRECTOS';
sql>Directory created.  

Open in new window


--Test frename
exec utl_file.frename('ORALOAD', 'test.txt', 'CORRECTOS', 'correcto_test.txt', TRUE);  

Error at line 1
ORA-29292: ORA-29292: file rename operation failed
ORA-06512: en "SYS.UTL_FILE", línea 340
ORA-06512: en "SYS.UTL_FILE", línea 1186
ORA-06512: en línea 1

Open in new window


--Test fcopy
exec utl_file.fcopy('ORALOAD','test.txt','CORRECTOS','ok_test_'||to_char(sysdate,'yyyymmddhh24miss')||'.txt',0,1000);
PL/SQL procedure successfully completed.

Open in new window


test.txt was copied to CORRECTOS and renamed.
Comprobation:
C:\temp\test_utl_file>dir
26/03/2014  05:09 p.m.                 5 test.txt

C:\temp\test_utl_file>cd CORRECTOS
C:\temp\test_utl_file\CORRECTOS>dir
28/03/2014  09:54 a.m.                 5 ok_test_20140328095408.txt
C:\temp\test_utl_file\CORRECTOS>

Open in new window


--Test fremove
exec utl_file.fremove('ORALOAD','test.txt');

Error at line 1
ORA-29291: file remove operation failed 
ORA-06512: en "SYS.UTL_FILE", línea 243
ORA-06512: en "SYS.UTL_FILE", línea 1126
ORA-06512: en línea 1

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39961543
test.txt  can't be renamed or removed if it is open by some thing else, even your own session.
0
 

Author Comment

by:carlino70
ID: 39961653
sdstuber, look please the procedure attached.
read-demo.sql
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39961665
that doesn't help illustrate if test.txt is open or not when you attempt the frename.


you close vsfile  but that doesn't mean that file was pointing to test.txt.

and even if it was, that doesn't mean some other session has it open, or somebody has the file open in an editor, or has it open for some other reason
0
 

Author Comment

by:carlino70
ID: 39961680
no one is using 'test.txt' in my session, or in another
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39961709
try this


DECLARE
    v_file     UTL_FILE.file_type;
    v_filename VARCHAR2(100) := 'sds' || TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || '.txt';
BEGIN
    v_file := UTL_FILE.fopen('ORALOAD', v_filename, 'w');
    UTL_FILE.fclose(v_file);

    UTL_FILE.frename(
        'ORALOAD',
        v_filename,
        'CORRECTOS',
        v_filename,
        TRUE
    );
END;

Open in new window


Do you end up with a new file in the CORRECTOS subdirectory?

If not, what is the error?
0
 

Author Comment

by:carlino70
ID: 39962246
works fine.

D:\oracle\product\10.2.0\oradata\TEMP\CORRECTOS\sds20140328142837.txt
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39962576
Since the utl_file functionality works then either

the file test.txt  is either invalid/corrupt somehow
or
the file correcto_test.txt already exists and is invalid/corrupt somehow
or
the assumption that these files are not open by something else is false
or
you have encountered a bug in utl_file and will need to contact oracle support.

or a combination of these
0
 

Author Closing Comment

by:carlino70
ID: 40004934
Tkanks
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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
This video shows how to recover a database from a user managed backup

730 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