Exécuter des ordres PL/SQL dynamiquement ?

**Andre.lissarrague** · 08/11/2013, 19h17

Bonjour,

je bute sur un petit problème :::

je vais essayer d'expliquer clairement mon problème et son contexte.

Je veux mettre à jour une table (de gestion) à partir des données d'une autre table (Temporaire réceptrice d'un flux).
Bien sur

le nom des colonnes dans les deux tables ne sont pas systématiquement les mêmes, ni les tailles ....

Du coup, dans un package PL/SQL j'ai :

une fonction qui me renvoie une table PL/SQL (T_Asso) de record avec :
- - le nom (T_Asso(Num_lign).Col_Membre) et la taille de colonne de table d'origine,
- - le nom et la taille de colonne de table de destination,
- - la différence de taille entre les deux colonnes (origine - destination)
une procédure d'intégration qui :
- 1. récupère la table PL/SQL de correspondance entre les colonnes des tables d'origine et de destination
  2. lit les valeurs de la table d'origine via un curseur
  3. et (je voudrais, mais je peux point ...) pour chaque ligne lue de la table d'origine,
    Tronquer les valeurs trop longues à la taille de la colonne de la table de destination.

Voici le petit algo que j'ai imaginé pour détecter et tronquer les valeurs trop longues :
je lis le tableau de correspondance
pour chaque ligne lue,

Si la différence de taille des colonnes d'origine et destination est positive, alors
Si la longueur (nombre de caractère) de la valeur d'origine est supérieure à la taille de la colonne d'origine et destination
Alors je tronque la valeur
Sinon touv a bien , je fais rien !

Là où je butte, c'est comment "sélectionner" la colonne du curseur (contenant les valeurs d'origine) en fonction de la ligne de ma table PL/SQL.
en gros arriver à exécuter un ordre PL/SQL semblable à :

Code :

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

1
2
3
4
 
If length(trim(Rec_Membre_Courant.' || T_Asso(Num_lign).Col_Membre ||' ))  > Table_Asso(Num_lign).Dim_P  Then      -- I2
              Rec_Membre_Courant.' || T_Asso(Num_lign).Col_Membre ||' := Substr(trim(Rec_Membre_Courant. ' || T_Asso(Num_lign).Col_Membre ||') , 1, Table_Asso(Num_lign).Dim_P ) ;
            End If ;

avec :
définition de la table Pl/SQL d'association :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
-- pour associer les colonnes de MEMBRE_TEMP à PERSONNE
    Type Asso_RecTYP Is Record (Col_Membre Varchar2(255), Col_Personne Varchar2(255) ,
                                dim_M Integer, dim_P Integer, diff_lng Integer) ;
    Type Table_Asso_TYP Is Table Of Asso_RecTYP 
                            Index By Binary_Integer ;
 
   -- Tableau de correspondance entre MEMBRE_TEMP à PERSONNE
    T_Asso Table_Asso_TYP ;
    -- Indice des lignes du tableau Table_Asso
     Num_lign integer ;

procédure d'initialisation du tableau :

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
 
Function genere_table_ASSO Return Table_Asso_TYP 
 
Is
 
    -- Tableau de correspondance entre MEMBRE_TEMP (origine) à PERSONNE (destination)
    Table_Asso Table_Asso_TYP ;
    -- Indice des lignes du tableau Table_Asso
     Num_lign integer ;
 
    Begin
     dbms_output.disable ;   
     dbms_output.enable (50000000) ;
 
    -- Initialialisation coorespondance entre la table MEMBRE_TEMP et a tabe PERSONNE
        Table_Asso(2).Col_Membre :=  'memcn_num' ;                   Table_Asso(2).Col_PERSONNE :=  'memcn_num' ;
        Table_Asso(3).Col_Membre :=  'matricule' ;                   Table_Asso(3).Col_PERSONNE :=  'matricule' ;
        Table_Asso(6).Col_Membre :=  'civilite' ;                     Table_Asso(6).Col_PERSONNE :=  'sigle' ;
        Table_Asso(7).Col_Membre :=  'nomusuel' ;                     Table_Asso(7).Col_PERSONNE :=  'nomusuel' ;
        Table_Asso(8).Col_Membre :=  'nom_patro' ;                    Table_Asso(8).Col_PERSONNE :=  'nom_patro' ;
        Table_Asso(9).Col_Membre :=  'prenom' ;                       Table_Asso(9).Col_PERSONNE :=  'prenom' ;
        Table_Asso(10).Col_Membre :=  'datnaiss' ;                   Table_Asso(10).Col_PERSONNE :=  'datnaiss' ;
 
        Num_lign := Table_Asso.first ;
        While Num_lign <= Table_Asso.last 
        Loop  -- on récupère les tailles des colonnes via le dictionnaire de données Oracle
         Begin 
          Select DATA_LENGTH Into Table_Asso(Num_lign).Dim_M  From User_Tab_Columns 
               Where TABLE_NAME = 'MEMBRE_TEMP' And COLUMN_NAME = UPPER(Table_Asso(Num_lign).Col_Membre) ;
          Select DATA_LENGTH Into Table_Asso(Num_lign).Dim_P  From User_Tab_Columns 
               Where TABLE_NAME = 'PERSONNE'  And COLUMN_NAME = UPPER(Table_Asso(Num_lign).Col_PERSONNE) ;  
    -- on calcule la différence de taille entre les colonnes d'origine et de destination
          Table_Asso(Num_lign).diff_lng := Table_Asso(Num_lign).Dim_M - Table_Asso(Num_lign).Dim_P ;
          Num_lign := Table_Asso.next(Num_lign) ;
 
      Exception
         When no_data_found Then 
           dbms_output.put_line ('données non trouvées pour nb_ligne = ' || Num_lign ) ; 
           Num_lign := Table_Asso.next(Num_lign) ;
        End ;   
      End Loop ;
 
  /*   -- pour tets unitaire seulement 
     dbms_output.put_line ('Vérif. résulata final' ) ;     
        Num_lign := Table_Asso.first ;
        While Num_lign <= Table_Asso.last 
        Loop
          dbms_output.put_line ('ligne ' || Num_lign || ' ; MEMBRE.' || Table_Asso(Num_lign).Col_Membre || ' ' || Table_Asso(Num_lign).Dim_M ||
                                ' ; Personne.' || Table_Asso(Num_lign).Col_PERSONNE || ' ' || Table_Asso(Num_lign).Dim_P ||
                                ' ; diff de longueur ' || Table_Asso(Num_lign).diff_lng) ;
           Num_lign := Table_Asso.next(Num_lign) ;
        End Loop ;
      
    */
 
   Return   Table_Asso ; 
 
