IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PL/SQL Oracle Discussion :

Exécution procédure avec curseur et UTL_FILE


Sujet :

PL/SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre confirmé
    Inscrit en
    Juillet 2005
    Messages
    126
    Détails du profil
    Informations forums :
    Inscription : Juillet 2005
    Messages : 126
    Par défaut Exécution procédure avec curseur et UTL_FILE
    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

  2. #2
    Membre confirmé
    Inscrit en
    Juillet 2005
    Messages
    126
    Détails du profil
    Informations forums :
    Inscription : Juillet 2005
    Messages : 126
    Par défaut
    j'ai aussi regardé la valeur de cpt qui est 0 donc c'est dès le premier passage que ça plante

  3. #3
    Membre émérite Avatar de Z3phur
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Décembre 2007
    Messages
    680
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Industrie

    Informations forums :
    Inscription : Décembre 2007
    Messages : 680
    Par défaut
    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.

  4. #4
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    C'est la longueur du champ qu'il veut, pas la longueur du nom de la colonne.

  5. #5
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    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';

  6. #6
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    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 ?

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 2
    Dernier message: 17/10/2013, 22h28
  2. Exécuter procédure avec Select en entrée
    Par CinePhil dans le forum PL/SQL
    Réponses: 20
    Dernier message: 04/07/2012, 18h07
  3. Lenteur d'exécution d'une fonction (avec curseur)
    Par labolabs dans le forum PL/SQL
    Réponses: 8
    Dernier message: 07/06/2011, 07h29
  4. Procédure avec curseur générique
    Par nemo67 dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 31/03/2010, 13h45
  5. Réponses: 9
    Dernier message: 18/07/2007, 11h34

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo