[Oracle 10g] récupérer toutes les colonnes dans un DELETE FROM RETURNING

Bonjour,

Je cherche à récupérer toutes les lignes supprimées par un DELETE.

Code:

---

1 2 3 4 5

DELETE FROM ma_table WHERE .... RETURNING col1, col2, ... BULK COLLECT INTO ma_structure;

---

Cependant, il semble que je sois obligé d'énumérer toutes les colonnes.
Existe t-il un moyen de ne pas énumérer toutes les colonnes, comme dans les SELECT * INTO ... ?

Merci de vos astuces.

Non

Citation:

---

Envoyé par mnitu

Non

---

Le problème, c'est pas tellement de tout énumérer, c'est surtout que si je rajoute une colonne dans la table, ma procédure sera invalide.

J'aurais aimé trouver une méthode générique, même si c'est un peu bricolé.

Il suffit de faire le SELECT avant le DELETE.

Citation:

---

Envoyé par Waldar

Il suffit de faire le SELECT avant le DELETE.

---

Oui, mais je voudrais sauver les lignes supprimées, sans dupliquer la clause WHERE.

J'ai trouvé une idée, mais ça ne semble pas fonctionner. Je crée dynamiquement la liste des colonnes (à partir de la vue USER_TAB_COLS).
Je crée la liste des colonnes, et la liste des bind variables.

Code:

---

1 2 3

liste_col := listecolonnes('ma_table', ''); liste_bind := listecolonnes('ma_table', ':');

---

Ce qui me donne 2 listes :
- col1,col2, ...
- :col1,:col2, ...

Puis je crée une requête dynamique, et je voudrais tout récupérer dans un tableau de ROWTYPE (appelé ma_liste).

Code:

---

1 2 3 4 5 6

req := 'delete from ma_table c where C.id <= 100 RETURNING ' || liste_col || ' INTO ' || liste_bind;   EXECUTE IMMEDIATE req RETURNING BULK COLLECT INTO ma_liste;

---

PLS-00429: unsupported feature with RETURNING clause

Oui, pourquoi faire simple quand on peut faire compliqué après tout ;)

Code:

---

1 2 3 4 5 6 7 8

CREATE TABLE TMP_SAV AS SELECT * FROM MaTable WHERE ...;   DELETE FROM MaTable WHERE ...;

---

Et si vraiment vous ne voulez pas dupliquer votre clause WHERE :

Code:

---

1 2 3 4 5 6 7 8

CREATE TABLE TMP_SAV AS SELECT * FROM MaTable WHERE ...;   DELETE FROM MaTable WHERE <PK> in (SELECT <PK> FROM TMP_SAV);

---

Citation:

---

Envoyé par doukem

Le problème, c'est pas tellement de tout énumérer, c'est surtout que si je rajoute une colonne dans la table, ma procédure sera invalide.

J'aurais aimé trouver une méthode générique, même si c'est un peu bricolé.

---

Bref, je ne pense pas qu'elle a vraiment une utilité pratique (pour ne pas parler des performance) mais voilà

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

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 Connected as mni   SQL> SQL> set serveroutput on SQL> Create Or Replace Type dept_obj As Object ( 2 department_id number(4), 3 department_name varchar2(30), 4 manager_id number(6), 5 location_id number(4) 6 ) 7 /   Type created SQL> Declare 2 Type dept_aa Is Table Of dept_obj; 3 l_dept dept_aa; 4 sql_stmt constant varchar2(1000) := 'Delete departments Returning dept_obj(department_id, department_name, manager_id, location_id) Into :1'; 5 Begin 6 Execute immediate sql_stmt RETURNING BULK COLLECT INTO l_dept; 7 -- 8 Rollback; 9 -- 10 For i In 1..L_dept.Count() Loop 11 dbms_output.put_line(l_dept(i).department_id||' '||l_dept(i).department_name); 12 End Loop; 13 End; 14 /   10 Administration 20 Marketing 30 Purchasing 40 Human Resources 50 Shipping 60 IT 70 Public Relations 80 Sales 90 Executive 100 Finance 110 Accounting 120 Treasury 130 Corporate Tax 140 Control And Credit 150 Shareholder Services 160 Benefits 170 Manufacturing 180 Construction 190 Contracting 200 Operations 210 IT Support 220 NOC 230 IT Helpdesk 240 Government Sales 250 Retail Sales 260 Recruiting 270 Payroll   PL/SQL procedure successfully completed   SQL>

---

Citation:

---

Envoyé par mnitu

Code:

---

1 2 3 4 5 6 7 8

SQL> Create Or Replace Type dept_obj As Object ( 2 department_id number(4), 3 department_name varchar2(30), 4 manager_id number(6), 5 location_id number(4) 6 ) 7 /

---

---

Merci pour cette astuce.
Par contre on en revient toujours au problème de devoir déclarer toutes les colonnes.
Si je dois rajouter une colonne dans ma table, je dois aussi rajouter un attribut dans l'objet.

Ce qui me surprend, c'est que le RETURNING BULK COLLECT INTO permet de récupérer les lignes dans un TABLE OF OBJECT, mais pas dans un TABLE OF ..%ROWTYPE.
=> sinon l'utilisation du %ROWTYPE permettrait de s'affranchir de l'énumération.

Citation:

---

Envoyé par doukem

Merci pour cette astuce.
Par contre on en revient toujours au problème de devoir déclarer toutes les colonnes.
Si je dois rajouter une colonne dans ma table, je dois aussi rajouter un attribut dans l'objet.
...

---

C'est la même problématique que vous avez déjà résolue via USER_TAB_COLS pour créer la liste des colonnes.

Citation:

---

Envoyé par mnitu

C'est la même problématique que vous avez déjà résolue via USER_TAB_COLS pour créer la liste des colonnes.

---

Je ne comprends pas trop. J'ai bien résolu ce problème dans mon cas en utilisant USER_TAB_COLS, mais je suis bloqué ensuite car il est impossible d'utiliser un tableau de record dans le RETURNING.

En utilisant votre approche, je peux utiliser une structure dans le RETURNING, mais je ne peux plus utiliser le USER_TAB_COLS pour déclarer la structure, je suis obligé de tout énumérer.

La structure de l'objet est identique à celle de la table décrite dans USER_TAB_COLS. Vous pouvez donc ré créer l'objet chaque fois que la structure de la table bouge.