Execute Immediate et bind variables

Version imprimable

Bonjour,
Je suis dans un trigger, dans lequel un curseur me renvoie les noms de colonnes qui m'intéressent.
Code:

1 2 3 4 5 6 7 8 FETCH CUR_PK INTO LCUR_PK; WHILE CUR_PK%FOUND LOOP lreq := 'select to_char((:new' ||lCur_PK.column_name||')) from dual'; execute immediate lreq into ltest; lpk := lpk || '--' || lTest; FETCH CUR_PK INTO LCUR_PK; END LOOP;
Logiquement, Oracle refuse car il souhaite binder la valeur commençant par ":".
Connaissez vous un moyen d'effectuer ce genre d'opération ?
Par avance merci,
Erwan

15/03/2010, 21h09
skuatamad
1/ Même dans un trigger une "bonne pratique" consiste à placer le code dans une procédure (incluse dans un package de préférence).

2/ Je ne suis pas fan (et je ne suis pas le seul) de la syntaxe open fetch ..., ça c'est pour ton 1er curseur, je préfère :
Code:

1 2 3 for c in (your query) loop ... end loop;
3/ Pour le 2eme curseur, sans reprendre ton exemple, tu peux utiliser USING pour bind la variable, ex :
Code:

1 2 l_query := 'select * from emp where empno = :1'; open c for l_query using p_empno;
où p_empno pourrait être :new (ou le paramètre de la procédure dans laquelle tu passes :new)
USING fonctionne aussi avec EXECUTE IMMEDIATE.

Je ne suis pas un grand spécialiste des triggers, j'espère ne pas t'induire en erreur.
Si tu as toujours des difficultés à mettre en place ton trigger, je pense qu'un exemple complet du trigger serait préférable.
16/03/2010, 10h32
wanagain35
Bonjour, et merci de votre réponse, mais il me semble que le problème n'a pas été cerné.

1/ Je suis dans un schéma spécifique, dans lequel j'ai un trigger qui fait appel à des procédures pour générer des triggers dans un autre schéma.
J'ai besoin des pks que je récupère dans un curseur (grâce à USER_CONS_COLUMNS), je boucle sur celles-ci pour en extraire les valeurs.

2/ Admettons.

3/ Ma question n'est pas celle-là.

4/ Je récupère des NOMS de colonnes dans un trigger et je souhaite conserver leurs valeurs. Oracle ne peut interpréter :
Code:

1 2 :new.MonCurseur.NomColonne
J'utilise alors du code dynamique avec execute immediate :
Code:

1 2 execute immediate 'select to_char((:new.'||lCur_PK.column_name||')) from dual';
Mais il refuse d'interpréter le :new (nouvelle valeur de la colonne nommée dans le trigger) et me demande de le binder. Ce qui n'est pas du tout ce que je souhaite faire.
Voyez vous donc un moyen de contourner ce problème ?
Ai-je été plus précis ?
Merci d'avance de vos réponses,
Cordialement,
Erwan

Re-bonjour,

J'ai trouvé une solution en bouclant depuis le trigger générant les triggers.
Quelque chose qui ressemble en résumé à :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
  CURSOR CUR_PK (tTable IN TABLES_TO_AUDIT.TABLE_NAME%TYPE)IS
  SELECT 'TO_CHAR(:new.' || column_name || ');' SavPK
  FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME in (SELECT INDEX_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = upper(''||tTable||'') AND CONSTRAINT_TYPE = 'P')
  ORDER BY POSITION ASC; 
 
  OPEN CUR_PK(:NEW.TABLE_NAME);
  FETCH CUR_PK INTO LCUR_PK;
  tTriggerPK := 'lPk := ' || LCUR_PK.SavPK || CHR(10) ;
  FETCH CUR_PK INTO LCUR_PK;  
  WHILE CUR_PK%FOUND LOOP
    tTriggerPK := tTriggerPK || 'lPk := lPk || ''-'' || ' || LCUR_PK.SavPK || CHR(10) ;
    FETCH CUR_PK INTO LCUR_PK;
  END LOOP;
  CLOSE CUR_PK;

Pour ensuite générer le code des triggers appropriés.
Merci