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