Procédure PL/SQL : passer un fichier .txt ou .csv en paramètre

Version imprimable

18/04/2012, 15h43
JQueen

Procédure PL/SQL : passer un fichier .txt ou .csv en paramètre

Bonjour à tous,

Je travaille sur une base de données que je veux mettre à jour en utilisant un fichier txt ou csv.

Pour cela, je veux écrire une procédure en PL/SQL qui prend en paramètre ce fichier, parcours ses lignes et applique les mises à jour.

Ce que je demande ici c'est comment passer un fichier en paramètre. J'ai pensé à ceci :

Code:

create or replace procedure MiseAJour (monFichier UTL_FILE.FILE_TYPE) IS ...

des suggestions ?

Merci d'avance
18/04/2012, 15h55
McM

C'est la bonne méthode si le fichier est sur le sur le serveur de base et que tu l'ouvres avant.
18/04/2012, 16h08
JQueen

Citation:

si le fichier est sur le sur le serveur de base et que tu l'ouvres avant.

Je dois mettre le fichier sous quel path ?
Vous voulez dire quoi par l'ouvrir avant ? avant de créer la procédure ou avant d'exécuter la procédure ?

Merci
18/04/2012, 16h41
Scriuiw

Personnellement je préfère utiliser le Loader SQL et travailler le contenu du fichier à partir d'une table temporaire.

Citation:

Je dois mettre le fichier sous quel path ?

Celui que tu veux mais attention, cela dépend du paramétrage du serveur, il est possible que le fichier doit se trouver impérativement sur le serveur Oracle et non sur un serveur distant

Citation:

avant de créer la procédure ou avant d'exécuter la procédure ?

Je pense que McM voulait dire qu'il fallait ouvrir le fichier via UTL_FILE.FOPEN dans ta procédure avant de commencer à le scanner
18/04/2012, 18h23
McM

Le path, c'est un DIRECTORY Oracle sur le serveur.
Un file_type, c'est un pointeur sur un fichier, donc forcément déjà ouvert par utl_file.fopen

Pour compléter : La base ne peut accéder directement au poste client.

Pour intégrer un fichier, 3 possibilités :
sqlloader (ça reste du manuel, donc à réserver au one-shot)
lecture de fichier par utl_file (le fichier doit être sur le serveur de base)
table externe (le fichier doit aussi être sur le serveur de base)
par forms : webutil, upload dans un clob et lecture du clob

Tout dépend de l'utilisation, de sa fréquence, volumétrie, besoin d'avoir un historique, etc..

Procédure de mise à jour

Merci pour tous vos aides.

Voilà ce que je viens de faire :
1- j'ai créé un directory nommé repertoire qui pointe sur le répertoire physique existant sur le disque et contenant mon fichier
2- j'ai écrit cette procédure :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 CREATE OR REPLACE PROCEDURE MiseAJour (resultat2.txt UTL_FILE.FILE_TYPE) IS -- declaration des constantes, variables, curseurs et exceptions fich_resultat UTL_FILE.FILE_TYPE; buffer varchar2(1500); BEGIN -- creation du descripteur du fichier fich_resultat := UTL_FILE.FOPEN ('repertoire', 'resultat2.txt', 'r'); -- lecture du fichier et insertion des données dans la table Avion loop UTL_FILE.DET_LINE(fich_resultat, buffer); exception when no_data_found then exit; end; insert into Avion () values (buffer); end loop; commit; UTL_FILE.FCLOSE(fich_resultat); -- declaration des exceptions END;
Mon souci ici est : je sais et je suis sûre que chaque enregistrement du fichier (chaque ligne) a la même structure que celle de chaque enregistrement de ma table Avion. L'insertion des données dans la table se fait-elle convenablement ? C'est-à-dire est-ce que l'enregistrement du fichier est lu champ par champ ?

J'espère être bien claire.

Merci pour tout éclaircissement :ccool:

19/04/2012, 08h51
mnitu

Citation:

Envoyé par JQueen

...L'insertion des données dans la table se fait-elle convenablement ?
...

Non il va falloir bosser. Mais si à la place de la lecture du fichier via UTL_FILE vous utilisez une table externe alors oui ça va être plus simple d’écrire l’insert. Mais, il va falloir bosser pour écrire la définition de la table externe. Personnellement pour ce genre de choses j’utiliserais une table externe.
19/04/2012, 08h55
Scriuiw

