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 ?

Exécution procédure avec curseur et UTL_FILE

PL/SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager