Exécution procédure avec curseur et UTL_FILE

**Cathy** · 19/08/2008, 10h47

bonjour, je travaille sur ORACLE 9i et j'ai une erreur lorsque j'exécute la procédure suivante :

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
111
112
113
114
115
 
CREATE OR REPLACE PROCEDURE toto (OUTPUTFILELOCATION IN VARCHAR2, OUTPUTFILENAME IN VARCHAR2) IS
 
	   -- OUTPUT FILE
	   OUTPUTFILEID UTL_FILE.FILE_TYPE;
 
	   -- SYSTEM TABLE CURSOR
	   CURSOR C_TABLENAME IS 
	   SELECT TABLE_NAME, COLUMN_NAME, DATA_LENGTH 
			  FROM ALL_TAB_COLUMNS 
			  WHERE TABLE_NAME LIKE 'toto%'
			  AND DATA_TYPE = 'VARCHAR2'
			  AND COLUMN_NAME != 'C_OPERATION'
			  ORDER BY TABLE_NAME;
 
	   TYPE   		   	 T_CURTYP IS REF CURSOR;
  	   DATA        	 T_CURTYP;
  	   COLUMNDATA     VARCHAR2(32676);
	   REQCOUNTSPECAR VARCHAR2(32676);
	   SUMNBCAR 		 VARCHAR2(32676);
	   MSG			 VARCHAR2(500);
           MailID         NUMBER;
           cpt            NUMBER;
           PARAM_FILE     VARCHAR2(200);
 
BEGIN
         OUTPUTFILEID := UTL_FILE.FOPEN(OUTPUTFILELOCATION,OUTPUTFILENAME,'W',5000);
         cpt := 0;
	 -- CURSOR ON TABLES
	 FOR TABS IN C_TABLENAME
  	 LOOP
 
	     -- SPECIAL CHARACTER COUNT
             SUMNBCAR := '(NVL(LENGTH(' || TABS.COLUMN_NAME || '),0)- NVL(LENGTH(REPLACE(' || TABS.COLUMN_NAME || ',CHR(128),'''')),0))  ';
	     FOR J IN 129 .. 255 LOOP
		SUMNBCAR := SUMNBCAR || '+ (NVL(LENGTH(' || TABS.COLUMN_NAME || '),0) - NVL(LENGTH(REPLACE('||TABS.COLUMN_NAME||',CHR(' || J || '),'''')),0))';
	     END LOOP;
MSG := 'yo1';						
	     -- MAIN SQL QUERY
	     REQCOUNTSPECAR := 'SELECT ' || TABS.COLUMN_NAME || ' , ' || SUMNBCAR || ' AS NBSPC  ' ||
					' FROM ' || TABS.TABLE_NAME || 
					' WHERE D_FLAG_CMH IS NULL '||
				  	' GROUP BY ' || TABS.COLUMN_NAME || 
					' HAVING ' || SUMNBCAR || ' + NVL(LENGTH('||TABS.COLUMN_NAME||'),0)  > ' || TABS.DATA_LENGTH ;
MSG := 'yo2';	
	     -- CURSOR ON CURRENT TABLE
	     OPEN DATA FOR REQCOUNTSPECAR ;
	     MSG := 'yo21';
	     LOOP
MSG := 'yo211';
       	 	 FETCH DATA INTO COLUMNDATA;
       	 	 	     MSG := 'yo22';
                 EXIT WHEN DATA%NOTFOUND;
                        	 	 	     MSG := 'yo23';
		      DBMS_OUTPUT.PUT_LINE(TABS.TABLE_NAME||';'||TABS.COLUMN_NAME || ';' || COLUMNDATA);
      	 	 	     MSG := 'yo24';
                 -- WRITE LINE into FILE
		 UTL_FILE.PUT_LINE(OUTPUTFILEID,TABS.TABLE_NAME||';'||TABS.COLUMN_NAME || ';' || COLUMNDATA || CHR(13));
		      	 	 	     MSG := 'yo25';
                 cpt := cpt + 1;
	     MSG := 'yo26';
	      END LOOP;
	          MSG := 'yo27';
	      CLOSE DATA;
	          MSG := 'yo28';
          END LOOP;	
              MSG := 'yo29';
          -- Close output file
          UTL_FILE.FCLOSE (OUTPUTFILEID);
MSG := 'yo3';	  
          -- Insert mail request
          IF cpt <> 0 THEN 
             BEGIN
                 SELECT SQ_SS10_MAIL.NEXTVAL INTO MailID FROM DUAL;
                 PARAM_FILE := OUTPUTFILELOCATION || '/' || OUTPUTFILENAME;
 
                 INSERT INTO SS10_EM_MAIL_REQUEST (
                    C_REQUEST_NO, C_MAIL_ID, C_MAIL_STATUS,
                    D_REQUEST, D_STATUS,
                    C_PARAM_1, C_PARAM_2, C_PARAM_3, C_PARAM_4, C_PARAM_5,
                    C_LIG_1,C_LIG_2)
                    VALUES (MailID, '08', '0',SYSDATE, SYSDATE,'toto','0', '0', '0',
                            OUTPUTFILELOCATION || '/' || OUTPUTFILENAME,
                            'Detection of special characters in toto tables',
			    'For more details, see attached file');
                 COMMIT;
             END;
          END IF;
EXCEPTION
         WHEN UTL_FILE.INVALID_PATH THEN
              MSG := OUTPUTFILELOCATION || '/' || OUTPUTFILENAME || ' FILE LOCATION IS INVALID.';
              RAISE_APPLICATION_ERROR( -20070, MSG ) ;
         WHEN UTL_FILE.INVALID_MODE THEN
              MSG := OUTPUTFILELOCATION || '/' || OUTPUTFILENAME || ' THE OPEN_MODE PARAMETER IN FOPEN IS INVALID.';
              RAISE_APPLICATION_ERROR( -20070, MSG ) ;
         WHEN UTL_FILE.INVALID_FILEHANDLE THEN
              MSG := OUTPUTFILELOCATION || '/' || OUTPUTFILENAME || ' FILE HANDLE IS INVALID.';
              RAISE_APPLICATION_ERROR( -20070, MSG ) ;
         WHEN UTL_FILE.INVALID_OPERATION        THEN
              MSG := OUTPUTFILELOCATION || '/' || OUTPUTFILENAME || ' FILE COULD NOT BE OPENED OR OPERATED ON AS REQUESTED.';
              RAISE_APPLICATION_ERROR( -20070, MSG ) ;
         WHEN UTL_FILE.READ_ERROR       THEN
              MSG := OUTPUTFILELOCATION || '/' || OUTPUTFILENAME || ' OPERATING SYSTEM ERROR OCCURRED DURING THE READ OPERATION.';
              RAISE_APPLICATION_ERROR( -20070, MSG ) ;
         WHEN UTL_FILE.WRITE_ERROR THEN
              MSG := OUTPUTFILELOCATION || '/' || OUTPUTFILENAME || ' OPERATING SYSTEM ERROR OCCURRED DURING THE WRITE OPERATION.';
              RAISE_APPLICATION_ERROR( -20070, MSG ) ;
         WHEN UTL_FILE.INTERNAL_ERROR THEN
              MSG := OUTPUTFILELOCATION || '/' || OUTPUTFILENAME || ' UNSPECIFIED PL/SQL ERROR';
              RAISE_APPLICATION_ERROR( -20070, MSG ) ;
         WHEN OTHERS THEN
--              MSG := OUTPUTFILELOCATION || '/' || OUTPUTFILENAME || ' UNSPECIFIED PL/SQL ERROR';
              RAISE_APPLICATION_ERROR( -20070, MSG ) ;
END;
/

ERREUR :

ORA-20070: yo211
ORA-06512: à "toto", ligne 111
ORA-06512: à ligne 9

Dans ce message d'erreur le yo211 nous montre que le programme s'est planté sur le

FETCH DATA INTO COLUMNDATA;

bien entendu ce n'est pas moi qui ai écrit ce programme, sachant que celui-ci fonctionne sur une autre base (8i).
Une autre différence entre les 2 bases serait que mon user 8i est DBA contrairement au user 9i.

Pouvez-vous m'aider svp?

Cat

**Cathy** · 19/08/2008, 11h26

j'ai aussi regardé la valeur de cpt qui est 0 donc c'est dès le premier passage que ça plante

**Z3phur** · 19/08/2008, 11h52

Salut,

juste pour faire un essai, au lieu de faire

-- MAIN SQL QUERY
REQCOUNTSPECAR := 'SELECT ' || TABS.COLUMN_NAME || ' , ' || SUMNBCAR || ' AS NBSPC ' ||
' FROM ' || TABS.TABLE_NAME ||
' WHERE D_FLAG_CMH IS NULL '||
' GROUP BY ' || TABS.COLUMN_NAME ||
' HAVING ' || SUMNBCAR || ' + NVL(LENGTH('||TABS.COLUMN_NAME||'),0) > ' || TABS.DATA_LENGTH ;

pourquoi ne pas faire un curseur avec paramètre :

Code :

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

1
2
3
4
5
6
cursor c_reqcountspecar ( p_column_name varchar2, p_table_name varchar2, p_sumnbcar varchar2, p_data_length integer) is
    select p_column_name , p_sumnbcar as nbspc
    from p_table_name 
    where d_flag_cmh is null 
    group by p_column_name 
    having p_sumnbcar + nvl(length(p_column_name),0)  > p_data_length;

et après ouvrir ce curseur normalement pour voir si tu passe dedans.

**McM** · 19/08/2008, 12h26

Code :

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

length(p_column_name)

C'est la longueur du champ qu'il veut, pas la longueur du nom de la colonne.

**McM** · 19/08/2008, 13h03

Voici déjà une amélioration du code (Remplacer 128 REPLACE par 1 TRANSLATE).
Ca va t'éviter le dépassement des 32676 caractères

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
v_lstspec	VARCHAR2(255);
 
BEGIN
outputfileid := UTL_FILE.FOPEN(outputfilelocation,outputfilename,'W',5000);
cpt := 0;
 
FOR j IN 128 .. 255 
LOOP
	v_lstspec :=  v_lstspec || CHR(j);
END LOOP;
 
-- cursor on tables
FOR tabs IN (SELECT table_name, column_name, data_length 
			FROM all_tab_columns 
			WHERE table_name LIKE 'toto%'
			AND data_type = 'VARCHAR2'
			AND column_name != 'C_OPERATION'
			ORDER BY table_name)
LOOP
 
	-- special character count
	sumnbcar := '(NVL(LENGTH(' || tabs.column_name || '),0) - NVL(LENGTH(TRANSLATE('||tabs.column_name ||',''0'''|| v_lstspec || ',''0'')),0))';
 
	msg := 'yo1';						
	-- main sql query
	reqcountspecar := 'SELECT ' || tabs.column_name || ' , ' || sumnbcar || ' AS NBSPC  ' ||
	' FROM ' || tabs.table_name || 
	' WHERE D_FLAG_CMH IS NULL '||
	' GROUP BY ' || tabs.column_name || 
	' HAVING ' || sumnbcar || ' + NVL(LENGTH('||tabs.column_name||'),0)  > ' || tabs.data_length ;
	msg := 'yo2';

**McM** · 19/08/2008, 13h09

Code :

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

1
2
3
4
5
6
7
8
DECLARE
TYPE   		   	 T_CURTYP IS REF CURSOR;
  	   DATA        	 T_CURTYP;
  	   COLUMNDATA     VARCHAR2(32676);
BEGIN
OPEN DATA FOR REQCOUNTSPECAR ; -- SELECT column_name, nbcar from ...
 LOOP
      	 	 FETCH DATA INTO COLUMNDATA;

Y'a un problème là ;
data contient 2 colonnes
columndata est un varchar2
L'un ne peut pas rentrer dans l'autre.
Et tu dis que ça marche sur 8i ?

**Cathy** · 19/08/2008, 13h37

oui pas de doute, ça marche sur 8i avec exactement le même code !!

c'est pourquoi je me demande s'il ne me manque pas de grant ou si le fameux

TYPE T_CURTYP IS REF CURSOR;

est toujours possible en 9i.

Je ne sais pas quel rôle joue le

Code :

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

FETCH DATA INTO COLUMNDATA;

qui permettrai peut-être de faire colonne par colonne...

**Cathy** · 19/08/2008, 13h51

Z3PHUR, je n'ai pas réussi à tester ta méthode...j'avoue avoir quelques difficultés avec les curseurs dynamiques...

est-ce que ceci est juste :

Code :

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

1
2
3
4
5
6
7
8
 
FOR DATA IN c_reqcountspecar( TABS.COLUMN_NAME, TABS.TABLE_NAME, SUMNBCAR, TABS.DATA_LENGTH)
 
LOOP
 
...
 
END LOOP;

car je suis bien dans ma boucle du curseur :

FOR TABS IN C_TABLENAME
LOOP

**McM** · 19/08/2008, 13h58

Pour moi le champ NBSPC a été rajouté dans la requête
Il n'a je pense aucun intérêt.

Sinon rajoute aussi un WHERE tabs.column_name IS NOT NULL, il n'y aura jamais de caractères spéciaux dans ce cas.

Code :

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

1
2
3
4
5
6
7
reqcountspecar := 
'SELECT ' || tabs.column_name || 
	' FROM ' || tabs.table_name || 
	' WHERE D_FLAG_CMH IS NULL '||
	' AND '|| tabs.column_name ||' IS NOT NULL '||
	' GROUP BY ' || tabs.column_name || 
	' HAVING ' || sumnbcar || ' + NVL(LENGTH('||tabs.column_name||'),0)  > ' || tabs.data_length ;

Dernière chose, sumnbcar donne bien le nb de caractères spéciaux.
Je vois pas à quoi sert le HAVING ?
nb carac spéciaux + taille totale > taille maxi du champ.

Ca veut dire que sur un champ de 50, pour une chaine de 40 caractères, il faut plus de 10 caractères spéciaux pour que ça ressorte dans la requête ?

**McM** · 19/08/2008, 14h00

Envoyé par Cathy

Z3PHUR, je n'ai pas réussi à tester ta méthode...j'avoue avoir quelques difficultés avec les curseurs dynamiques...

Oublie cette méthode, elle ne marchera pas, du fait que certains paramètres contiennent des noms de colonnes.

**Cathy** · 19/08/2008, 14h38

Bravo McM, j'ai suivi tes conseils en enlevant le champ NBSPC de la requête et je n'ai plus d'erreur...

Par contre je suis incapable d'expliquer pourquoi l'ancien code fonctionne sous 8i, si un jour je trouve la réponse je mettrai à jour cette discussion...

En tout cas je te remercie bcp pour ton aide.

merci aussi à toi Z3phur ;-)

Biz

Exécution procédure avec curseur et UTL_FILE

PL/SQL Oracle

Discussions similaires

Partager

Partager