Discussion :

[Keph]

Bonjour.

J'utilise Oracle 8i.

Je début avec Oracle et j'ai besoin d'utiliser une fonction stockée. J'ai écrit la fonction suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE OR REPLACE  FUNCTION "KEPH"
RETURN number 
IS
  id_fam number;
BEGIN
  EXECUTE IMMEDIATE 'INSERT INTO bug_family (id_test, description, id_owner, date_opening)
    VALUES (2, ''tes'', 2, SYSDATE)
    RETURNING id_family INTO :1' INTO id_fam;
  RETURN id_fam;
END;

Ensuite, j'appelle cette fonction avec SQL*Plus en tappant la commande suivante :

Code :

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

select keph() from dual;

Malheureusement, j'obtiens l'erreur suivante :

Code :

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

1
2
3
4
5
6
select keph() from dual
       *
ERROR at line 1:
ORA-01007: variable not in select list
ORA-06512: at "MANG_ADMIN.KEPH", line 4
ORA-06512: at line 1

Je ne vois vraiment pas d'où peut provenir le problème. Le voyez-vous ?

Merci d'avance.

Keph

PS : j'utilise cette fonction stockée pour récupérer l'id de l'enregistrement que je suis en train de créer. Je pourrais récupérer la valeur courante de la séquence qui génére cet id, mais je pourrai alors avoir des problèmes d'accès concurentiel. Je pense pouvoir contourner cette limitation avec la commande RETURNING INTO mais pour cela je dois faire une fonction stockée... C'est fou d'en faire autant pour si peu, non ?

[Magnus]

1/ pourquoi encadrer le nom de votre procédure de guillemets
2/ pour invoquer une procédure, on peut faire :

Code :

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

1
2
3
4
5
 
begin
  ma_proc; -- pas de parenthèse s'il n'y a pas d'arguments
end;
/

ou

Code :

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

1
2
 
exec ma_proc; -- idem

3/ votre idée ne répond pas à votre problématique initiale des accès concurrents.
Pour gérer des identifiants unique, regardez dans la FAQ du côté des séquences ou faîtes une recherche avancée sur ce forum avec ce mot clé

[Keph]

Bonjour et merci pour votre réponse.

1/ J'ai généré cette procedure avec Schema Manager qui ajoute automatiquement les guillemets en question.

2/ Cela ne fonctionne pas mieux dans le cas présent.

3/ J'utilise une séquence pour gérer les identifiants uniques, et des triggers pour les mettre automatiquement lors d'un INSERT (comme on le dit dans la FAQ). Seulement j'aimerais, lors d'un INSERT, récupérer l'identifiant de la ligne que je viens de créer.

[plaineR]

Quand vous encadrez votre nom d'objet par des guillemets, il faut respecter la casse quand vous l'appelez par la suite. Néanmoins il est vivement déconseillé de procéder ainsi...

De toutes façons vous ne pourrez pas appeler une fonction qui fait un insert dans un ordre select, sous peine d'obtenir l'erreur :
ORA-14551: impossible d'effectuer une opération DML dans une interrogation

Enfin, je ne comprends pas pourquoi vous utilisez execute immediate
Pourquoi ne pas faire :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE  FUNCTION KEPH
RETURN number 
IS
  id_fam number;
BEGIN
  INSERT INTO bug_family (id_test, description, id_owner, date_opening)
  VALUES (2, 'tes', 2, SYSDATE)
  RETURNING id_family INTO id_fam;
  RETURN id_fam;
END;
/

[Keph]

C'est curieux, j'ai beau faire (après création de la fonction comme l'a indiqué plaineR) :

Code :

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

1
2
3
4
begin;
keph();
end;
/

ou encore :

Code :

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

call keph();

j'obtiens l'erreur:

Code :

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

1
2
3
4
call keph()
     *
ERROR at line 1:
ORA-06576: not a valid function or procedure name

ou encore:

Code :

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

1
2
3
4
5
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00221: 'KEPH' is not a procedure or is undefined
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Mais je pense que je dois mal m'y prendre. Vous pensez alors que faire une fonction stockée qui réalise un INSERT et qui renvoie l'identifiant est une mauvaise idée ?

[Magnus]

mais vous liser TOUT ce que l'on écrit ?
Pas de parenthèses et pas de mot clé CALL

[Keph]

Pas la peine de s'énerver, j'avais compris en lisant votre message que les parenthèses n'étaient pas obligatoires puisqu'il n'y avait pas de paramètres (comme c'est le cas dans beaucoup de langages comme le Pascal) : j'avais sûrement mal compri.

Concernant le mot clef CALL, de toute façon j'ai la même erreur en tappant EXEC.

Enfin, en reprenant vos listings :

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
19
20
21
22
23
24
25
SQL> CREATE OR REPLACE  FUNCTION KEPH
  2  RETURN number 
  3  IS
  4    id_fam number;
  5  BEGIN
  6    INSERT INTO bug_family (id_test, description, id_owner, date_opening)
  7    VALUES (2, 'tes', 2, SYSDATE)
  8    RETURNING id_family INTO id_fam;
  9    RETURN id_fam;
 10  END;
 11  /
 
Function created.
 
SQL> begin
  2   keph;
  3  end;
  4  /
 keph;
 *
ERROR at line 2:
ORA-06550: line 2, column 2:
PLS-00221: 'KEPH' is not a procedure or is undefined
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

Donc oui, je lis ce que vous écrivez.

Je n'ai pas fait de formation Oracle mais je connais le langage SQL pour ce qui est des choses basiques (INSERT, UPDATE, SELECT, DELETE...).
J'essaie juste de comprendre comment faire une chose aussi triviale que récupérer l'identifiant de la ligne que l'on vient de créer.
Je me suis alors penché vers la commande RETURNING INTO, mais il fallait créer une procédure stockée pour pouvoir utiliser des variables. Alors j'en suis là.

PS : je fait toujours des recherches avant de poster sur un forum. Si je me permet de poser cette question, c'est que je n'ai pas trouvé.

[pifor]

Code :

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

1
2
3
4
5
6
 
declare r number;
begin
r:= keph;
end;
/

[Keph]

Merci pifor, ta procedure fonctionne.

Est-il possible de récupérer la valeur de la variable 'r' de ton exemple dans une commande de type SELECT afin de récupérer cette valeur en PHP ?

[pifor]

Je ne pense pas car:

De toutes façons vous ne pourrez pas appeler une fonction qui fait un insert dans un ordre select, sous peine d'obtenir l'erreur :
ORA-14551: impossible d'effectuer une opération DML dans une interrogation

Cedi dit, il y a peut-être un moyen dans l'API Oracle du client PHP de récupérer des valeurs d'une clause RETURNING ... INTO car c'est possible avec l'API en C.

[pifor]

Voir l'exemple 1585 (avec un CLOB) à étudier ...

[Magnus]

ATTENTION : il ne faut surtout pas confondre les notions de "procédure" et de "fonctions" qui sont très proches :
- une fonction a une valeur de retour qu'il faut forcément affecter lors de l'invocation de la fonction :
- une procédure ne possède pas de valeur de retour et il est donc illégal de tenter d'affecter l'invocation à une variable :

Ex :

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
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
 
10g SOC5> DROP PROCEDURE P_PROC;
 
Procédure supprimée.
 
10g SOC5> CREATE PROCEDURE P_PROC IS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('PROCEDURE');
  4  END;
  5  /
 
Procédure créée.
 
10g SOC5> 
10g SOC5> DROP FUNCTION F_FUNC;
 
Fonction supprimée.
 
10g SOC5> CREATE FUNCTION F_FUNC RETURN NUMBER IS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('FUNCTION');
  4    RETURN 12;
  5  END;
  6  /
 
Fonction créée.
 
10g SOC5> 
10g SOC5> 
10g SOC5> EXEC P_PROC;
PROCEDURE
 
Procédure PL/SQL terminée avec succès.
 
10g SOC5> 
10g SOC5> DECLARE
  2    LC VARCHAR2(100);
  3  BEGIN
  4    LC := P_PROC;
  5  END;
  6  /
  LC := P_PROC;
        *
ERREUR à la ligne 4 :
ORA-06550: Ligne 4, colonne 9 :
PLS-00222: la fonction nommée 'P_PROC' n'existe pas dans cette étendue
ORA-06550: Ligne 4, colonne 3 :
PL/SQL: Statement ignored
 
 
10g SOC5> 
10g SOC5> EXEC F_FUNC;
BEGIN F_FUNC; END;
 
      *
ERREUR à la ligne 1 :
ORA-06550: Ligne 1, colonne 7 :
PLS-00221: 'F_FUNC' n'est pas une procédure ou est indéfini
ORA-06550: Ligne 1, colonne 7 :
PL/SQL: Statement ignored
 
 
10g SOC5> 
10g SOC5> DECLARE
  2    LN NUMBER(10);
  3  BEGIN
  4    LN := F_FUNC;
  5  END;
  6  /
FUNCTION
 
Procédure PL/SQL terminée avec succès.

[Keph]

Très bien, merci pour vos éclaircissements.

Je vous tiendrai au courant de mon avancement.

[Keph]

Hello.

J'ai finalement réussi à résoudre mon problème. J'étais sur une mauvaise voie avec ma fonction stockée, mais pifor m'a remis sur la bonne piste.

Je vous livre mon code PHP pour faire l'INSERT et récupérer l'ID de l'enregistrement créé :

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
// Connection à la base
$conn = OCIlogon('***', '***', '***');
 
// Requête SQL
$sql = "INSERT INTO table (description) VALUES ('test') RETURNING id INTO :id";
 
// Parsing
$stid = OCIParse($conn, $sql);
 
// Execution de la premiere requête
$id = '';
OCIBindByName($stid, ":id", $id, 5);
ociexecute($stid, OCI_DEFAULT);
ocicommit($conn);
 
echo "L'id est : $id";