Discussion :

[PpPool]

bonjour à tous,

j'ai une procédure stokée relativement simple dans un package qui crée temporairement une vue avec les transactions autonomes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
--//extract package body BIBLIO
 
--//----------------------------------------------------------------------------------
PROCEDURE CREER_VUE(nom_vue IN USER_VIEWS.VIEW_NAME%TYPE,chSQL IN VARCHAR2)
IS
 
--//--DECLARATION DE PROCEDURE AUTONOME
pragma autonomous_transaction ; 
 
BEGIN
-- tout le code qui figure ici est dans la transaction autonome 
EXECUTE IMMEDIATE ('CREATE VIEW '||nom_vue||' AS '||chSQL) ; 
commit ; 
 
END CREER_VUE;

--//--------------------------------------------------------------------------------
//Lorsque j'appelle via SQL+ par exemple ma procédure, j'ai systèmatiquement un message

privilèges insuffisants à "TP.BIBLIO"

TP a les priviléges CONNECT,RESOURCE et DBA

--//lorsque je code comme suit l'exécution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
declare
--extract
--différentes variables locales
nom_vue USER_VIEWS.VIEW_NAME%TYPE;
chSQL VARCHAR2(500);
--//--DECLARATION DE PROCEDURE AUTONOME
pragma autonomous_transaction ; 
 
BEGIN
nom_vue:='TITI_AJU3';
chSQL:='SELECT RESU_QUANTITATIF,RESU_SUP_INF,RESU_MESURE FROM RESULTATS  WHERE ESS_ID='''||id_essai||''' AND PARA_ID='''||id_parametres||''' AND MATR_ID='''||id_matrice||''' AND RESU_REPONSE=0'; 
 
 
-- tout le code qui figure ici est dans la transaction autonome 
EXECUTE IMMEDIATE ('CREATE VIEW '||nom_vue||' AS '||chSQL) ; 
commit ; 
END;

--là, bien sur çà marche !!



j'ai cherché, mais pas trouvé le privilège qu'il manque à l'utilisateur TP

un GRANT 'EXECUTE' existe t-il ?
merci de votre aide

[rouardg]

Bonjour,

Peux-tu essayer un GRANT CREATE VIEW TO TP et réexécuter ta procédure STP ???

Dis moi si ça marche.

Autre chose qui me choque un peu et qui serait à vérifier. Créer des vues de manière dynamique, ce n'est pas courant. Mais bon, passons.

En fait, tu créées une vue dans une transaction autonome. Celle-ci est donc totalement indépendante de la transaction principale. Ensuite, on quittera cette transaction autonome lorsque l'on mettra fin à cette transaction, soit par un COMMIT (ce que tu fais), soit par un ROLLBACK.
Mais il y a aussi les instructions du LDD (langage de définition de données) qui effectue un COMMIT.

J'ai donc peur que ton CREATE VIEW en dynamique ne te fasse quitter ta transaction autonome. Du coup, ton COMMIT, et bien il commiterait ta transaction principle !!! Il faut vérifier ce point !!!

[rouardg]

C'est encore moi. J'ai fait un essai et te confirme mes propos. Il faut que tu supprimes ton COMMIT.

Voici l'essai :

create table essai (num number(3)) ;
insert into essai (num) values (999) ;
select * from essai ;

Jusque là, j'ai créé une table et fait un INSERT. Je n'ai ni commité, ni rollbacké. Tu es donc d'accord que ma transaction est toujours en cours.

D'ailleurs, si tu connais un peu ces vues :

select * from v$rollstat ;
select * from v$transaction ;

Elles te donneront confirmation qu'une transaction est en cours.

Maintenant, je créée tout comme toi une vue en dynamique :

begin
execute immediate 'create view v_credit (id_credit) as select id_credit from credit' ;
end ;
/

Je n'ai toujours pas commité ou rollbacké, et pourtant ma transaction est terminé. Pour t'en rendre compte, tu peux réinterroger les vues V$ROLLSTAT ou V$TRANSACTION.

Même mieux, fais un ROLLBACK suivi d'un select * from essai et tu verras que la table essai contient toujours ma ligne de données.

[PpPool]

merci rouardg pour ton aide

le privilége CREATE VIEW fonctionne, merci bcq (j'ai supprimé le commit dans la procédure)

Quelques éclaircissements sur le pq des vues dynamiques :
je travaille dans une société d'ingénierie statistiques où nous avons besoin d'extraire dynamiquement selon n'importe quelles combinaisons choisies par l'utilisateur des données stockées dans des tables très statiques (une maj hebdommadaire). Sur ces données nous lançons ensuite des processus statistiques très importants styles réseaux de neurones.

La solution des vues apparait comme étant la meilleure (enfin je pense), sachant qu'il n'y aura pas pendant ce temps des commandes LDD sur ces tables.

bonne journée

[rouardg]

Ok pour la création de vues en dynamique. Ce n'est pas chose courante, mais je comprends votre besoin.

Au sujet du privilège, je te dois une explication.

Tout d'abord, tu me parlais de ton user TP :

TP a les priviléges CONNECT,RESOURCE et DBA

En fait, CONNECT, RESOURCE et DBA ne sont pas des privilèges, mais des rôles, un rôle étant un ensemble de privilèges.

Le fait d'avoir affecter ces 3 rôles (qui contiennent chacun le privilège CREATE VIEW) fait que ton utilisateur TP se retrouve avec le droit de créer des vues. D'ailleurs, tu as pu le faire dans ton bloc de code PL/SQL.

Par contre, dès que tu as voulu faire la même chose dans une procédure PL/SQL, tu n'avais plus le privilège. En fait, pour une raison qui m'est un peu obscure à moi aussi, dès que tu utilises des procédures ou autres, Oracle vérifie que l'on t'a bien DIRECTEMENT affecté le privilège. Tu as beau essayé d'affecter ce privilège par un rôle, ça ne marche pas.

C'est pour cela que je t'ai fait affecter le privilège CREATE USER à ton user TP. Du coup, si tu fais le compte, il y a 4 choses qui font que TP peut créer des vues : le privilège et les 3 rôles.

[PpPool]

merci encore pour toutes tes explications, c'est maintenant bcq plus clair.
bonne journné