Problème de conception : plusieurs rows vs. Select Into

Version imprimable

Bonjour,

Je débute en PL/SQL. J'ai à ma disposition une base Oracle et SQL*Plus.

Pour mon premier script, j'ai souhaité faire quelque chose qui me semble simple : récupérer l'ensemble des droits sur tous les objets de la base, pour tous les utilisateurs.

J'étais donc parti sur cette idée :
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 Set SERVEROUTPUT On Prompt ; Prompt ------------------------------------- ; Prompt - Liste des utilisateurs de la base - ; Prompt ------------------------------------- ; Prompt ; DECLARE CURSOR object_cur IS SELECT OBJECT_NAME, OWNER FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' OR OBJECT_TYPE = 'INDEX' OR OBJECT_TYPE = 'VIEW'; object_rec object_cur%ROWTYPE; vpriv VARCHAR2(64); vcount INTEGER; CURSOR user_cur IS SELECT USERNAME FROM DBA_USERS; user_rec user_cur%ROWTYPE; PROCEDURE count_rights (object VARCHAR2, user VARCHAR2) IS BEGIN SELECT COUNT(t.PRIVILEGE) INTO vcount FROM DBA_TAB_PRIVS t WHERE t.GRANTEE = user AND t.TABLE_NAME = object; END; PROCEDURE get_rights (object VARCHAR2, user VARCHAR2) IS BEGIN SELECT t.PRIVILEGE INTO vpriv FROM DBA_TAB_PRIVS t WHERE t.GRANTEE = user AND t.TABLE_NAME = object; END; BEGIN OPEN object_cur; LOOP FETCH object_cur INTO object_rec; EXIT WHEN object_cur%NOTFOUND; OPEN user_cur; LOOP FETCH user_cur INTO user_rec; EXIT WHEN user_cur%NOTFOUND; count_rights (object_rec.OBJECT_NAME, user_rec.USERNAME); IF vcount <> 0 THEN get_rights (object_rec.OBJECT_NAME, user_rec.USERNAME); END IF; END LOOP; CLOSE user_cur; END LOOP; CLOSE object_cur; END; /
On a donc deux sortes de cursor for loops (ce n'est pas exactement ça) imbriquées. Le count_rights me sert à gérer l'exception NO_DATA_FOUND, je n'arrive pas à la gérer autrement.

Le get_rights ne fonctionne pas car il arrive qu'il retourne plusieurs rows, lorsque l'utilisateur a plusieurs droits sur un même objet. Et il ne m'est pas possible de faire le Select Into sur plusieurs lignes.

Or je suis obligé de faire ce Select Into, puisqu'un simple Select ne marche pas. Oracle se plaint qu'un "Into" est attendu à ce moment-là.

Je pense en fait que la façon dont j'ai conçu le script n'est pas bonne du tout. Et je suis de toute façon bloqué par ce dernier point du Select Into sur plusieurs rows.

Le problème est que je ne vois pas vraiment quelle autre direction prendre...

Pourriez-vous s'il vous plaît me suggérer une piste pour contourner ce problème ? Quitte à tout refaire depuis le début, tant pis !

Merci d'avance.

Bon en fait j'ai laissé tomber les curseurs et j'ai fait un truc tout con avec un script shell.

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
#!/bin/sh
 
echo > gtr.log
 
sqlplus "****/**** as sysdba" @sql/gtr.sql
 
# traitement du fichier resultat
 
# suppression des titres des colonnes et de la ligne resultats
cat gtr.log | grep -v "USERNAME" | grep -v "rows selected" > gtr.log.tmp
mv gtr.log.tmp gtr.log
 
# suppression des lignes vides
/usr/bin/sed '/^$/d' gtr.log > gtr.log.tmp
mv gtr.log.tmp gtr.log
 
# suppression des lignes de tirets
/usr/bin/sed '/----/d' gtr.log > gtr.log.tmp
mv gtr.log.tmp gtr.log
 
# formatage vers CSV
cat gtr.log | tr -s " " | tr " " ";" | sort -u > gtr.log.tmp
mv gtr.log.tmp gtr.log
echo "Utilisateur;Objet;Type d'Objet;Droit Possede;" > gtr.csv
cat gtr.log >> gtr.csv
rm gtr.log
 
echo ""
echo "Les droits ont ete extraits vers le fichier gtr.csv."
echo ""

Et le SQL qui va avec :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Set Line 512
 
Spool gtr.log
 
SELECT u.USERNAME, o.OBJECT_NAME, o.OBJECT_TYPE, t.PRIVILEGE
FROM DBA_USERS u, DBA_OBJECTS o, DBA_TAB_PRIVS t
WHERE t.GRANTEE = u.USERNAME
AND t.TABLE_NAME = o.OBJECT_NAME
AND u.USERNAME != ALL ('SYS', 'SYSTEM', 'DBSNMP', 'OUTLN',
                        'MDSYS', 'ORDSYS', 'ORDPLUGINS', 'CTXSYS', 'DSSYS', 'PERFSTAT',
                        'WKPROXY', 'WKSYS', 'WMSYS', 'XDB', 'ANONYMOUS', 'ODM', 'ODM_MTR',
                        'OLAPSYS', 'TRACESVR', 'REPADMIN', 'HR', 'OE', 'PM', 'SH', 'QS',
                        'QS_ES', 'QS_WS', 'QS_OS', 'QS_CB', 'QS_CS', 'QS_ADM', 'QS_CBADM',
                        'RMAN', 'SYSMAN', 'DIP', 'DMSYS', 'MDDATA', 'MGMT_VIEW', 'OLAPDBA',
                        'OLAPSVR', 'TSMSYS', 'EXFSYS', 'SI_INFORMTN_SCHEMA', 'ORACLE_OCM');
 
Spool off
 
Quit

Voilà, c'est pas génial mais ça fait l'affaire. En attendant d'apprendre petit à petit..!

30/12/2008, 15h18
greek75

salut

c'est dommage que t'as pas pris le pl/sql

moi aussi je comprenais rien au début. J'ai lu les tutos sur ce site sur oracle et c'est pas mal expliqué. même si le site est un peu pateu

http://www.waanser.com/pl-sql/38-initiation-en-plsql.html
30/12/2008, 15h45
brassouille

Salut,
je n'ai pas regardé en détail ton code, mais le SELECT INTO ne marche effectivement que si il y a une seule ligne retournée.

Si tu as plusieurs lignes :
- soit tu crées un curseur que tu parcours, à chaque passe il lira une nouvelle ligne que tu devras traiter
- soit tu crées une variable de type tableau (la plus simple à utiliser est TABLE INDEX BY, qui se déclare du genre TYPE toto IS TABLE OF cequetuveux INDEX BY BINARY_INTEGER. Ensuite, tu as la même syntaxe que SELECT INTO, sauf que tu rajoutes le mot clé BULK : BULK COLLECT INTO variableDeTypeTableau. Ensuite tu devras parcourir ce tableau pour faire ce que tu veux de chaque ligne retournée.