I need to create a CSV file with procedure and this file is saving in my computer, but with my code I have one error. Can you help me please?
My code is:
create or replace PROCEDURE JSP
as
CURSOR C_donnees IS
select distinct CODPRO, MOTCLE,CODZN6,CODZN8
From PRO WHERE CODZN6 = 'C' AND CODSOC = '1';
w_sysdate varchar2(20);
w_repertoire varchar2(64);
w_fichier varchar2(80);
l_fichier_log utl_file.file_type;
BEGIN
-- INITIALISATION DES VARIABLES ---
-----------------------------------
SELECT To_Char(SysDate, 'YYYYMMDD_HH24MISS') DATEJOUR INTO w_sysdate FROM Dual;
w_repertoire := 'C:\Users\julie\Documents\Singer Safety\Fichie SQL';
w_fichier := 'extraction_'||w_sysdate||'.csv';
-- ouverture du ficher ---
dbms_output.put_line('Début de la boucle');
l_fichier_log := utl_file.fopen( w_repertoire, w_fichier, 'A');
-- PARCOUR LE SELECT ---
-------------------------------------
FOR r_donnees IN c_donnees
LOOP
-- ecriture des lignes du select dans le fichier
utl_file.put_line(l_fichier_log, r_donnees.CODPRO || ';' || r_donnees.MOTCLE || ';' || r_donnees.CODZN6 || ';' || r_donnees.CODZN8 );
END LOOP;
--------- fermeture des fichiers et fin ----------
UTL_FILE.fclose_all;
dbms_output.put_line('FIN du script');
END; -- fin de la procédure
And I have this error:
ORA-29280: chemin de répertoire non valide
ORA-06512: à "SYS.UTL_FILE", ligne 41
ORA-06512: à "SYS.UTL_FILE", ligne 478
ORA-06512: à "C##JLECAT.JSP", ligne 21
Can you help me please?
The location
parameter to UTL_FILE.FOPEN
must be an Oracle directory object name, not a path string. These can be created like this:
CREATE OR REPLACE DIRECTORY my_dir AS '/usr/bin/my_directory';
Then you would specify 'MY_DIR' as the location
, i.e.
w_repertoire := 'MY_DIR';
Also, the location has to be accessible to the database server, not local to your machine as your path appears to be.