[11gR2] Procédure générique pour insérer des valeurs dans une table

Procédure générique pour insérer des valeurs dans une table

Bonjour,

Je suis confronté à un problème depuis 2 jours que je n'arrive pas à résoudre.
Je souhaite créer une procédure stockée générique qui permettrait d'insérer des valeurs dans une table.

Exemple : P_INSERT ( Libelle_table,champ_table,val1,val2,.. val100)
Libelle_table = libellé de la table ou nous voulons insérer des données
champ_table = champ des tables séparés par ","
val1 à val100 : valeurs des données à insérer
Ne sachant pas combien de champs j'aurai dans la table à insérer j'ai mis une limite à 100. Val1 à val 100 ont pour valeur par defaut null. Je ne suis donc pas obligé d'utiliser les 100 valeurs.

L'algorithme que je souhaite utiliser est la suivante :
-----------------------------------------------------------------------
Mettre les valeurs à insérer dans un tableau
Déclaration d'un tableau. tab
$i=0
Tant que val($i) n'est pas nulle
$i=$i+1
tab(1) = val($i)
Fin tant que

En sortant de cette boucle, nous avons le nombre de valeur renseigné pour $i
Mettre les valeurs renseignées de cette maniere : val1,val2,val3,valn
$j=1
chaine_a_inserer = tab($j)
Pour $j de 2 à $i
chaine_a_inserer = chaine_a_inserer + ',' + tab($j)

EXECUTION 'INSERT INTO '||Libelle_table||' ('||champ_table ||') SELECT '||chaine_a_inserer|| ' FROM DUAL';
-----------------------------------------------------------------------
J'ai commencé le code ci-dessous, je n'ai pas encore géré les exceptions, je suis actuellement en mode debug avec les "DBMS_OUTPUT.PUT_LINE"

