Select dynamique avec execute immediate

Version imprimable

01/06/2008, 03h49
BOLARD

Select dynamique avec execute immediate

Bonjour à tous,

Je souhaitait faire une procédure générique en pls-sql qui mes en majuscule toute les valeurs d'un champ d'une table. Je ne maîtrise pas bien les select dynamique.

Je écrit cette procédure sur isql *sql, mais c'est un peu difficile pour débogguer car je ne peux pas contrôler ce qu'il y a dans mes variable
dbms_outpu.put_line('.........') et print ne fonctionne pas....
La procédure arrive à se compiler, mais à l'exécution de cette procédure j'ai une erreur de type de boucle. Apparament ça boucle trop, Portant je récupère le nombre de d'enregistrement de la table. avec un count * de la table mais avec execute immediate je ne c'est pas ce qui me récupère dans mon into, j'avais mis un alias sur le count *, mais j'ai l'impression qui me recupère mal nbFila.

Voici ma procédure

Citation:

create or replace procedure campoMajusculo(tabla in varchar2, campo in varchar2)
is
reqContador varchar2(50);
reqSelect varchar2(50);
reqUpdate varchar2(50);
nbFila integer;
campoTabla varchar2(50);

Begin

reqContador := 'select count(*) as nbFila from ' || tabla ;
EXECUTE IMMEDIATE reqContador into nbFila;

// j'ai essayer aussi en commençant à 0, mais ça change rien,
//même message d'erreur à l'exécution
For fila in 1..nbFila
LOOP
reqSelect := 'select ' || campo || ' from ' || tabla;
EXECUTE IMMEDIATE reqSelect into campoTabla;
campoTabla := upper(campoTabla);
reqUpdate := 'update ' || tabla || 'set ' || campo || '=' || campoTabla;

EXECUTE IMMEDIATE reqUpdate;
END LOOP;
End;

ça compile bien mais quand j'exécute ça bug

Citation:

begin
campoMajusculo('Prueba','proyecto');
end;

begin
*

ERROR en línea 1:
ORA-01422: la recuperación exacta devuelve un número mayor de filas que el solicitado
ORA-06512: en "DAV79.CAMPOMAJUSCULO", línea 23
ORA-06512: en línea 2

Je voudrais savoir si quelqu'un aurait une solution à mon problème, comment je peux vérifier ce qu'il y a dans le into.

Il y a t'il possibilité de construire un curseur de la même manière q'un select dynamique.

Citation:

ex : reqCur = 'curDyn CURSOR IS select ' || campo || ' from ' || tabla;
execute immediate into curDyn;

for fila in curDyn
loop
campo = fila.campo;
..........................;
end loop

Mon problème est assez urgent, car je doit rendre un projet de base de donnée en ajoutant cette fonctionnalité.

Merci d'avance pour votre aide.

dav79
01/06/2008, 08h43
plalm

Citation:

Envoyé par BOLARD

Je écrit cette procédure sur isql *sql, mais c'est un peu difficile pour débogguer car je ne peux pas contrôler ce qu'il y a dans mes variable
dbms_outpu.put_line('.........') et print ne fonctionne pas....

Pour visualiser dans ta console les messages provenant de dbms_output, tu dois modifier les paramètres de ta console.

Code:

set serverout on

Si tu travailles en console, il est utile de connaître les différents paramètres, leurs valeurs et leur utilité. Documentation à ce propos (par exemple) : http://www.ss64.com/orasyntax/plus_set.html

A suivre pour le contenu de ta procedure ...

Pour ce qui est de la procedure, je ne crois pas qu'elle fasse ce que tu désires :

action 1 : rechercher le nombre de lignes présentes dans la table

action 2 : rechercher les valeurs de ton attribut

action 3 : updater la valeur de ton attribut

Si ta table contient 100 lignes, tu vas mettre à jour 100 fois toutes tes 100 lignes !

