Discussion :

[M.Aicha]

Bonjour,

Je viens de débuter en pl sql, et je cherche a crée une procedure stockée qui prend en parametre un fichier csv, parcour le fichier ligne par ligne et insert les donnée dans une table temporaire .
voici le debut e 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
 
CREATE OR REPLACE PROCEDURE LIAISON_TM(LiaisonServicesRateplan IN VARCHAR2) IS
      -- declaration des constantes, variables
    fich_resultat UTL_FILE.FILE_TYPE;
  num1 Integer;
  i Integer;
    pos_fich1 Integer;
  position Integer;
  anc_position Integer;
  champ varchar2(20);
    chaine1 varchar2(20);
  buffer1 varchar2(1500);
  echange boolean;
  p_directory VARCHAR2(20);
  cursor mpulktmb_cur IS SELECT TMCODE FROM mpulktmb ORDER BY TMCODE;
 
 BEGIN
    -- creation du descripteur du fichier
    fich_resultat := UTL_FILE.FOPEN ('RATING_LOG', 'LiaisonServicesRateplan', 'r');
 
 while echange loop
         echange := true;
        loop
              begin
              --Lecture d une ligne
                   UTL_FILE.GET_LINE(fich_resultat, buffer1);
             --buffer1= variable destiné a recevoir les données lues
              exception
                    when no_data_found then exit;
            end;
           --extraction du premier champ de chaque ligne
          pos_fich1 := instr (buffer1, ';');--Recherche dans buffer1 la position de ' ;  '
                     chaine1 := substr (buffer1, 0, pos_fich1 -1); --Extrait de buffer1, les caractères situés à partir du rang 0 jusqu'à la longueur pos_fich1 -1
                    num1 := to_number(chaine1);
 
           end loop;
end loop;    
            loop
                begin
                  UTL_FILE.GET_LINE(fich_resultat, buffer1);
                  exception
                    when no_data_found then exit;
                 end;
             end loop;
              commit;
 
          UTL_FILE.FCLOSE(fich_resultat);
 
END LIAISON_TM;

Merci d avance de votre aide

[al1_24]

Quelle est la question ?
Tu rencontres une erreur ? une partie de ton code ne fonctionne pas ?

Donne nous un peu plus de détails...

[M.Aicha]

Bonjour,
Merci d'avoir pris le temps de répondre à ma demande,
En fait, j'ai carrément changé de code et maintenant j'ai un message d'erreur qui est le suivant :

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

j'ai cherché un peu sur le net, et apparemment c'est un problème de taille du buffer
j'ai ajouté cette ligne à mon code : DBMS_OUTPUT.ENABLE( 1000000 ); mais c'est toujours le même problème.

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
CREATE OR REPLACE
PROCEDURE LIAISON_TM(
 p_filename IN VARCHAR2,
 p_directory IN VARCHAR2,
 p_ignore_headerlines IN INTEGER DEFAULT 1,
 p_delimiter IN VARCHAR2 DEFAULT ';')
IS
 v_filehandle UTL_FILE.file_type;
 v_text VARCHAR2(32767);
 v_eof BOOLEAN := FALSE;
 v_fields DBMS_SQL.varchar2a;
 v_field_index INTEGER;
 v_length INTEGER;
 v_start INTEGER;
 v_index INTEGER;
 v_enclosed_start INTEGER;
 v_enclosed_end INTEGER;
 
BEGIN
 
--DBMS_OUTPUT.ENABLE( 1000000 ) ;
 v_filehandle := UTL_FILE.fopen('RATING_LOG','LiaisonServicesRateplan.csv', 'r',32767);
 
IF p_ignore_headerlines > 0
 THEN
 BEGIN
 FOR i IN 1 .. p_ignore_headerlines
 LOOP
 UTL_FILE.get_line(v_filehandle, v_text);  
 END LOOP;
 
 EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
 v_eof := TRUE;
 END;
 END IF;
 
