Procédure stockée : INSERT partiel

Version imprimable

05/10/2016, 11h49
CoderCool

Procédure stockée : INSERT partiel

Bonjour,

J'ai créé une procédure stockée afin de mettre à jour une table. Cette procédure est très simple (ce qui me va bien) car elle se contente de supprimer les lignes d'une table correspondant à un paramètre passé à la procédure et de recharger les nouvelles lignes selon le même paramètre. Donc un DELETE et un INSERT.
Lorsque je joue la procédure elle n'insère rien. Si je modifie ma clause WHERE (en fait je la supprime), elle m'insère quelque ligne mais je suis très loin des 20000 attendues.
Si je joue ma requête manuellement (clause WHERE incluse) elle me remonte bien les 20000 lignes.
N'étant pas du tout un expert en PL/SQL je sollicite vos lumières (mon ami google ne pas aidé, mais j'ai certainement mal cherché)
Merci pour votre aide
05/10/2016, 12h26
Waldar

Sans voir votre code ni ne pouvoir l'appliquer sur jeu de test c'est très compliqué de vous aider.

Précisions

Hello,
Merci pour la réponse, effectivement il manque un peu de code ;)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
 
CREATE OR REPLACE PROCEDURE p_Test ( v_TYP_QW IN VARCHAR2)
IS
    v_Nothing_ToDO NUMBER(2) := 0;
 
BEGIN
 
    IF v_TYP_QW='ST' THEN
 
        DELETE STA_TB WHERE STA_TYPE_QWERY = v_TYP_QW;
 
        INSERT INTO STA_TB
        SELECT ALLTB.OWNER , ALLTB.TABLE_NAME            
        FROM ALL_ALL_TABLES ALLTB 
       WHERE ALLTB.OWNER LIKE 'A%' OR (ALLTB.OWNER LIKE 'BB%'AND ALLTB.TABLE_NAME LIKE'CC%');        
    ELSE  
         /* Nothing to do */
         v_Nothing_ToDO := 1;
    END IF ;
 
END;

Sur votre procédure :
Elle ne peut pas être valide, vous avez au moins une colonne STA_TYPE_QWERY dans votre table STA_TB, hors elle n'est pas alimentée dans l'insert et je ne vois ni un owner ni un table_name aller dedans.
Votre ELSE IF et votre variable ne servent à rien.

De manière générale, utiliser DBMS_OUTPUT pour contrôler à chaque étape intermédiaire, par exemple :

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
create or replace procedure p_test (v_typ_qw in varchar2)
is
 
begin
    dbms_output.put_line('TYP_QW : ' || v_typ_qw);
 
    if v_typ_qw = 'ST' then
 
        delete from sta_tb
         where sta_type_qwery = v_typ_qw;
 
        dbms_output.put_line('Nb lignes delete : ' || to_char(sql%rowcount));
 
        insert into sta_tb
        select alltb.owner
             , alltb.table_name      
          from all_all_tables alltb 
         where  alltb.owner      like 'A%'
            or (alltb.owner      like 'BB%'
           and  alltb.table_name like 'CC%');
 
        dbms_output.put_line('Nb lignes insert : ' || to_char(sql%rowcount));
    end if;
 
end;
/

Et appelez votre procédure de cette façon :

Code:

1
2
3
4
5
6
7
8
9
set serveroutput on
 
begin
    dbms_output.enable;
    p_test('TOTO');
    p_test('ST');
 -- rollback; -- A vous de voir
end;
/

05/10/2016, 20h54
mnitu

Citation:

Envoyé par CoderCool

Bonjour,

....N'étant pas du tout un expert en PL/SQL je sollicite vos lumières (mon ami google ne pas aidé, mais j'ai certainement mal cherché)

Dans la base Oracle il y a deux moteurs: un moteur SQL et un autre PL/SQL. Dans PL/SQL lors de l'exécution d'une requête SQL le contrôle passe au moteur SQL de la base et après l'exécution de la requête le contrôle reviens dans PL/SQL Toute cela c'est pour vous dire que lorsque vous exécutez la requête sous sqlplus ou autre outil interactif puis via l'invocation de la procédure c'est toujours le même moteur SQL qui l'exécute!
Par conséquence si vous avez des différences des comportements vous devez chercher les cause ailleurs: la requête n'est pas identique, la base n'est pas la même, la table d'origine des données a été modifiée par un autre utilisateur, etc.

Suite

Bonjour,

Merci pour cette réponse.
Voici mon code en cours de test :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
 
CREATE OR REPLACE PROCEDURE p_LIST2 ( v_TYP_QW IN VARCHAR2)
IS
    -- v_Nothing_ToDO NUMBER(2) := 0;
 
BEGIN
 
    IF v_TYP_QW='STAT_COUNT' THEN
 
        DELETE STA_QUERIES WHERE STA_TYPE_QWERY = v_TYP_QW;
 
        INSERT INTO STA_QUERIES
 
            SELECT ALLTB.OWNER , ALLTB.TABLE_NAME ,'QUERY TEST' ,'TEST STAT_COUNT' FROM ALL_ALL_TABLES ALLTB ;
 
            dbms_output.put_line('Nb lignes insert STAT_COUNT : ' || to_char(sql%rowcount));
       ELSE
             INSERT INTO STA_QUERIES  SELECT ALLTB.OWNER , ALLTB.TABLE_NAME , 'blabla query', 'TEST'  FROM ALL_ALL_TABLES ALLTB;
 
             dbms_output.put_line('Nb lignes insert ELSE : ' || to_char(sql%rowcount));
       END IF;
END;

Si j'execute la procedure elle passe bien dans la partie 'STAT_COUNT' mais n'insère que 60 lignes. Si je joue la requête de la condition 'STAT_COUNT' je retourne 21991 lignes;
Y-a-t-il une limite dans le SGBD, un paramètre que sur lequel je devrais jouer pour retourner toutes mes lignes ?

06/10/2016, 14h05
mnitu

Arrêtez de croire dans des phénomènes paranormaux!

Quand vous exécutez disons via sqlplus, la requête Select ... elle vous renvoie 21991 lignes.
Quand vous exécutez toujours sous sqlplus la requête insert into ... Select ... donc en dehors de votre procédure, elle crée combien des lignes ?
06/10/2016, 15h15
CoderCool

suite

A mon age je ne crois plus trop au paranormal ;) En revanche je veux bien une explication. Elle existe, c'est certain et je parie que je suis la cause du souci en ne faisant pas correctement quelque chose mais quoi ?
Pour répondre à la question de MNITU (je n'ai pas dû être très clair) j'insère 21991 lignes en INSERT (...) SELECT (...)
et si je lance la procédure via

Code:

exec p_List2('STAT_COUNT')

Je n'insère que 60 lignes. :(
06/10/2016, 15h34
CoderCool

Fin

Un gentil DBA m'a donné la réponse : en PL_SQL les grant role ne sont pas remontés donc je n'ai pas les mêmes droits. Voilà

Tant mieux!
Voici un jeu d'essai au minima mais qui reproduit votre scénario
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 SQL> set serveroutput on SQL> create table t_users as select * from all_users where 1 = 2 2 / Table created SQL> create or replace procedure load_t_users is 2 begin 3 delete t_users; 4 insert into t_users select * from all_users; 5 dbms_output.put_line (sql%rowcount||' lignes insèrées'); 6 end; 7 / Procedure created SQL> insert into t_users select * from all_users 2 / 65 rows inserted SQL> exec load_t_users 65 lignes insèrées PL/SQL procedure successfully completed SQL>
Comme je vous ai indiqué quand le moteur PL/SQL arrive à exécuter la requête (insert into select ...) il passe la main au moteur SQL et il attends le retour et donc par voie de conséquence j'ai bien le même numéro des enregistrements crées dans le deux cases: appel de la procédure et insert direct.
Maintenant en quoi votre cas diverge de celui ci ?

06/10/2016, 15h47
mnitu

Citation:

Envoyé par CoderCool

Un gentil DBA m'a donné la réponse : en PL_SQL les grant role ne sont pas remontés donc je n'ai pas les mêmes droits. Voilà

Désolé, mais je ne vois pas comment "les grant des roles" peuvent impacter le nombre des enregistrements.
06/10/2016, 15h48
CoderCool

Dixit le DBA de mon client, lorsque mon code s'exécute en PL/SQL, je n'ai pas les rôles suffisants. J'avoue ne pas maîtriser la chose mais il m'a ouvert les droits nécessaires et je parviens à exécuter correctement ma procédure.
Merci pour votre aide.
06/10/2016, 16h33
mnitu

Un rôle c’est un regroupement identifié (nommé) des privilèges.

Les privilèges viennent en deux catégories : privilèges system et privilèges objets. Dans notre cas les privilèges system on peut les ignorer.

Les privilèges objets sont ceux qui permettent l’accès aux tables, vues, etc. Exemple : je peux interroger ou bine modifier les enregistrements d'une table, etc. Mais ces privilèges sont manichéistes : j’ai le droit d’interroger ou pas disons la table en question. Elles ne permettent pas de gérer des aspects de restriction pour des raisons de confidentialités, de type je ne peux pas sélectionner le salaire des employées (colonne) ou je ne peux voir que les données de collègues qui travaillent dans le même département que moi (ligne). Pour cela Oracle offre des solutions mais qui ne sont pas basées sur les rôles.

Bon courage pour la suite.