Solved

Problem using utl_file.frename ORA-29292

Posted on 2014-03-27
15
3,473 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
  • 8
  • 7
15 Comments
 
LVL 73

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 73

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
 

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 73

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 73

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 73

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 73

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 73

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 73

Accepted Solution

by:
sdstuber earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now