WHILE NOT v_eof
 LOOP
 BEGIN
 UTL_FILE.get_line(v_filehandle, v_text);
 DBMS_OUTPUT.put_line('v_text=' || v_text);
 v_fields.DELETE;
 v_field_index := 0;
 v_length := LENGTH(v_text);
 v_start := 1;
 v_enclosed_start := INSTR(v_text, ';', 1);
 v_enclosed_end := INSTR(v_text, ';', v_enclosed_start + 1);
 
WHILE (v_start <= v_length)
 LOOP
 v_index := INSTR(v_text, p_delimiter, v_start);
 
IF v_enclosed_end != 0
 AND v_index > v_enclosed_start
 AND v_index < v_enclosed_end
 THEN
 v_index := INSTR(v_text, p_delimiter, v_enclosed_end);
 v_enclosed_start := INSTR(v_text, ';', v_enclosed_end + 1);
 
IF v_enclosed_start != 0
 THEN
 v_enclosed_end :=INSTR(v_text, ';', v_enclosed_start + 1);
 END IF;
 END IF;
 
IF v_index = 0
 THEN
 v_fields(v_field_index) :=TRIM(LTRIM(RTRIM(SUBSTR(v_text, v_start), ';'), ';'));
 v_start := v_length + LENGTH(p_delimiter);
 ELSE
 v_fields(v_field_index) :=TRIM(LTRIM(RTRIM(SUBSTR(v_text, v_start, v_index - v_start),';'),';'));
 v_start := v_index + LENGTH(p_delimiter);
 END IF;
 
v_field_index := v_field_index + 1;
 END LOOP;
 
 INSERT
 INTO temp12(vShDes_sn, vShdes_sp, vAcceSsFee, vSubScript, vCsind, vSrvInd, vAdvInd, vSusInd, vPrintSubsCrind)
 VALUES (v_fields(0),v_fields(1),v_fields(2),v_fields(3),v_fields(4),v_fields(5),v_fields(6),v_fields(7),v_fields(8));
 
 EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
 v_eof := TRUE;
 
WHEN OTHERS
 THEN
 
 DBMS_OUTPUT.put_line(SQLERRM);
 DBMS_OUTPUT.put_line(v_text);
 
 END;
 END LOOP;
 
UTL_FILE.fclose(v_filehandle);
 
 
DECLARE
 P_FILENAME VARCHAR2(200):= 'LiaisonServicesRateplan.csv';
 P_DIRECTORY VARCHAR2(200):= 'RATING_LOG';
 P_IGNORE_HEADERLINES NUMBER := 1;
 BEGIN
 LIAISON_TM( P_FILENAME => P_FILENAME, P_DIRECTORY => P_DIRECTORY, P_IGNORE_HEADERLINES => P_IGNORE_HEADERLINES );
END;
 
 
END LIAISON_TM;

Voici le contenu de mon fichier CSV ::

FE;VSERV1;1;1;Y;T;Z;Y;Y
FR;VSERV2;1;1;Y;T;Z;Y;Y
FF;VSERV3;1;1;Y;T;Z;Y;Y
FG;VSERV4;1;1;Y;T;Z;Y;Y

Un coup de main de votre côté est le bienvenu

Merci d'avance.

[al1_24]

Qu'est censé faire ton programme ?
A quelle ligne du programme rencontres-tu ton erreur ? A quelle ligne du fichier ?
Peut-être qu'en indentant tes lignes de code et en y ajoutant des commentaires, sa structure apparaitrait mieux...

[mnitu]

Le plus simple sera de créer une table externe pour lire le fichier des données et de réduire la procédure à un simple

Code :

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

1
2
3
4
 
Insert into table ...
Select *
  From table_externe

[M.Aicha]

Bonjour,

A propos, je cherche a crée une procédure stockée qui prend en paramètre un fichier csv, parcourt le fichier ligne par ligne et insert les données dans une table.
J'ai pensé effectivement a la création d'une table temporaire''temp12'', en voici la requête:

Code :

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

1
2
3
4
 
 INSERT
 INTO temp12(vShDes_sn, vShdes_sp, vAcceSsFee, vSubScript, vCsind, vSrvInd, vAdvInd, vSusInd, vPrintSubsCrind)
 VALUES (v_fields(0),v_fields(1),v_fields(2),v_fields(3),v_fields(4),v_fields(5),v_fields(6),v_fields(7),v_fields(8));

