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;

Error -29280: ORA-29280: invalid directory path

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

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

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

CREATE DIRECTORY FileDirectory AS 'C:\Transfert';

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

Directory created.

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

Grant succeeded.

Error -29280: ORA-29280: invalid directory path

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

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

CREATE DIRECTORY My_DIR AS ....;