Code:

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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
CREATE OR REPLACE PROCEDURE "INSERT_UPDATE_TABLE" (p_LIB_TAB IN Varchar2,
p_LIB_VAL IN Varchar2,
p_champ1 IN Varchar2 DEFAULT NULL,
p_champ2 IN Varchar2 DEFAULT NULL,
p_champ3 IN Varchar2 DEFAULT NULL,
p_champ4 IN Varchar2 DEFAULT NULL,
p_champ5 IN Varchar2 DEFAULT NULL,
p_champ6 IN Varchar2 DEFAULT NULL,
p_champ7 IN Varchar2 DEFAULT NULL,
p_champ8 IN Varchar2 DEFAULT NULL,
p_champ9 IN Varchar2 DEFAULT NULL,
p_champ10 IN Varchar2 DEFAULT NULL,
p_champ11 IN Varchar2 DEFAULT NULL,
p_champ12 IN Varchar2 DEFAULT NULL,
p_champ13 IN Varchar2 DEFAULT NULL,
p_champ14 IN Varchar2 DEFAULT NULL,
p_champ15 IN Varchar2 DEFAULT NULL,
p_champ16 IN Varchar2 DEFAULT NULL,
p_champ17 IN Varchar2 DEFAULT NULL,
p_champ18 IN Varchar2 DEFAULT NULL,
p_champ19 IN Varchar2 DEFAULT NULL,
p_champ20 IN Varchar2 DEFAULT NULL,
p_champ21 IN Varchar2 DEFAULT NULL,
p_champ22 IN Varchar2 DEFAULT NULL,
p_champ23 IN Varchar2 DEFAULT NULL,
p_champ24 IN Varchar2 DEFAULT NULL,
p_champ25 IN Varchar2 DEFAULT NULL,
p_champ26 IN Varchar2 DEFAULT NULL,
p_champ27 IN Varchar2 DEFAULT NULL,
p_champ28 IN Varchar2 DEFAULT NULL,
p_champ29 IN Varchar2 DEFAULT NULL,
p_champ30 IN Varchar2 DEFAULT NULL,
p_champ31 IN Varchar2 DEFAULT NULL,
p_champ32 IN Varchar2 DEFAULT NULL,
p_champ33 IN Varchar2 DEFAULT NULL,
p_champ34 IN Varchar2 DEFAULT NULL,
p_champ35 IN Varchar2 DEFAULT NULL,
p_champ36 IN Varchar2 DEFAULT NULL,
p_champ37 IN Varchar2 DEFAULT NULL,
p_champ38 IN Varchar2 DEFAULT NULL,
p_champ39 IN Varchar2 DEFAULT NULL,
p_champ40 IN Varchar2 DEFAULT NULL,
p_champ41 IN Varchar2 DEFAULT NULL,
p_champ42 IN Varchar2 DEFAULT NULL,
p_champ43 IN Varchar2 DEFAULT NULL,
p_champ44 IN Varchar2 DEFAULT NULL,
p_champ45 IN Varchar2 DEFAULT NULL,
p_champ46 IN Varchar2 DEFAULT NULL,
p_champ48 IN Varchar2 DEFAULT NULL,
p_champ49 IN Varchar2 DEFAULT NULL,
p_champ50 IN Varchar2 DEFAULT NULL,
p_champ51 IN Varchar2 DEFAULT NULL,
p_champ52 IN Varchar2 DEFAULT NULL,
p_champ53 IN Varchar2 DEFAULT NULL,
p_champ54 IN Varchar2 DEFAULT NULL,
p_champ55 IN Varchar2 DEFAULT NULL,
p_champ56 IN Varchar2 DEFAULT NULL,
p_champ57 IN Varchar2 DEFAULT NULL,
p_champ58 IN Varchar2 DEFAULT NULL,
p_champ59 IN Varchar2 DEFAULT NULL,
p_champ60 IN Varchar2 DEFAULT NULL,
p_champ61 IN Varchar2 DEFAULT NULL,
p_champ62 IN Varchar2 DEFAULT NULL,
p_champ63 IN Varchar2 DEFAULT NULL,
p_champ64 IN Varchar2 DEFAULT NULL,
p_champ65 IN Varchar2 DEFAULT NULL,
p_champ66 IN Varchar2 DEFAULT NULL,
p_champ67 IN Varchar2 DEFAULT NULL,
p_champ68 IN Varchar2 DEFAULT NULL,
p_champ69 IN Varchar2 DEFAULT NULL,
p_champ70 IN Varchar2 DEFAULT NULL,
p_champ71 IN Varchar2 DEFAULT NULL,
p_champ72 IN Varchar2 DEFAULT NULL,
p_champ73 IN Varchar2 DEFAULT NULL,
p_champ74 IN Varchar2 DEFAULT NULL,
p_champ75 IN Varchar2 DEFAULT NULL,
p_champ76 IN Varchar2 DEFAULT NULL,
p_champ77 IN Varchar2 DEFAULT NULL,
p_champ78 IN Varchar2 DEFAULT NULL,
p_champ79 IN Varchar2 DEFAULT NULL,
p_champ80 IN Varchar2 DEFAULT NULL,
p_champ81 IN Varchar2 DEFAULT NULL,
p_champ82 IN Varchar2 DEFAULT NULL,
p_champ83 IN Varchar2 DEFAULT NULL,
p_champ84 IN Varchar2 DEFAULT NULL,
p_champ85 IN Varchar2 DEFAULT NULL,
p_champ86 IN Varchar2 DEFAULT NULL,
p_champ87 IN Varchar2 DEFAULT NULL,
p_champ88 IN Varchar2 DEFAULT NULL,
p_champ89 IN Varchar2 DEFAULT NULL,
p_champ90 IN Varchar2 DEFAULT NULL,
p_champ91 IN Varchar2 DEFAULT NULL,
p_champ92 IN Varchar2 DEFAULT NULL,
p_champ93 IN Varchar2 DEFAULT NULL,
p_champ94 IN Varchar2 DEFAULT NULL,
p_champ95 IN Varchar2 DEFAULT NULL,
p_champ96 IN Varchar2 DEFAULT NULL,
p_champ97 IN Varchar2 DEFAULT NULL,
p_champ98 IN Varchar2 DEFAULT NULL,
p_champ99 IN Varchar2 DEFAULT NULL,
p_champ100 IN Varchar2 DEFAULT NULL
 )
 
IS
 
 TYPE TAB_CHAMP is table of varchar2(200)index by binary_integer ;
 TAB1 TAB_CHAMP ;
 val_param varchar2(50) ;
-- chaine varchar2(900) ;
 chaine2 varchar2(900) ;
 chaine3 varchar2(900) ;
 val_champ varchar2(100) ;
 ret varchar2(100) ;
 i pls_integer := 0 ;
 j pls_integer := 1 ;
 
 
Begin
 
   Loop
   i := i + 1 ;
       val_param :=  'p_champ'||i;
       --DBMS_OUTPUT.PUT_LINE (' val_param: '||val_param);
       --execute immediate 'declare p_champ1 varchar2(100) := :1; begin :2 := '||p_champ1||'; end;' USING IN val_param, OUT ret; 
      -- execute immediate 'declare p_champ1 varchar2(100) := :1; begin :2 := '||val_param||'; end;' USING IN p_champ1, OUT ret;  
      -- execute immediate 'declare p_champ'||i||' varchar2(100) := :1; begin :2 := '||val_param||'; end;' USING IN p_champ1, OUT ret;  
   execute immediate 'declare p_champ'||i||' varchar2(100) := :1; begin :2 := '||val_param||'; end;' USING IN p_champ1, OUT ret;   
 
 
    DBMS_OUTPUT.put_line('val_param= '||val_param ||'. retour:'|| ret);  
   exit;
 
   IF ret is null THEN
      EXIT;
    END IF;
    TAB1(i):= ret;
 
 
   End loop ;
   commit;
 
  chaine2:= TAB1(j);
 
  FOR j IN 2 .. i LOOP
    chaine2:=  chaine2 ||','|| TAB1(j);
  END LOOP;
  COMMIT;
 
 
 chaine3:= 'INSERT INTO '||p_LIB_TAB||' ('||p_LIB_VAL||') SELECT '||chaine2|| ' FROM DUAL';   
 
 
 EXECUTE IMMEDIATE chaine3;
 COMMIT;
 
 
 
End ;

Mon soucis actuellement est la suivante :
Je récupère dans une variable les paramètres val($i).
Exemple :
dans le paramètre de la procédure val1 = 'toto'
$i=1
$var=val($i)
$var est affectée à val1 et non 'toto'

J'ai donc tenté d'utiliser un bind

Code:

1
2
3
val_param :=  'p_champ'||i;
execute immediate 'declare p_champ'||i||' varchar2(100) := :1; begin :2 := '||val_param||'; end;' USING IN p_champ1, OUT ret;

Mon soucis est p_champ1, si je met p_champ||i, il m'interprète 'p_champ1' et non la valeur du paramètre p_champ1.
En fait je souhaiterai avoir l'équivalent de la fonction eval sous UNIX pour ceux qui connaissent.

Cela fait 2 jours que je suis sur ce problème, donc tout aide est le bienvenue.
Je vous remercie par avance

Pourquoi tant de haine envers votre base de données ?

Bon j'ai fais une procédure plus simple, je met en paramètre les champs à insérer.
Je n'y arrive pas, j'ai l'erreur de l'EXCEPTION qui apparaît et rien d'autre.
Quand je copie et je colle la sortie du DBMS_OUTPUT.PUT_LINE et que j'execute, la ligne est bien insérée.

Je comprend plus rien, et je ne sais même pas comment débugger le problème :cry:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE OR REPLACE PROCEDURE "INS_TAB" ( p_$CHAMP1 IN Varchar2, p_$CHAMP2 IN Varchar2 ) IS chaine VARCHAR2(500); BEGIN chaine :='INSERT INTO LIBELLE_TABLE SELECT '''||p_$CHAMP1||''', ''' ||p_$CHAMP2||''' FROM DUAL;'; DBMS_OUTPUT.PUT_LINE(chaine); EXECUTE IMMEDIATE chaine; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Erreurs'); END;

Sortez le ; de votre chaîne exécutée.
De plus, pour insérer une seule ligne la syntaxe est INSERT INTO table VALUES.

Bonjour,

J'ai enlevé le ";" de la chaîne exécutée mais il y a toujours une erreur.
La syntaxe pour insérer une seule ligne :
insert into table select champx from dual fonctionne également, je l'ai testé et la ligne est bien insérée.

Supprimez aussi votre bloc d'exception qui ne sert qu'à masquer votre erreur.

J'ai copié/collé votre procédure, j'ai supprimé le ; comme je vous l'ai indiqué et ça "fonctionne".

Merci pour votre aide.
Le bloc d'exception a été supprimé et j'ai pu corrigé.
Du coup, je n'ai vais même pas mettre de bloc d'exception, s'il y aune erreur se sera plus facile de débugger.

J'avais oublié de faire un commit :oops:
C'était la fin de la journée, rien qu'une bonne nuit de sommeil pour se remettre d'à plomb :ccool:

Je laisse le sujet ouvert pour mon post initial

Quel est le but réel de cette procédure ?

si ce n'est de n'avoir qu'une seule procédure "générique" a appeler qui sera moins performante qu'un insert/update statique