En faisant une sélection sur la table temporaire 'temp12' aucune donnée insérée, mais par contre j'ai un message d'erreur au niveau de ligne 22 qui est le suivant : "ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes"

Comme j'avais expliqué hier, apparemment c'est un problème de taille du buffer
j'ai ajouté cette ligne à mon code : DBMS_OUTPUT.ENABLE( 1000000 ); mais c'est toujours le même problème.

Auriez vous une solution pour résoudre le problème du Buffer?

Merci d'avance.

[mnitu]

Voici un exemple qui utilise les tables externes (dans la suite il a été supposé que l’objet directory TEMP existe et que les privilèges appropriés ont été attribué. Le fichier mon_fich.csv contient les données de votre exemple.)

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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> create table ext_fich_csv (
  2    cod          varchar2(2),
  3    service      varchar2(6),
  4    attrib1      number(1),
  5    attrib2      number(1),
  6    attrib3      varchar2(1),
  7    attrib4      varchar2(1),
  8    attrib5      varchar2(1),
  9    attrib6      varchar2(1),
 10    attrib7      varchar2(1)
 11  )
 12  organization external
 13  ( type oracle_loader
 14    default directory TEMP
 15    access parameters ( records delimited by newline
 16                        badfile     'ext_fich_csv.bad'
 17                        logfile     'ext_fich_csv.log'
 18                        FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"' LDRTRIM
 19                        MISSING FIELD VALUES ARE NULL
 20                        REJECT ROWS WITH ALL NULL FIELDS
 21                        (
 22                          cod,
 23                          service,
 24                          attrib1,
 25                          attrib2,
 26                          attrib3,
 27                          attrib4,
 28                          attrib5,
 29                          attrib6,
 30                          attrib7
 31                        )
 32                      )
 33    location ('nom.csv')
 34  )
 35  /
 
Table created
SQL> create table test_lec_fich_csv as
  2  Select *
  3    From ext_fich_csv
  4   Where 1= 2
  5  /
 
Table created
SQL> Create or Replace Procedure Lec_Fich_Csv (
  2    nom_fic         In varchar2
  3  ) Is
  4    sql_stmt constant varchar2(100) := Replace('alter table ext_fich_csv location (''@nom_fic@'')','@nom_fic@',nom_fic);
  5  Begin
  6    Execute Immediate sql_stmt;
  7    --
  8    Insert Into test_lec_fich_csv
  9           (
 10              cod,
 11              service,
 12              attrib1,
 13              attrib2,
 14              attrib3,
 15              attrib4,
 16              attrib5,
 17              attrib6,
 18              attrib7
 19           )
 20    Select  cod,
 21            service,
 22            attrib1,
 23            attrib2,
 24            attrib3,
 25            attrib4,
 26            attrib5,
 27            attrib6,
 28            attrib7
 29      From  ext_fich_csv;
 30  End;
 31  /
 
Procedure created
SQL> exec Lec_Fich_Csv('mon_fich.csv');
 
PL/SQL procedure successfully completed
SQL> Select *
  2    From test_lec_fich_csv
  3  /
 
COD SERVICE ATTRIB1 ATTRIB2 ATTRIB3 ATTRIB4 ATTRIB5 ATTRIB6 ATTRIB7
--- ------- ------- ------- ------- ------- ------- ------- -------
FE  VSERV1        1       1 Y       T       Z       Y       Y
FR  VSERV2        1       1 Y       T       Z       Y       Y
FF  VSERV3        1       1 Y       T       Z       Y       Y
FG  VSERV4        1       1 Y       T       Z       Y       Y
 
SQL>

[M.Aicha]

Bonjour mnitu,

