Discussion :

[NicolasC]

Bonjour à tous et d'avance merci à ceux qui pourront m'aider.

Dans le cadre de mon travail, je dois réaliser l'extraction de certains champs d'une dizaine de table de base de données au format csv (ou txt).

Je pensais tout d'abord utiliser le package dbms_output, mais celui-ci borne la longueur des lignes à 255 caractères. Or, mes lignes dépassent allègrement les 300/400 caractères, et je n'ai pas trouvé de moyen pour augmenter cette taille.
Je me suis donc rabattu sur un package que je n'ai encore jamais utilisé, mais dont j'avais entendu parler : UTL_FILE.

Celui-ci semble permettre des lignes de 1000 caractères, mais malheureusement j'ai une exception à l'exécution.


Voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
spool C:\Extraction\clients.log
 
Set linesize 10000
set feedback off
set flush off
set heading off
set headsep off
set timing off
set pagesize 0
set recsep off
set termout off
set trimspool on
set verify off
set wrap on
set pause off
set serveroutput on
 
DECLARE     
	handle UTL_FILE.FILE_TYPE ;
	chaine varchar2(30) := 'test écriture';
	ouvert boolean;
	excep  exception;
        message varchar2(50);
        repertoire Varchar(30)   := 'C:\Extraction';
        fichier varchar(30) := 'test.txt';
 
BEGIN
 
	begin
		handle := UTL_FILE.FOPEN(repertoire, fichier, 'W', 32764);
		Exception
		When OTHERS Then
			message := sqlerrm;
                        raise excep;
		ouvert := UTL_FILE.IS_OPEN( handle );
		-- Petit test "alacon"
                if ouvert = true then
		  dbms_output.put_line('fichier ouvert ');
		else
		  dbms_output.put_line('fichier non ouvert ');
		end if;
    End;
 
 
	UTL_FILE.PUT_LINE( handle, chaine ) ;
	UTL_FILE.FCLOSE( handle ) ;
 
Exception
   When excep Then
     UTL_FILE.FCLOSE_ALL ;
     RAISE_APPLICATION_ERROR( -20100, message ) ;
 
END;
/
spool off
exit

Et voici l'exception que j'obtiens :

DECLARE
*
ERROR at line 1:
ORA-20100: User-Defined Exception
ORA-06512: at line 33

La ligne 33 est bien évidemment la ligne correspondant à mon RAISE_APPLICATION_ERROR.
Je n'arrive pas d'une part à savoir ce que signifie cette exception, et encore moins à la résoudre.

J'espère que vous pourrez m'aider.

PS : je suis sous Oracle 9I

[Waldar]

Est-ce que votre répertoire est bien déclaré dans le paramètre utl_file_dir de votre .ini ?

[SheikYerbouti]

Vous masquez la véritable erreur avec votre block exception.
Avec une 9i, il est conseillé de passer par les DIRECTORIES.

[NicolasC]

Est-ce que votre répertoire est bien déclaré dans le paramètre utl_file_dir de votre .ini ?

Je n'ai aucun paramètre utl_file_dir dans mon fichier init.ora (ce n'est pas moi qui gère l'installation d'oracle, mais une autre équipe).
Est-ce qu'il suffit que je rajoute une ligne comme celle_ci : utl_file_dir = C:\Extraction ?

Vous masquez la véritable erreur avec votre block exception.
Avec une 9i, il est conseillé de passer par les DIRECTORIES.

J'ai lu votre doc sur le UTL_FILE (http://sheikyerbouti.developpez.com/...e=Chap8#L8.2.5) et j'ai essayé de créer un DIRECTORY (puis de donner les droits d'écriture) puis de l'utiliser dans mon script, et cette exception se lançait tout de même.

[SheikYerbouti]

Juste pour info, Les DIRECTORIES sont stockées en majuscules dans la vue ALL_DIRECTORIES, donc ça ne fonctionnera pas si vous saisissez dans votre code la DIRECTORY en minuscules.

[Waldar]

Je n'ai aucun paramètre utl_file_dir dans mon fichier init.ora (ce n'est pas moi qui gère l'installation d'oracle, mais une autre équipe).
Est-ce qu'il suffit que je rajoute une ligne comme celle_ci : utl_file_dir = C:\Extraction ?.

Pour attaquer un fichier par utl_file, il faut soit passer par un DIRECTORY, soit par le paramètre utl_file_dir.

Mais comme le précise SheikYerbouti c'est beaucoup plus avantageux de passer par les objets DIRECTORIES.
Modifier le .ini vous oblige à redémarrer votre base, ce n'est pas très souple !

[NicolasC]

Juste pour info, Les DIRECTORIES sont stockées en majuscules dans la vue ALL_DIRECTORIES, donc ça ne fonctionnera pas si vous saisissez dans votre code la DIRECTORY en minuscules.

Effectivement, je saisissais la DIRECTORY en minuscules.
J'ai donc refait le test.

Voici la création de ma DIRECTORY :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
CREATE DIRECTORY FICHIERS AS 'C:\EXTRACTION';
GRANT READ, WRITE ON DIRECTORY FICHIERS TO PUBLIC;