Ne serait-il pas souhaitable de voir le problème autrement

action 1 : vérifier que la table existe

action 2 : vérifier que l'attribut existe

action 3 : vérifier que l'attribut peut être mis en majuscule

action 4 : mettre à jour l'ensemble des lignes de la table

Cela pourrait donner quelque chose comme (écrit sans console, donc non testé, donc avec erreurs syntaxe)

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
create or replace function proc_upper_col(ptbl in varchar2, pcol in varchar2) return integer
is
vtbl_exist  integer;
vcol_exist  integer;
vcol_format varchar2(250);
vstrupd     varchar2(250);
Begin
   -- la table ptbl existe ?
   select count(*) into vtbl_exist from all_tables where table_name = ptbl;
      -- si la table ptbl n existe pas, sortie 1
      if vtbl_exist = 0 then 
         dbms_output.put_line('la table ' || ptbl ||' n existe pas');
         return 1;
      end if;
 
   -- la colonne pcol existe ?
   select count(*) into vcol_exist from all_tables_columns where table_name = ptbl and column_name = pcol;
      -- si la colonne pcol n existe pas, sortie 2
      if vcol_exist = 0 then
    	  dbms_output.put_line('la colonne ' || pcol ||' n existe pas');
	  return 2;
      end if;
 
   -- la colonne pcol peut être mise en majuscule ?
   select data_type into vcol_format from all_tables_columns where table_name = ptbl and column_name = pcol;
      -- si la colonne pcol n est pas un string, sortie 3
      if vcol_format not in ('CHAR','VARCHAR','VARCHAR2') then 
	 dbms_output.put_line('la colonne ' || pcol ||' n est pas un string');
	 return 3;
      end if;
 
   -- mise a jour de ptbl.pcol
   vstrupd := 'update ' || ptbl || ' set ' || pcol || '=upper(' || pcol || ') where 1=1;'
   dbms_output.put_line('commande SQL = ' || vstrupd);
   execute immediate vstrupd;
 
   commit;
 
   return 0;
 
   exception
      when others then 
	     dbms_output.put_line('Error : ' || SQLCODE ||' - ' || SQLERRM);
end;
/

01/06/2008, 13h17
mnitu
Programmez offensif! :)
- Action 1: mettre à jour l'ensemble de lignes de la table
Exception
Si table inexistante alors Raise_application_error(-20000, 'la table ||tname||' est inexistante'
Si colonne inexistante alors Raise_application_error ... ||colname||
Si value erreur alors Raise_application_error ...
- Ps1. When OTHERS Then DBMS_OUTPUT non suivi de RAISE égal bug numéro 1 de la programmation PL/SQL
- Ps2. Le retour des valeurs 0, 1, 2 etc. casse la logique de programmation basée sur les exceptions ce qui est une régression dans les techniques de
  programmation.
01/06/2008, 17h26
BOLARD

Merci beaucoup pour ta solution pertinante, tu m'as appris plein de commande que je ne connaissait pas, je vais tester ta fonctionner.

Je vais suivre tes conseils.
03/06/2008, 16h29
grome

Bonjour

je passe par là ... et il y a un truc que je ne comprend pas. Je débute en pl sql et je galère un peu avec l'écriture d'un petit script.

execute immediate (que l'on peut voir dans le code de ce fil de discussion) d'après la doc Oracle ce serait pas réservé à des ordres autres que le select ?
03/06/2008, 16h33
orafrance

non, il permet aussi d'exécuter du code DDL (CREATE, ALTER, etc...) mais évidemment il vaut mieux utiliser SQL avec des variables SQL*Plus éventuellement.
03/06/2008, 16h41
grome

Citation:

Envoyé par orafrance

non, il permet aussi d'exécuter du code DDL (CREATE, ALTER, etc...) mais évidemment il vaut mieux utiliser SQL avec des variables SQL*Plus éventuellement.

ok alors je vais exposer mon problème dans une autre dicussion