Merci pour votre aide.
Comme indice, j'utilise Oracle 11g et PL/SQL, la table externe a bien été créée, mais j'ai toujours le message d'erreur suivant :

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 32
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 97
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 112
ORA-06512: à "SYSADM.LIAISON_TM", ligne 113
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à ligne 12
FE;VSERV1;1;1;Y;T;Z;Y;Y
FR;VSERV2;1;1;Y;T;Z;Y;Y
FF;VSERV3;1;1;Y;T;Z;Y;Y
FG;VSERV4;1;1;Y;T;Z;Y;Y

j'ai ajouté: dbms_output.enable (1000000);
mais ça n’aboutit a rien .

Sinon, dite moi, quel est l’intérêt de la procédure suivante :

Code :

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

1
2
3
4
5
6
7
 
CREATE OR REPLACE Procedure Lec_Fich_Csv (
  2    nom_fic         IN varchar2
  3  ) IS
  4    sql_stmt constant varchar2(100) := REPLACE('alter table ext_fich_csv location (''@nom_fic@'')','@nom_fic@',nom_fic);
  5  Begin
  6    Execute Immediate sql_stmt;

Merci.

[mnitu]

...
Sinon, dite moi, quel est l’intérêt de la procédure suivante :

Elle permet de changer le nom du fichier à lire.

[M.Aicha]

Merci, sommes nous obligé de changer le nom de fichier ou pas ?

[mnitu]

Code :

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

1
2
 
...et je cherche a crée une procedure stockée qui prend en parametre un fichier csv...

implique que le nom du fichier n'est pas toujours le même.

[M.Aicha]

Merci pour l'information

Auriez vous une idée a propos du message d'erreur suivant:

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 32
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 97
ORA-06512: à "SYS.DBMS_OUTPUT", ligne 112
ORA-06512: à "SYSADM.LIAISON_TM", ligne 113
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à "SYSADM.LIAISON_TM", ligne 137
ORA-06512: à ligne 12
FE;VSERV1;1;1;Y;T;Z;Y;Y
FR;VSERV2;1;1;Y;T;Z;Y;Y
FF;VSERV3;1;1;Y;T;Z;Y;Y
FG;VSERV4;1;1;Y;T;Z;Y;Y

j'ai ajouté: dbms_output.enable (1000000);
mais ça n’aboutit a rien .

[mnitu]

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes

L'erreur indique une limite de 20000 (taille par défaut). Vérifiez donc tous les appels à dbms_output.enable.

[M.Aicha]

Oui, je suis tout a fait d'accord avec vous, c'est pour cette raison que j'ai ajouté a mon code : dbms_output.enable (1000000);

mais le problème persiste toujours.

[Zyniel]

Le package DBMS_OUTPUT est un package qui a pour vocation de permettre de laisser des traces de débogage.
Pour conséquence, il a longtemps été nécessaire de paramétrer une taille de buffer pour cette fonctionnalité.

Je le souligne, DBMS_OUTPUT fonctionne avec un principe de mémoire tampon. Toute votre sortie est gardée dans une mémoire tampon et sera restituée à la fin de l'exécution. Il n'est pas possible de la purger en cours de traitement.
Ce mécanisme diffère des mécanismes de types Console des autres langages, auxquels vous avez surement tenté de vous raccrocher.

Pour plus d'informations, aller consulter : http://docs.oracle.com/cd/E11882_01/...5/d_output.htm

Votre problème est que votre buffer de sortie a une taille limite qui est inférieure au total des caractères que vous tentez de restituer via votre procédure PL/SQL qui insère dans un fichier.

Deux options :
- Vous configurez votre SERVEROUTPUT proprement pour ne pas avoir de taille limite. (CF Doc et passage sur SET SERVEROUTPUT ON ~ dbms_output.enable(NULL);
- Vous ne faites pas de sortie visuelle inutile et évitez ainsi d'avoir une erreur de dépassement de capacité.

[mnitu]

Oui, je suis tout a fait d'accord avec vous, c'est pour cette raison que j'ai ajouté a mon code : dbms_output.enable (1000000);

mais le problème persiste toujours.

If there are multiple calls to ENABLE, then buffer_size is the last of the values specified. The maximum size is 1,000,000 and the minimum is 2000 when the user specifies buffer_size (NOT NULL).