Code:

INSERT INTO Avion () VALUES (buffer);

Ça ne marchera pas comme ça, il faut que tu découpes ton buffer zone à zone
19/04/2012, 10h09
McM
Ton paramètre, ça ne va pas : (resultat2.txt UTL_FILE.FILE_TYPE)

A ce que tu dis, ton fichier existe dans le directory, donc tu n'as qu'à passer le nom du fichier (comme tu l'utilises dans le fopen)

Code:

fich_resultat := UTL_FILE.FOPEN ('repertoire', 'resultat2.txt', 'r');

Ton code serait plus comme ceci :
Code:

1 2 3 4 CREATE OR REPLACE PROCEDURE MiseAJour (p_nomfic IN VARCHAR2) IS ... fich_resultat := UTL_FILE.FOPEN ('REPERTOIRE', p_nomfic, 'r'); ...
Attention, les directories Oracle sont stockés en majuscules.

Bonjour,

Citation:

Ça ne marchera pas comme ça, il faut que tu découpes ton buffer zone à zone

Oui, c'est ce que je me suis dit aussi. Je suis en train de travailler là dessus.

Citation:

Mais si à la place de la lecture du fichier via UTL_FILE vous utilisez une table externe alors oui ça va être plus simple d’écrire l’insert

Là, vous voulez dire qu'il vaudrait mieux créer une table dans le bloc PL/SQL pour recevoir les données du fichier? ça me parait bien mais je ne vois pas vraiment l'idée ...

Citation:

Ton paramètre, ça ne va pas : (resultat2.txt UTL_FILE.FILE_TYPE)

J'ai effectué les changements et voilà mon code :
Code:

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 CREATE OR REPLACE PROCEDURE MiseAJour (test_result.txt in varchar2) IS -- declaration des constantes, variables, curseurs et exceptions fich_resultat UTL_FILE.FILE_TYPE; buffer varchar2(1500); BEGIN -- creation du descripteur du fichier fich_resultat := UTL_FILE.FOPEN ('REPERTOIRE', 'test_result.txt', 'r'); -- lecture du fichier et insertion des données dans la table Avion loop UTL_FILE.GET_LINE(fich_resultat, buffer); exception when no_data_found then exit; end; insert into Avion () values (buffer); end loop; commit; UTL_FILE.FCLOSE(fich_resultat); -- declaration des exceptions END; /
Je vais essayer de voir comment lire les lignes du fichier champ par champ, et je vais faire des recherches aussi sur l'idée de table externe.
Des suggestions, des liens, des idées seront les bienvenus :ccool:

19/04/2012, 10h49
JQueen

J'ai oublié de mentionner que l'exécution de mon code PL/SQL génère l'erreur suivante :

Citation:

Avertissement : procédure créée avec erreurs de compilation.

Pourquoi ? Comment peut-on connaitre les erreurs ? les localiser ?

Merci
19/04/2012, 11h18
Scriuiw

Tout dépend de l'outil que vous utilisez

En SQL+ il faut tapper

Code:

sho err

pour afficher les erreurs

Sinon

Code:

PROCEDURE MiseAJour (test_result.txt IN varchar2)

Je ne pense pas qu'Oracle supporte les . dans une variable
Plutôt que test_result.txt, un nom de variable plus explicite serait souhaité comme par exemple

Code:

pe_fichier IN VARCHAR2

Code:

1
2
3
4
5
6
7
8
9
-- lecture du fichier et insertion des données dans la table Avion
	loop
		UTL_FILE.GET_LINE(fich_resultat, buffer);
		exception
			when no_data_found then exit;
		end;
		INSERT INTO Avion () VALUES (buffer);
	end loop;
	commit;

Il manque un BEGIN dans la boucle

Code:

1
2
3
4
5
6
7
LOOP
  BEGIN
    [..]
  EXCEPTION
    WHEN..
  END;
END LOOP;

L'INSERT est mal codé aussi

Et j'oubliais...

Code:

commit;

Pas bien !!!

19/04/2012, 11h47
JQueen
voilà les erreurs que j'ai corrigés :
1- j'ai supprimé l'extention ".txt" du nom de fichier passé en paramètre, car comme vous avez dit c'est inacceptable par oracle
2- j'ai ajouté le mot clé "begin" manquant
3- j'ai supprimé le "commit"

En exécutant le script, j'ai eu l'erreur suivante :

Citation:

pls-00201 identifier 'utl_file' must be declared

il s'est avéré que ceci est du au manque de privilèges sur le package UTL_FILE, voilà ce que j'ai fait :
Code:

1 2 grant execute on utl_file to my_user;
La procédure est bien créée.

Mais, j'ai fait des recherches pour voir comment manipulé le buffer champ par champ mais aucun résultat :( des idées ? des pistes ?

Merci
19/04/2012, 11h54
Scriuiw

Quelle est la structure du fichier
Longueur fixe des zones ou séparateur ?

Si longueur fixe, tu peux utiliser SUBSTR pour faire le découpage sinon si c'est par séparateur il faut boucler sur le buffer en utilisant les fonctions SUBSTR et INSTR

Mon fichier est le résultat de ce script :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SPOOL D:\balkis\test_result.txt
 
set echo off
set feed off
set verify off
set heading off
set NULL null
set termout off
 
SELECT NumAv || '	' ||  NomAv || '	' || Capacite || '	' || Localisation  
FROM Avion;
 
SPOOL OFF

les colonnes (champs) sont séparées par une tabulation.

19/04/2012, 13h56
McM
une tabulation c'est un chr(9)
pour le savoir, il suffit de faire un

Code:

select ascii(' ') from dual

Pour ton paramètre, ce n'est toujours pas ça, il n'est pas utilisé dans ta procédure. Tu ouvres toujours le même fichier texte qui s'appelle 'test_result.txt' quelque soit le paramètre d'entrée de procédure.
Code:

1 2 3 UTL_FILE.FOPEN ('REPERTOIRE', 'test_result.txt', 'r'); à remplacer par UTL_FILE.FOPEN ('REPERTOIRE', test_result, 'r');
Pour le découpage, tu peux faire une recherche sur le forum (découpage chaine suivant séparateur), sinon vois avec instr qui te permet de récupérer la position d'une chaine (ou la position d'une Xème répétition d'une chaine), et de substr qui te permet de découper une chaine.
19/04/2012, 14h15
mnitu

Citation:

Envoyé par JQueen

...et je vais faire des recherches aussi sur l'idée de table externe.
Des suggestions, des liens, des idées seront les bienvenus

13 External Tables Concepts

mise à jour de la table : idée tri du fichier et de la table

Bonjour,

Toujours dans le but de mettre à jour ma base, j'ai essayé de résoudre deux problèmes dans mon script:
1- traiter les lignes de mon fichier champ par champ, et ceci en utilisant les deux fonctions substr et instr

2-insertion des données du fichier dans ma table (Avion dans notre cas), et ceci en optant pour l'idée suivante :

trier les lignes de mon fichier
créer un curseur sur ma table dont les valeurs sont ordonnées
insertion des données

voilà ce que j'ai fait jusqu'à maintenant :
1- là c'est le code du curseur créé
Code:

1 2 cursor avion_cur is select id from Avion order by id;
2- là c'est le code du tri de mon fichier
Code:

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 -- variables déclarées i, num1, num2 integer; pos_fich1, pos_fich2 integer; anc_position, position integer; chaine1, chaine2 varchar2(20); buffer, buffer1, buffer2, buffAux varchar2(1500); fich_resultat UTL_FILE.FILE_TYPE; -- tri -- creation du descripteur du fichier fich_resultat := UTL_FILE.FOPEN ('REPERTOIRE', 'test_result', 'r'); -- tri des lignes du fichier test_result while echange loop echange := vrai; loop begin UTL_FILE.GET_LINE(fich_resultat, buffer1); UTL_FILE.GET_LINE(fich_resultat, buffer2); exception when no_data_found then exit; end; pos_fich1 := instr (buffer1, ' '); pos_fich2 := instr (buffer1, ' '); chaine1 := substr (buffer1, 0, pos_fich -1); chaine2 := substr (buffer2, 0, pos_fich -1); num1 := to_number(chaine1); num2 := to_number(chaine2); if (num1 > num2) then buffAux := buffer1; buffer1 :) buffer2; buffer2 := buffAux; echange := faux; end loop; end loop;
En fait, comme le fichier a les mêmes champs que la table, le 1er champ de chaque ligne est un nombre. Donc j'ai procédé comme suit pour le tri :
* extraction du premier champ de chaque ligne
* conversion en nombre (en utilisant la focntion to_number())
* tri à bulles des lignes du fichier