Apparemment, il n'y a pas eu de problèmes ici, ma fenêtre SQL m'a confirmé la création.

J'ai ensuite modifié mon script pour utiliser cette DIRECTORY en me basant sur votre tutorial :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
spool C:\Extraction\clients.log
 
DECLARE     
	handle UTL_FILE.FILE_TYPE ;
	chaine varchar2(30) := 'test écriture';
                  message varchar2(50);
                 repertoire Varchar(30)   := 'FICHIERS';
                 fichier varchar(30) := 'TEST.TXT';
                  except exception;
 
BEGIN
 
	begin
		handle := UTL_FILE.FOPEN(repertoire, fichier, 'W', 32764);
		Exception
    		  When OTHERS Then
		  message := SQLERRM || ' [' || repertoire || '] -> ' || fichier;
		  Raise except;
 
 
   	 End;
 
 
	UTL_FILE.PUT_LINE( handle, chaine ) ;
	UTL_FILE.FCLOSE( handle ) ;
 
Exception
   When except Then
     UTL_FILE.FCLOSE_ALL ;
     RAISE_APPLICATION_ERROR( -20100, message ) ;
 
 
END;
/
spool off
exit

A noter que ce script est lancé par un batch, car j'aurais plusieurs extractions à faire.

Le script est appelé par l'instruction suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

sqlplus @clients.sql

A l'exécution, j'ai désormais une autre exception :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
DECLARE
*
ERROR at line 1:
ORA-20100: User-Defined Exception [FICHIERS] -> TEST.TXT 
ORA-06512: at line 28

En espérant que l'un d'entre vous saura me dire pourquoi ...

[Waldar]

A vérifier, mais vous avez donnez les droits au user PUBLIC, est-ce que vous vous connectez avec ce compte-ci (je ne suis pas sûr que les droits se propagent à tous les users) ?

[NicolasC]

Je me connecte en administrateur, donc j'imagine (et j'espère )qu'il a tous les droits



PS : j'ai finalement réussi à générer mon extraction par une méthode des plus moches (création d'un spool + 2 selects). C'est moche niveau code, mais ça marche. En revanche, je reste intéressé par la solution du problème que j'ai en utilisant l'UTL_FILE

[Waldar]

Avec le compte Oracle SYSTEM ? ou SYS ?
Essayez quand même de faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

GRANT READ, WRITE ON DIRECTORY FICHIERS TO <user_oracle>;

Si ça ne marche pas, et bien ce sera autre chose

[SheikYerbouti]

Mettez ceci dans votre block Exception:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
Exception
 
When UTL_FILE.INVALID_PATH Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File location is invalid.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_MODE Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The open_mode parameter in FOPEN is invalid.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_FILEHANDLE Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File handle is invalid.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_OPERATION    Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File could not be opened or operated on as requested.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.READ_ERROR    Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the read operation.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.WRITE_ERROR Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the write operation.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INTERNAL_ERROR then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Unspecified PL/SQL error';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.CHARSETMISMATCH Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'A file is opened using FOPEN_NCHAR,'
      || ' but later I/O operations use nonchar functions such as PUTF or GET_LINE.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.FILE_OPEN Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested operation failed because the file is open.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_MAXLINESIZE Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The MAX_LINESIZE value for FOPEN() is invalid;'
      || ' it should be within the range 1 to 32767.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_FILENAME Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The filename parameter is invalid.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.ACCESS_DENIED Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Permission to access to the file location is denied.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.INVALID_OFFSET Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid;'
      ||' it should be greater than 0 and less than the total number of bytes in the file.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.DELETE_FAILED Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file delete operation failed.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When UTL_FILE.RENAME_FAILED Then
    LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file rename operation failed.';
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
 
When others Then
    LC$Msg := 'Erreur : ' || To_char( SQLCODE ) || ' sur ouverture du fichier ('
       || PC$Dir || ') ' || PC$Nom_Fichier ;
    RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;

[NicolasC]

Topic posté le 27/11/2009 ...
J'suis à la bourre.

D'avance, désolé à toutes les personnes essayant de m'aider de ne pas avoir répondu plus tôt. J'ai complètement délaissé cette problématique du jour au lendemain suite à un nouveau projet au boulot (et aux vacances aussi).

Projet fini, j'ai du temps libre, et je reviens donc dessus.

Pour répondre à SheikYerbouti, j'ai mis les exceptions, et mon programme passe dans l'exception "Invalid_Path".

J'ai lancé la requête suivante :
select name, value from v$parameter where name like '%utl_file%';

et je n'ai aucun résultat ! Cela m'intrigue beaucoup.


EDIT : me demandant si le problème ne pouvait pas venir de l'utilisateur générique que nous utilisons pour nous connecter à la base, j'ai essayé de lancer le script en tant que dba : sqlplus "sys/mdp as sysdba" @mon_script.sql.
L'exception "Invalid_Path" se lance quand même


EDIT 2 : après de nouveaux tests, cela fonctionne en Oracle 10g, mais pas en Oracle 9i