Discussion :

[PhacoNinja]

Bonjour à tous,

Dans la création d'une fonction/procédure, je souhaiterai savoir si dans la définition du CURSOR, il est possible, via les parametres passés à la fonction/procédure, de passer le nom d'une table ou schéma ?
Non parce que j'ai beau essayé mais la compilation ne passe pas, damned !!!

Code :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION ODS.F_DELETE (pSchema IN VARCHAR2, pTable IN VARCHAR2)
RETURN NUMBER
IS
 
CURSOR curs
SELECT *
FROM pSchema.pTable
WHERE DATE < sysdate;
 
BEGIN

Merci d'avance pour votre aide.

[ZIED]

il faut utiliser Cursor curs IS

Code :

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

1
2
3
4
5
6
7
 
CURSOR curs is
SELECT *
FROM pSchema.pTable
WHERE DATE < sysdate;
 
BEGIN...

[PhacoNinja]

Il s agit une erreur de saisie de ma part. J ai bien fait comme tu l'indiques mais la fonction ne compile pas. Le problème persiste. Any Idea ?

[Waldar]

Il faut passer par du SQL Dynamique.
Mais si c'est pour faire des opérations sur une table, c'est une procédure stockée dont vous avez besoin, pas une fonction.

[grosminetos]

Il est possible dans la declaration du curseur de lui indiquer qu'il y aura un passage de parametre comme avec une fonction ou une procedure .

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DECLARE
CURSOR curs (vdt date) IS
SELECT *
FROM pSchema.pTable
WHERE DATE < vdt;
 
BEGIN...
OPEN cur;
FETCH curs (vdt ) INTO .....
CLOSE cur;
END;

il y a aussi ;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
declare 
Qry clob := null;
BEGIN
Qry := 'SELECT ch1,ch2 FROM pSchema.pTable WHERE DATE < sysdate';
FOR i IN Qry
LOOP
i.ch1 ......
i.ch2 ......
END LOOP;
 
EXCEPTION 
WHEN OTHERS then .....
END;

L'instruction FOR permet d'effectuer le OPEN FETCH et le CLOSE AUTOMATIQUEMENT !!!! beaucoup plus simple !!!!

[PhacoNinja]

On peut donc utiliser du SQL Dynamique pour la définition d'un CURSOR ?

Sauf erreur de ma part, il est possible de faire des actions (INSERT, UPDATE, ...) sur une table directement au sein d'une fonction via SQL Dynamique. Ce n'est peut être pas "conventionnel" de faire ces actions dans une fonction mais ca marche.

J'utilise, peut être à tort, une fonction parce que j'ai besoin de récupérer les codes retours des différentes instructions afin d'être exploités par un shell.

[grosminetos]

en dynamique tu peux faire un grand nombre de trucs

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
declare 
qry      clob := null;
BEGIN
    qry := 'DECLARE 
                  definition fields 
                .....
             BEGIN 
                ........
                traitements
                ........
             EXCEPTION
                WHEN ......
            '
EXCECUTE IMMEDIATE qry;
....
END;

[PhacoNinja]

Merci pour tes retours grosminetos.
J'ai essayé ta 2ème solution mais j'ai tout de même une erreur lors de la compilation : PLS-00456 : item vCursor is not a cursor.
J'ai beau transformer ma fonction en procédure mais cela ne veut toujours pas compiler.
What happen ?

Code sql :

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
CREATE OR REPLACE PROCEDURE ODS.P_DEL_PROCESS (
   pAppli       IN   VARCHAR2,
   pSource      IN   VARCHAR2,
   pSchema      IN   VARCHAR2
   )
 AS
BEGIN
DECLARE
 
   vCursor   clob :=null;
 
BEGIN
 
--  vCursor := 'SELECT DTR.TABLE_NAME, DTR.TABLE_PK_NAME, DTR.COLUMN_NAME, DTR.COLUMN_VALUE, DTR.ORDERING, VDM.PK_ID
--                   FROM ' || pSchema || '.' || vSource || ' VDM
--                   JOIN   ODS.DEL_TAB_REL DTR
--                        ON (VDM.VIEW_APPLI =  DTR.VIEW_APPLI
--                             AND VDM.PK_NAME = DTR.PK_NAME)
--                   JOIN   ODSSHIVA.DEL_TAB_DATE DTD
--                        ON (VDM.snaptime > DTD.DATE_DEL)
--                   WHERE VDM.APPLICATION = '''|| pAppli || ''' 
--                   AND DTR.COLUMN_NAME IS NULL
--                   ORDER BY DTR.ordering;';
 
 vCursor := 'SELECT DTR.TABLE_NAME, DTR.TABLE_PK_NAME, DTR.COLUMN_NAME, DTR.COLUMN_VALUE, DTR.ORDERING, VDM.PK_ID
                   FROM ODS.T_DEL_MI_VA_APPLI VDM
                   JOIN   ODS.DEL_TAB_REL DTR
                        ON (VDM.VIEW_APPLI =  DTR.VIEW_APPLI
                             AND VDM.PK_NAME = DTR.PK_NAME);';
 
 FOR r1 IN vCursor
 
 LOOP

[PhacoNinja]

grosminetos, j'ai également testé ta première solution :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
DECLARE
CURSOR curs (vdt date) IS
SELECT *
FROM pSchema.pTable
WHERE DATE < vdt;
 
BEGIN...
OPEN cur;
FETCH curs (vdt ) INTO .....
CLOSE cur;
END;

Le problème est que pSchema et pTable étant des paramètres de la fonction/procédure, la compilation plante avec le message d'erreur suivant : ORA-00942 : Table or view does not exist pour FROM pSchema.pTable.
Je dois surement mal faire quelque part, mais ou et quoi ?

[skuatamad]

Lisez le tutoriel sur le sql dynamique, vous devez concaténer les variables à votre requête.

Cependant en fonction du besoin, il est possible qu'une solution tout dynamique soit inappropriée.

[grosminetos]

vCursor := 'SELECT DTR.TABLE_NAME, DTR.TABLE_PK_NAME, DTR.COLUMN_NAME, DTR.COLUMN_VALUE, DTR.ORDERING, VDM.PK_ID
FROM ODS.T_DEL_MI_VA_APPLI VDM
JOIN ODS.DEL_TAB_REL DTR
ON (VDM.VIEW_APPLI = DTR.VIEW_APPLI
AND VDM.PK_NAME = DTR.PK_NAME);';

PhacoNinja
Le probleme vient du fait que tu as mis un ';' a l'INTERIEUR de la chaine : il faut utiliser :

vCursor := 'SELECT DTR.TABLE_NAME, DTR.TABLE_PK_NAME, DTR.COLUMN_NAME, DTR.COLUMN_VALUE, DTR.ORDERING, VDM.PK_ID
FROM ODS.T_DEL_MI_VA_APPLI VDM
JOIN ODS.DEL_TAB_REL DTR
ON (VDM.VIEW_APPLI = DTR.VIEW_APPLI
AND VDM.PK_NAME = DTR.PK_NAME)';

Skuatamad à raison il ne faut pas fair du dynamique histoire de faire du dynamique , il y a les curseurs avec parametres.....