j'aime bien que vous me donnez des remarques ou des suggestions sur ce que je viens de faire.

Pour l'insertion des données dans la table, j'y travaille encore.

Merci d'avance pour tout :ccool:

20/04/2012, 13h46
Scriuiw
Y a t-il une raison spécifique à ce que tu fasses deux lectures de fichier par boucle ?
Code:

1 2 UTL_FILE.GET_LINE(fich_resultat, buffer1); UTL_FILE.GET_LINE(fich_resultat, buffer2);
Tes fichiers ont toujours un nombre pairs de lignes ?
20/04/2012, 13h55
McM

Faire un tri à bulle sur une lecture de fichier, c'est pas top. Si tu dois trier, laisse oracle le faire, sauf si tu n'as pas le choix.
Ensuite ton code de tri à bulle n'est pas bon, un tri à bulle mémorise tous les codes, et ensuite parcoure le tableau autant de fois qu'un élément n'est pas à la bonne place.

Bref.. pourquoi vouloir trier les données de ton fichier ? L'ordre d'insertion est important ? Quel intérêt si ton fichier est déjà créé par un select ordonné ?

Non, ça n'a aucun rapport avec le nombre de lignes du fichier.

En fait, pour commencer la comparaison, j'ai besoin de récupérer les deux premières lignes.

Voilà les corractions que j'ai faites sur mon code :

Code:

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
57
58
59
60
61
62
63
64
 
CREATE OR REPLACE PROCEDURE MiseAJour (test_result in varchar2) IS
	-- declaration des constantes, variables, curseurs et exceptions
	i integer; num1 integer; num2 integer; pos_fich1 integer; pos_fich2 integer; anc_position integer; position integer;
	chaine1 varchar2(20); chaine2 varchar2(20); buffer varchar2(20); buffer1 varchar2(20); buffer2 varchar2(20); buffAux varchar2(1500);
	champ varchar2(20);
	echange boolean;
	fich_resultat UTL_FILE.FILE_TYPE;
	cursor avion_cur is select NumAv from Avion order by NumAv; 
 
BEGIN
	-- creation du descripteur du fichier
	fich_resultat := UTL_FILE.FOPEN ('REPERTOIRE', 'test_result', 'r');
	UTL_FILE.GET_LINE(fich_resultat, buffer1);
	-- tri des lignes du fichier test_result
	while echange loop
		echange := true;
		loop
			begin
			UTL_FILE.GET_LINE(fich_resultat, buffer2);
			exception
				when no_data_found then exit;
			end;
			pos_fich1 := instr (buffer1, '	');
			pos_fich2 := instr (buffer1, '	');
			chaine1 := substr (buffer1, 0, pos_fich1 -1);
			chaine2 := substr (buffer2, 0, pos_fich2 -1);
			num1 := to_number(chaine1);
			num2 := to_number(chaine2);
			if (num1 > num2) then
				buffAux := buffer1;
				buffer1 := buffer2;
				buffer2 := buffAux;
				echange := false;
			end if;
		end loop;
	end loop;
	-- lecture du fichier et insertion des données dans la table Avion
	loop
		begin
		UTL_FILE.GET_LINE(fich_resultat, buffer);
		exception
			when no_data_found then exit;
		end;
		-- mise à jour de la table Avion
		i:=1;
		position := instr (buffer, '	', 1, i);
		for cur in avion_cur loop
			-- traitement du buffer champ par champ en utilisant les deux fonctions substr et instr
			while (position != 0) loop
				anc_position := position;
				position := instr (buffer, '	', 1, i);
				champ := substr(buffer, anc_position+1, position -1); 
			end loop;
		end loop;
		commit;
	end loop;
	commit;
	-- Fermeture du fichier
	UTL_FILE.FCLOSE(fich_resultat);
	 -- declaration des exceptions
 
END;
/

Il reste encore du travail à faire.

20/04/2012, 14h30
JQueen

Citation:

Si tu dois trier, laisse oracle le faire

Est-ce qu'oracle fait le tri du fichier sans qu'on ne lui demande ?!

Citation:

Quel intérêt si ton fichier est déjà créé par un select ordonné

Là t'as raison, je ne sais pas pourquoi j'ai pas pensé à un order by au lieu de toutes ces lignes de code, merci :ccool: