Problème dans procédure stockée avec ALTER SESSION

Version imprimable

28/06/2017, 12h13
seb299792

Problème dans procédure stockée avec ALTER SESSION
Tout d'abord,
bonjour à toutes et à tous,
J'ai plutôt une bonne expertise SQL Server mais là, je dois oeuvrer en Oracle et je sèche sur un problème.

J'ai un ensemble d'utilisateurs (schéma) et un schéma de travail TRAV sur lequel va travailler mon programme.

Je possède une table TABLE1 dans les schéma qui doit être alimenté depuis mon schéma de travail, cette table possède un ID en autoincrément (Trigger en Oracle).

Afin que ma procédure stockée compile, j'ai donc recopié la table dans le schéma TRAV.

Dans ma procédure je dois écrire tantôt dans mon schéma TRAV, tantôt dans d'autre schéma.

J'ai donc utilisé dans ma procédure :
Code:

1 2 -- Set Good Schema execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=' || myschema;
.

Si j'essai l'extrait suivant en dehors de la procédure stockée
Code:

1 2 3 4 ALTER SESSION SET CURRENT_SCHEMA=AutreSchema; INSERT INTO TABLE1 (MonCh) VALUES ('test'); ALTER SESSION SET CURRENT_SCHEMA=TRAV ;
Cela fonctionne parfaitement. En effet le schéma Autreschema possède bien la table et le trigger qui va bien pour renseigner le champs ID.

Lorsque j'exécute ma procédure stockée qui possède la même séquence, j'obtiens le message suivant :

Citation:

Rapport d'erreur -
ORA-01400: impossible d'insérer NULL dans ("TRAV "."TABLE1 "."ID")
ORA-06512: à "TRAV .MAPROCSTOCK", ligne 52
ORA-06512: à ligne 21
01400. 00000 - "cannot insert NULL into (%s)"
*Cause: An attempt was made to insert NULL into previously listed objects.
*Action: These objects cannot accept NULL values.

L'erreur est donc logique si celui ci essai d'insérer dans le schéma TRAV puisque le trigger n'existe pas.
Je peux néanmoins confirmer que le changement de session a lieu puisque dans l'exécution de mes requêtes le current user est bien AutreSchema.

Il doit me manquer quelques chose mais quoi ?

Merci de votre aide.
28/06/2017, 12h56
skuatamad

Je ne suis pas sûr de comprendre exactement le problème tel qu'il est exposé.
Les données doivent être insérées dans la table de AutreSchema ou dans la table de TRAV, ou les 2 ?

Un petit exemple simplifié permettrait de mieux comprendre le besoin.

En attendant, peut être que AUTHID CURRENT_USER correspond à la demande, ou en partie
28/06/2017, 14h11
seb299792

La table est TABLE1
Je dois insérer avec le schéma AutreSchema alors que j'appel ma procédure stockée avec TRAV. Mais attention AutreSchema est un paramétre VARCHAR2 dynamique.

Ok, c'est plutôt l'inverse de AUTHID CURRENT_USER.
Comme le nom du schéma est passé dynamiquement à la procédure, peut êre utiliser du SQL dynamique, par exemple :
Code:

1 2 3 4 5 6 7 create or replace trav.maproc (p_user in varchar2, p_values in varchar2) as l_test varchar2(32); begin l_test := DBMS_ASSERT.SCHEMA_NAME (upper(p_user)); execute immediate 'INSERT INTO '||p_user||'.TABLE1 (MonCh) VALUES (:1)' using p_values; end; /

28/06/2017, 14h44
seb299792

C'est la dernière solution que j'ai envisagée mais il s'agit alors d'interprétation. Je pensais qu'en utilisant

Code:

execute immediate 'ALTER SESSION SET CURRENT_SCHEMA=' || myschema;

Je pouvais me passer de l'interprétation et mettre directement

Code:

INSERT INTO TABLE1 (MonCh) VALUES (maVar)

N'est ce pas possible ainsi ?

En fait, c'est possible mais avec AUTHID CURRENT_USER ;)

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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
SQL>
SQL> @test.sql
SQL> set echo on
SQL>
SQL> drop user trav cascade;
 
Utilisateur supprime.
 
SQL> drop user autre cascade;
 
Utilisateur supprime.
 
SQL>
SQL> create user trav identified by a;
 
Utilisateur cree.
 
SQL> create user autre identified by b;
 
Utilisateur cree.
 
SQL>
SQL> grant connect, resource to trav;
 
Autorisation de privileges (GRANT) acceptee.
 
SQL> grant connect, resource to autre;
 
Autorisation de privileges (GRANT) acceptee.
 
SQL>
SQL> create table autre.table1 (id number primary key, col varchar2(10));
 
Table creee.
 
SQL>
SQL> create sequence autre.s;
 
Sequence creee.
 
SQL>
SQL> create or replace trigger autre.trg
  2  before insert on autre.table1
  3  for each row
  4  declare
  5  begin
  6      :new.id := s.nextval;
  7  end;
  8  /
 
Declencheur cree.
 
SQL> show err
Pas d'erreur.
SQL>
SQL> create table trav.table1 (id number primary key, col varchar2(10));
 
Table creee.
 
SQL>
SQL> grant select, insert on autre.table1 to trav;
 
Autorisation de privileges (GRANT) acceptee.
 
SQL>
SQL> create or replace procedure trav.maproc2(p_schema in varchar2) AUTHID CURRENT_USER as
  2  begin
  3      execute immediate 'alter session set current_schema = ' || p_schema;
  4      insert into table1 (col) values('test');
  5  end;
  6  /
 
Procedure creee.
 
SQL> show err
Pas d'erreur.
SQL>
SQL>
SQL> grant execute on trav.maproc2 to autre;
 
Autorisation de privileges (GRANT) acceptee.
 
SQL>
SQL> disc
Deconnecte de Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn trav
Entrez le mot de passe :
Connecte.
SQL> exec trav.maproc2('autre');
 
Procedure PL/SQL terminee avec succes.
 
SQL> select * from table1;
 
        ID COL
---------- ----------
         1 test
 
SQL> select * from autre.table1;
 
        ID COL
---------- ----------
         1 test
 
SQL> select * from trav.table1;
 
aucune ligne selectionnee
 
SQL>

Mais je ne sais pas si cette approche est vraiment recommandée :
https://asktom.oracle.com/pls/apex/f...59800346315595

Citation:

but in short, you should not be doing this in code that is not a utility (like a bit of code to dump a table to a file for example). This invokers rights is "special"

Mais je ne sais pas pourquoi Tom Kyte le déconseille.

29/06/2017, 09h26
seb299792

Merci à vous skuatamad pour vos réponses.

Rapide, précis, vous avez entièrement répondu à mes questions.