Discussion :

[JQueen]

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 :

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

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

des suggestions ?

Merci d'avance

[McM]

C'est la bonne méthode si le fichier est sur le sur le serveur de base et que tu l'ouvres avant.

[JQueen]

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

[Scriuiw]

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

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

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

[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..

[JQueen]

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 :

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
 
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

[mnitu]

...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.

[Scriuiw]

Code :

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

INSERT INTO Avion () VALUES (buffer);

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

[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 :

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

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

Ton code serait plus comme ceci :

Code :

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

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.

[JQueen]

Bonjour,

Ç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.

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 ...

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

J'ai effectué les changements et voilà 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
 
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

[JQueen]

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

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

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

Merci

[Scriuiw]

Tout dépend de l'outil que vous utilisez

En SQL+ il faut tapper

Code :

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

sho err

pour afficher les erreurs

Sinon

Code :

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

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 :

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

pe_fichier IN VARCHAR2

[Scriuiw]

Code :

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

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 :

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

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

L'INSERT est mal codé aussi

Et j'oubliais...

Code :

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

commit;

Pas bien !!!

[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 :

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 :

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

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

[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

[JQueen]

Mon fichier est le résultat de ce script :

Code :

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

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.

[McM]

une tabulation c'est un chr(9)
pour le savoir, il suffit de faire un

Code :

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

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 :

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

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.

[mnitu]

...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

[JQueen]

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 :

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

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 :

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
 
-- 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

[Scriuiw]

Y a t-il une raison spécifique à ce que tu fasses deux lectures de fichier par boucle ?

Code :

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

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 ?