End genere_table_ASSO ;

et extrait de procédure d'intégration :

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
 
Procedure Maj_Personne(Dat_Ref Date) Is
   --Pour stocker le numéro de personne à insérer
  v_Max_Personne Personne.Num_Personne%Type;
 
  -- Liste des personnes être importés 
Cursor Cur_Membres Is
    Select Distinct Me.* From Membre_Temp Me
	-- on ne prend en compte que les données du flux du jour
    Where Trunc(Met_Timestamp, 'dd') = Trunc(Dat_Ref, 'dd')
			;
 
  --Ligne du curseur CUR_MEMBRE : représente un membre à importer 
	Rec_Membre_Courant Cur_Membres%Rowtype;
 
		-- identifiant de la personne à mettre à jour
		Id_Personne Personne.Num_Personne%Type;
 
    -- Tableau de correspondance entre MEMBRE_TEMP à PERSONNE
    T_Asso Table_Asso_TYP ;
    -- Indice des lignes du tableau Table_Asso
     Num_lign integer ;
 
     Block_PL Varchar2(4000) ;
     val Varchar2(500) ;
     Lng Integer ;
 
Begin
 
   -- Initialisation correspondance entre la table MEMBRE_TEMP (origine) et la table PERSONNE (destination)
    T_Asso := pck_import_ambre.genere_table_asso;
 
	--Parcours de la table MEMBRE_TEMP (origine)
For Rec_Membre_Courant In Cur_Membres --  Boucle1
   Loop
	-- parcours cur_membres
	Begin
 
        -- traitement des valeurs trop grande
        Num_lign := T_Asso.first ;
        While Num_lign <= T_Asso.last 
        Loop
          If  T_Asso(Num_lign).diff_lng > 0 Then   -- I1
         /*   Block_PL := ' Begin 
             If length(trim(Rec_Membre_Courant.' || T_Asso(Num_lign).Col_Membre ||' ))  > Table_Asso(Num_lign).Dim_P  Then      -- I2
              Rec_Membre_Courant.' || T_Asso(Num_lign).Col_Membre ||' := Substr(trim(Rec_Membre_Courant. ' || T_Asso(Num_lign).Col_Membre ||') , 1, Table_Asso(Num_lign).Dim_P ) ;
            End If ;                                   --I2 
              END ; ' ;
            Execute Immediate Block_PL ;  */
            Block_pl := ' :Val := :Rec ; ' ;
            Execute Immediate Block_PL Using In Out VAL , 'Rec_Membre_Courant.' || T_Asso(Num_lign).Col_Membre ; 
            lng := length (Trim (val)) ;  
          End if ;                                     --I1
 
           Num_lign := T_Asso.next(Num_lign) ;
        End Loop ;
            -- traitement des enregistrements et MAJ de la table de destination
               ......
 
      	End; -- Bloc1         
		End Loop; -- parcours cur_membres  -- boucle1
 
 
	End Maj_Personne; -- BlocP

Vous pouvez les voir les différents essais que j'ai fais en SQL dynamique, mais aucun de fonctionne....

Donc si quelqu'un à une idée....
..je suis preneur.

En vous remerciant d'avance.

**draudrau** · 14/11/2013, 14h35

Bonjour,

j'avoue que je n'ai pas pris le temps de me plonger dans ton problème afin d'en comprendre toutes les ficelles, mais je pense que tu peux explorer les collections de type INDEX-BY TABLE.

exemples :

Code :

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

TYPE TAB_VARCHAR IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(30);

L'index correspond au nom de ta colonne. Cela te permet de récupérer la valeur d'un champ en fonction du nom du champ.

Ça devrait te faciliter la tâche.

Cdt.
Olivier

**Andre.lissarrague** · 15/11/2013, 14h41

Merci.

je vais essayer cela, dés que j'ai un peu de temps devant moi.

je vous tiens au courant (Pb

ou réussite

)

Exécuter des ordres PL/SQL dynamiquement ?

PL/SQL Oracle

Discussions similaires

Partager

Partager