Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29280: invalid directory path - Using UTL_FILE (Oracle 11g, Windows 7 Pro (64bit))
icon5.gif  ORA-29280: invalid directory path - Using UTL_FILE [message #564063] Tue, 21 August 2012 03:17 Go to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Good Morning,

I have encountered a problem since migrating a database from one server to another.

The previous server was running Oracle 10, the new one is on Oracle 11g.

Within the database for the user is a Package/Procedure and this was working fine on the original server: -

create or replace
PACKAGE BODY STOCK_EXPORT AS


FileDirectory               VarChar2(100)   :=  'C:\Transfert';
FileName1                   VarChar2(100)   :=  'EXPSUSUSHOP.txt';
Separator                   VarChar(3)      :=  '|';
Issuance                    VarChar(10)     :=  '100';
OutletCode1                 VarChar(10)     :=  '1';





/*************************************************************************/
/* PROCEDURE FILE_DIRECTORY + MISE A JOUR NOM DE FICHIER AUTOMATIQUE
/*************************************************************************/

PROCEDURE File_Directory_01 is
begin

UPDATE EXPORT_file_directory SET FILENAME_TARGET=sysdate-1||'-'|| seq_export_stock.NEXTVAL ||'-'||filename1;
COMMIT;
 SELECT
    Directory_Target,Filename_target into FileDirectory,Filename1
 FROM
    EXPORT_file_directory;
end;

/*************************************************************************/
/* Procedure to Export Articles from 'Outlet/Shop 1' - Union Shop
/*************************************************************************/
PROCEDURE EXP_ARTICLES_01 is
CURSOR CS1 IS

            SELECT  AR.CODE ART_CODE, TO_CHAR(TR.BOOKKEEPING_DATE, 'dd.mm.yyyy') ART_DATE, 0 ART_PRICE,
                    SUM(TA.QTY_WEIGHT) ART_QTY, SH.DESCRIPTION SHOP_DESC, TA.VAT_PERCENT VAT,
		    (SELECT VA.CODE FROM VATS VA WHERE TA.VAT_ID = VA.ID) VAT_ID,
                    TRANSLATE(AR.DESCRIPTION, '"', ' ' ) ART_DESC,
                    TO_CHAR(TR.BOOKKEEPING_DATE, 'hh.mm.ss') TIME_STAMP, NVL(SUM(TA.PRICE), 0) ART_AMOUNT
            FROM    TRANSACTIONS TR, TRANS_ARTICLES TA, ARTICLES AR, SHOPS SH
            WHERE   TR.BOOKKEEPING_DATE BETWEEN TO_DATE(SH.NOTES1, 'dd.mm.yyyy') AND sysdate-1
            AND     TR.DELETE_OPERATOR_ID IS NULL
            AND     TR.SHOP_ID  = SH.ID
            AND     TA.DELETE_OPERATOR_ID IS NULL
            AND     TR.ID = TA.TRANSACTION_ID
            AND     AR.ID = TA.ARTICLE_ID
            AND     TR.EXPORT_STOCK IS NULL
            AND     AR.STOCK_CONTROLLED = '1'
            AND     SH.ID = (SELECT ID FROM SHOPS WHERE CODE = OutletCode1)
            GROUP BY AR.CODE, TO_CHAR(TR.BOOKKEEPING_DATE, 'dd.mm.yyyy'), SH.DESCRIPTION, TA.VAT_PERCENT, AR.DESCRIPTION, TO_CHAR(TR.BOOKKEEPING_DATE, 'hh.mm.ss'), TA.VAT_ID
            ORDER BY AR.CODE, TO_CHAR(TR.BOOKKEEPING_DATE, 'dd.mm.yyyy');

    FP UTL_FILE.FILE_TYPE;
    ART_REC CS1%ROWTYPE;
    MYSTRING VARCHAR2(1000) := '';

BEGIN
    FP:=UTL_FILE.fopen(FileDirectory,FileName1,'W');
    UTL_FILE.fclose(FP);
    FP:=UTL_FILE.fopen(FileDirectory,FileName1,'A');
    OPEN CS1;
LOOP
    FETCH CS1 INTO ART_REC;
    EXIT WHEN CS1%NOTFOUND;

    MYSTRING := ART_REC.ART_CODE || Separator || ART_REC.ART_DESC || Separator || ART_REC.ART_PRICE || Separator || ART_REC.ART_QTY ||
                Separator || ART_REC.ART_DATE || Separator || ART_REC.SHOP_DESC || Separator || Separator || '0' || Separator ||
                ART_REC.VAT_ID || Separator || ART_REC.VAT || Separator || ART_REC.ART_AMOUNT || Separator || Issuance ||
                Separator || '20:00:00';

    UTL_FILE.put_line(FP,MYSTRING);
END LOOP;
    CLOSE CS1;
    UTL_FILE.fclose(FP);


/*****************************************************************/
/* Update Transactions
/*****************************************************************/
update TRANSACTIONS TR SET TR.EXPORT_STOCK = '1'
                WHERE TR.BOOKKEEPING_DATE BETWEEN TO_DATE((SELECT NOTES1 FROM SHOPS WHERE CODE = OutletCode1), 'dd.mm.yyyy') AND sysdate-1;
                COMMIT;

UPDATE SHOPS SET NOTES1 = TO_CHAR(sysdate-1, 'dd.mm.yyyy') WHERE CODE = OutletCode1;
COMMIT;

EXCEPTION
WHEN OTHERS THEN
    UTL_FILE.fclose(FP);
    dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
END;

/*************************************************************************/
/* PROCEDURE EXPORT_STOCK_ALL
/*************************************************************************/
PROCEDURE ARTICLES AS
 BEGIN
      File_Directory_01;
      EXP_ARTICLES_01;
 END;
END;


When I now run the above I'm returned the following error: -

Error -29280: ORA-29280: invalid directory path


I've checked on the OS that the directory does exist 'C:\Transfert' and that it is in the Table 'EXPORT_FILE_DIRECTORY'.

I'm at a loss as to what the cause could be, unless it has something to do with the declaration or configuration of UTL_FILE?

Any advice or suggestions would be greatly received.

Thanks & Regards



Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564068 is a reply to message #564063] Tue, 21 August 2012 03:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've checked on the OS that the directory does exist 'C:\Transfert'


On database server?

Quote:
that it is in the Table 'EXPORT_FILE_DIRECTORY'.


Show us.

Quote:
Error -29280: ORA-29280: invalid directory path


At which line?

Directory parameter should be an Oracle directory NOT an OS one.

Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564072 is a reply to message #564068] Tue, 21 August 2012 04:51 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Michel Cadot wrote on Tue, 21 August 2012 08:40
Quote:
I've checked on the OS that the directory does exist 'C:\Transfert'


On database server?

Quote:
that it is in the Table 'EXPORT_FILE_DIRECTORY'.


Show us.

Quote:
Error -29280: ORA-29280: invalid directory path


At which line?

Directory parameter should be an Oracle directory NOT an OS one.

Also always post your Oracle version, with 4 decimals.

Regards
Michel


Yes, on the Server it's being run on.

Here's the table 'export_file_directory' : -

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> select * from export_file_directory;

DIRECTORY_TARGET
------------------------------------------------------------
FILENAME_TARGET
-------------------------------------------------------------

C:\TRANSFERT
20-AUG-12-2322-EXPSUSUSHOP.txt


It appears to be on the line: -

FP:=UTL_FILE.fopen(FileDirectory,FileName1,'W');


Sorry, but what do you mean by the following; "Directory parameter should be an Oracle directory NOT an OS one."?

Regards
Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564081 is a reply to message #564072] Tue, 21 August 2012 05:44 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
UTL_FILE.fopen(FileDirectory,FileName1,

Quote:
FileDirectory VarChar2(100) := 'C:\Transfert';

This is an OS directory, not an Oracle one.

Regards
Michel
Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564159 is a reply to message #564081] Tue, 21 August 2012 13:41 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Michel Cadot wrote on Tue, 21 August 2012 10:44
Quote:
UTL_FILE.fopen(FileDirectory,FileName1,

Quote:
FileDirectory VarChar2(100) := 'C:\Transfert';

This is an OS directory, not an Oracle one.

Regards
Michel


Thanks for your reply.

I've looked at the information regarding the CREATE DIRECTORY and this does appear to be the problem as previously on the other machine (Oracle 10g) in the init.ora there was the following: -

###########################################
# PL/SQL
###########################################
utl_file_dir=*


This isn't configured on the new machine.

So to specify the location using the CREATE DIRECTORY what isn't clear to me is if I do this logged in as the Admin or the User?

Once logged in as required, I then need to run: -

CREATE DIRECTORY FileDirectory AS 'C:\Transfert';


Is this correct?

Thanks & Regards
Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564164 is a reply to message #564159] Tue, 21 August 2012 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
utl_file_dir=*


This is a BIG security hole, do you understand that this allows a user to overwrite your database files?

Quote:
So to specify the location using the CREATE DIRECTORY what isn't clear to me is if I do this logged in as the Admin or the User


Most often a DBA that gives you the READ and/or WRITE privilege on it.

Regards
Michel
Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564215 is a reply to message #564063] Wed, 22 August 2012 02:26 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Thanks for the reply.

I've created the DIRECTORY within Oracle: -

SQL> create directory FileDirectory as 'C:\Transfert';

Directory created.

SQL> grant read, write on directory filedirectory to susu;

Grant succeeded.


However, when I run it I still get the same error: -

Error -29280: ORA-29280: invalid directory path


This still happens even if I create it as 'C:/Transfert'.

What have I missed?

Thanks & Regards
Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564219 is a reply to message #564215] Wed, 22 August 2012 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 59506
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The directory does not exist (on the database server) or is not accessible by Oracle.

Also did you change the code?

Regards
Michel
Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564220 is a reply to message #564219] Wed, 22 August 2012 03:17 Go to previous messageGo to next message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Michel Cadot wrote on Wed, 22 August 2012 07:59
The directory does not exist (on the database server) or is not accessible by Oracle.

Also did you change the code?

Regards
Michel


Yes, the directory does exist on the database server.

By not accessible by Oracle, are you refering to the permissions on the directory, as everyone has Read & Write.

With regards to the code, are you refering to the following: -

BEGIN
    FP:=UTL_FILE.fopen('My_DIR',FileName1,'W');
    UTL_FILE.fclose(FP);
    FP:=UTL_FILE.fopen('My_DIR',FileName1,'A');
    OPEN CS1;
LOOP
......


Which I have changed although possibly not correctly?

Regards
Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564232 is a reply to message #564220] Wed, 22 August 2012 05:01 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2050
Registered: January 2010
Senior Member
Penfold wrote on Wed, 22 August 2012 04:17
Which I have changed although possibly not correctly?


Code you posted:

FP:=UTL_FILE.fopen('My_DIR',FileName1,'W');


Is incorrect. Oracle directory object is no different from any other Oracle object and follows same Oracle name rules. Unless name is created as quoted name (enclosed in double quotes) it is stored in data dictionary in upper case. So when you created Oracle directory object:

CREATE DIRECTORY My_DIR AS ....;


Oracle directory name in data dictionary is 'MY_DIR' while in UTL_FILE.fopen you referenced Oracle directory name as 'My_DIR'. Therefore, UTL_FILE can't find Oracle directory object and raises Error -29280: ORA-29280: invalid directory path. Change Oracle directory name to upper case and you should be OK.

SY.

[Updated on: Wed, 22 August 2012 05:03]

Report message to a moderator

Re: ORA-29280: invalid directory path - Using UTL_FILE [message #564266 is a reply to message #564232] Wed, 22 August 2012 07:07 Go to previous message
Penfold
Messages: 112
Registered: June 2005
Senior Member
Many thanks for the explanation, that now works.

Thanks for your time & Regards.



[Edit MC: remove useless complete previous post quote]

[Updated on: Wed, 22 August 2012 08:23] by Moderator

Report message to a moderator

Previous Topic: How to return in one row only
Next Topic: select_question
Goto Forum:
  


Current Time: Fri Oct 31 13:41:22 CDT 2014

Total time taken to generate the page: 0.06380 seconds