[SQL Dynamique] Problème d'exécution d'une requete d'update dans une fonction

Version imprimable

09/01/2007, 16h57
lordashram

[SQL Dynamique] Problème d'exécution d'une requete d'update dans une fonction
Bonjour,
Je suis sur Oracle 9i et je développe une application pour les cours et je suis bloqué sur un points non vu.
On doit faire un logiciel PHP/Oracle, si j'ai bien compris, la seule façon de "parler" avec le PHP c'est les fonctions. Me voilà parti à en exécuter une
Voilà mon problème :
J'ai créé une fontion qui va renvoyer 1 ou 0, jusque là aucun problème.
Mais dans ma fonction, je me suis rendu compte que je pouvais faire de la maintenant en modifiant la base profitant de mes tests.

Bref je fais tous plein de tests pour savoir si je vais renvoyer 1 ou 0, je fais mes select avec INTO ça marche très bien. Je veux faire un update.
J'ai vérifié, ma requete marche très bien en test en direct donc elle est bonne.
J'ai vérifié dans une procédure pour profiter du dbms_output, elle est toujours bonne. Donc le problème ne vient pas de la requete. hors j'ai l'erreur :
impossible d'effectuer une opération DML dans une interrogation

Je me suis dit, OK on va procéder autrement par un execute immediate.
J'ai envoyé la requete dans une variable et j'ai evoyé un execute immediate :
Code:

1 2 3 ligne_commande:={mon update...je vous épargne la taille de la ligne}; execute immediate ligne_commande;
Hors j'obtiens l'erreur ORA-00911: Caractère non valide
Pile sur la ligne
Code:

1 2 execute immediate ligne_commande;
Voilà, ayant repris le code d'une des question résolue ici, je comprend vraiment pas ce qui ne va pas. Que ce soit dans une solution comme dans l'autre.

En vous remerciant d'avance.
09/01/2007, 17h20
orafrance

le problème étant que tu nous épargnes la commande exécutée, j'vois mal comment on pourrait t'aider... :koi: en plus, pour du débuggage, je pense que tu sauras te débrouiller seul en affichant le contenu de ligne_commande et en le copiant sans SQL*Plus ;)
09/01/2007, 17h29
pifor

Peut-être qu'il suffit juste de supprimer le ';' final: il est nécessaire dans SQL*Plus mais il ne faut pas l'utiliser avec EXECUTE IMMEDIATE.

Ok vous l'aurez voulu :
Code:

1 2 3 4 ligne_commande:='UPDATE journee SET hfin_matin_jour=to_timestamp(''' || to_char(heure_depointage,'DD/MM/YY HH24:MI:SS') || ''',''DD/MM/YY HH24:MI:SS'') WHERE to_char(date_jour,''DD/MM/YY'')=''' || to_char(temps,'DD/MM/YY') || ''' AND rf_id_pers=' || pers; execute immediate ligne_commande;
Voilà la ligne commande qui est exécutée, sinon pour l'histoire du ';' c'est pas ça, j'ai tenté sans le ';' c'est la même chose.
Et pour le contenu de la ligne, tout est correct.

09/01/2007, 20h44
Yorglaa

Salut à tous,
pour autant que je sache, il n'est pas possible avec Oracle de faire du DML (insert - update - delete) dans une FONCTION. Laquelle ne peut être invoquée que depuis un Select ou une assignation de valeur à une variable, ou encore un test de valeur de variable.

avec Oracle il faut faire une PROCEDURE qui retourne un paramètre OUT.
09/01/2007, 21h06
lordashram

OK, peut-on, appeler une procédure à partir d'une fonction?Si oui comment, ça serait une feinte pour mon problème...
09/01/2007, 21h25
Yorglaa

oui et non...
enfin oui tu peux appeler une procédure dans le code d'une fonction, mais non Oracle vas pas se laisser feinter comme ça... si l'appel initial est une fonction, le DML est interdit, même en cascade à travers une procédure imbriquée.

voyons le problème en amont : pourquoi as-tu impérativement besoin d'une fonction ?
09/01/2007, 21h33
lordashram

En fait pour tout expliquer on fait un logiciel de pointage PHP/Oracle.
En gros je dois donner au php l'information si la personne peut pointer ou pas.

Dans la fonction je teste si le tuple qui contient le jour et la personne concernée existe dans la base, ça marche bien ça. Les select il n'y a pas de souci.
Par contre si je trouve pas le tuple, je souhaite le créer par exemple d'où un insert (d'ailleur même "bug").

Si la personne a déjà pointé le matin mais n'a pas dépointé à midi et qu'il est plus d'une certaine heure, je souhaite le dépointer en auto par le fameux update.

pour simplifier voilà le shémas de la table sur laquelle je travaille :
Table journee
-----------------------------------------------------------------

date_jour date
hdeb_matin_jour timestamp
hfin_matin_jour timestamp
hdeb_am_jour timestamp
hfin_am_jour timestamp
#rf_id_pers integer

-----------------------------------------------------------------

En gros s'il n'y a pas de donnée c'est qu'il a pas pointé tout simplement.
Pour chaque journée faite par la personne on crée un nouveau tuple.
(je sais c'est pas top comme chémas bdd...)

Voilà en gros, si le tuple existe pas j'aimerais pouvoir le créer.
Et donc qu'ensuite la personne peut se loguer donc renvoyer
l'info au php => 1 (0 en cas de pas de possibilité) d'où le fait
que j'ai besoin d'une fonction.
09/01/2007, 21h58
Yorglaa

juste come ça, à vue de nez et à cette heure-ci, il me semble possible de tout faire passer par une procédure plutôt que par une fonction dans un Select...

en gros, je verrais un principe du genre :
- depuis php tu fais un appel de procédure Oracle, en lui fournissant les paramètres (IN) qui vont bien... personne_id et current_timestamp par exemple et un paramètre de sortie (OUT) qui te donne le résultat final.

1. dans la procédure Oracle tu traite tous tes cas... par exemple : tu commences par ton test d'existence personne - jour de timbrage si oui tu continue le traitement si non (en gèrant l'exception no_data_found) tu fais l'insert voulu...
2. ensuite tu testes l'éventuelle entrée/non-sortie en fonction de la "certaine heure" que tu cites (mise dans une autre variable dans ta proc Oracle) en la comparant avec ton paramètre current_timestamp. là aussi tu peux facilement faire du conditionnel avec des IF ou des Select en gèrant l'exception no_data_found qui envoient les updates qui vont bien.
3. au final, en fonction de tes règles métier, tu renseigne le paramètre OUT avec 0 ou 1 selon que tu considère l'opération réussie ou pas...

voilà, c'est très schématiquement suggéré, mais je pense qu'il y a moyen de faire qqchose comme ça...
09/01/2007, 22h14
lordashram
Ok, je comprend, je sais que c'est pas le lieu pour faire des déclarations d'amour, mais je t'aime :oops: ça se trouve sans le savoir tu viens de nous sauver (moi et mon binome) pour ce projet.
Je vais passer par une procédure donc.
Si j'ai bien compris le principe, le IN c'est le paramètre passé en entrée mettons :
Code:

1 2 3 4 5 CREATE OR REPLACE PROCEDURE toto ( pers IN INTEGER )
par contre pour le OUT j'ai pas encore bien compris...
je suis en train de regarder le tuto de développez.com sur le PL/SQL c'est pas encore super super bien expliqué (si je peux me permettre)...

Pour le moment à priori si j'ai bien compris je pourrais déclarer comme ça :
Code:

1 2 3 4 5 6 7 8 9 CREATE OR REPLACE PROCEDURE toto ( pers IN INTEGER ,pointe IN OUT NUMBER ) IS BEGIN {...} bla bla bla je fais tous mes tests et je renseigne pointe avec 1 ou 0 END;
Je me trompe?

Merci encore , vraiment merci:P

oui, c'est plus ou moins ça...
voilà un petit début, plein de trous à compléter ;-) ...

mais ça pourrait bien te donner des idées..

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
Create or Replace Procedure Pointage (
                                        nPers_id    IN  Number
                                        , tCurrent  IN  TimeStamp
                                        , pResult   OUT pls_Integer
                                     )
Is
    nTest                   Number ;
    tLimitTime              TimeStamp := to_timestamp( bla bla bla... la limite à considérer pour après-midi) ;
 
Begin
    -- on commence par le test d'existence, encapsulé dans un bloc Begin / End pour pouvoir avoir un bloc "Exception" juste pour cette partie
    Begin
        Select id_pers
        Into    nTest
        From    journee
        Where   (
                  hdeb_matin_jour = aujourdhui -- (je te laisse coder une peu quand même)
                    Or
                  hdeb_am_jour = aujourdhui -- on teste aussi l après-midi, si qqun avait congé le matin)
                )
        And     id_pers = nPers_id ; -- c'est la paramètre IN no 1
 
        Exception
        When No_data_found  -- la ligne n'existe pas
        Then
                Insert into journee
                Values ( bla, bla, bla) ;
 
    End ;
 
    -- ensuite on teste les heures
    Begin
 
        If comparaison entre tCurrent (le paramète IN no2) et tLimitTime
        Then
            le traitement approprié ... dépend du test que tu fais en dessus ;-) ;
        Elsif un éventuel autre test...
        Then
            un autre insert, update ou autre...
        End  If ;
 
    End ;
 
    Commit ;
    pResult := 1 ; -- succès !!
 
    Exception
    When Others -- toute exception non gérée plus haut
    Then
        Rollback ;
        pResult := 0 ; -- Echec !
 
 
End ;

ps j'ai fais a en vitesse sans trop checker la syntaxe et tout ça... alors indulgence !

09/01/2007, 22h55
Yorglaa

j'ajoute juste que comme alternative, tu peux aussi déterminer le current_timestamp dans une variable en début de procédure et pas comme un paramètre IN... ça évite de devoir le donner en paramètre, donc d'éviter les tricheries potentielles (l'heures courante sera celle du serveur DB, donc hors de portée du User frauduleux) !

mais ça dépend du mode de saisie :
- le user saisit son pointage via son PC (dont il peut modifier l'horloge) ?
- c'est une machine automatique ?
- autre ?

le risque étant un serveur DB situé sur un autre fuseau horaire (pour les multi nationales) où il faudra aussi gèrer le paramètre TimeZone dans la procédure...

Citation:

Envoyé par Yorglaa

pour autant que je sache, il n'est pas possible avec Oracle de faire du DML (insert - update - delete) dans une FONCTION. Laquelle ne peut être invoquée que depuis un Select ou une assignation de valeur à une variable, ou encore un test de valeur de variable.

Bonjour Yorglaa,
Je me permets de modérer tes propos ;) , il est tout à fait possible de faire du DML sous Oracle ... en PL/SQL. Par contre il n'est pas possible d'utiliser cette fonction dans un ordre DML :
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 SQL> create table t (a number); Table created. SQL> create or replace function f return number is 2 begin 3 insert into t values (1); 4 return 1; 5 end; 6 / Function created. SQL> declare 2 a number; 3 begin 4 a := f; 5 end; 6 / PL/SQL procedure successfully completed. SQL> select * from t; A --------- 1 SQL> select f from dual; select f from dual * ERROR at line 1: ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "COMPTA.F", line 3 ORA-06512: at line 1

10/01/2007, 10h09
Yorglaa

@plaineR

merci pour cette précision !
mais il est vrai que dans le cas qui nous occupe, notre ami essayait d'utiliser cette fonction dans une Select...

Ceci dit, je préfère largement ne pas faire de DML dans les fonctions et utiliser les procédures pour ça...
ça me permet aussi de ne pas avoir à me poser de question lorsque je veux utiliser une fonction dans un Select... je sais qu'elle fonctionnera, et qu'il n'y aura jamais de DML dans une fonction que j'aurais développé !

mais chaque développeur aura sa popre politique en la matière !
10/01/2007, 10h12
plaineR

Citation:

Envoyé par Yorglaa

Ceci dit, je préfère largement ne pas faire de DML dans les fonctions et utiliser les procédures pour ça...
ça me permet aussi de ne pas avoir à me poser de question lorsque je veux utiliser une fonction dans un Select... je sais qu'elle fonctionnera, et qu'il n'y aura jamais de DML dans une fonction que j'aurais développé !

Je suis entièrement d'accord avec toi ;)