PL SQL Cursor beginner

[mrique]

Hi
I'm beginning with PL SQL and try to learn cursors usage
I get ora 920 with this on line "WHERE WHERE_NORME "
What i'm trying to do is to fetch where conditions stored in a table,
and reuse it in dml order in my cursor.. but I must have missed
somethin ? any pl sql killer having one minute for me ? thank's

CREATE OR REPLACE PROCEDURE Feed_Anomalie AS
CURSOR NORMES IS
SELECT ID_NORME, TABLE_NORME, WHERE_NORME FROM QTE_NORMES ORDER BY
ID_NORME;
ID_NORME QTE_NORMES.ID_NORME%TYPE;
TABLE_NORME QTE_NORMES.TABLE_NORME%TYPE;
WHERE_NORME QTE_NORMES.WHERE_NORME%TYPE;

BEGIN
OPEN NORMES;

LOOP
FETCH NORMES INTO ID_NORME , TABLE_NORME , WHERE_NORME ;
EXIT WHEN NORMES%NOTFOUND;
INSERT INTO QTE_ENREGISTREMENT_NC
(NUM_DOSS, DATE_NC, LAST_DATE_NC, REF_NORME)
SELECT DISTINCT CLE_DOSS, SYSDATE, SYSDATE, ID_NORME
FROM (
SELECT
DOSSIER.CLE_DOSS, SYSDATE, SYSDATE, ID_NORME
FROM TABLE_NORME
WHERE WHERE_NORME
MINUS
SELECT QTE_ENREGISTREMENT_NC.NUM_DOSS , SYSDATE, SYSDATE, ID_NORME
FROM QTE_ENREGISTREMENT_NC );
END LOOP;
CLOSE NORMES;
END;
/

[Frank van Bortel]

mrique wrote:

Hi
I'm beginning with PL SQL and try to learn cursors usage
I get ora 920 with this on line "WHERE WHERE_NORME "
What i'm trying to do is to fetch where conditions stored in a table,
and reuse it in dml order in my cursor.. but I must have missed
somethin ? any pl sql killer having one minute for me ? thank's

CREATE OR REPLACE PROCEDURE Feed_Anomalie AS
CURSOR NORMES IS
SELECT ID_NORME, TABLE_NORME, WHERE_NORME FROM QTE_NORMES ORDER BY
ID_NORME;
ID_NORME QTE_NORMES.ID_NORME%TYPE;
TABLE_NORME QTE_NORMES.TABLE_NORME%TYPE;
WHERE_NORME QTE_NORMES.WHERE_NORME%TYPE;

BEGIN
OPEN NORMES;

LOOP
FETCH NORMES INTO ID_NORME , TABLE_NORME , WHERE_NORME ;
EXIT WHEN NORMES%NOTFOUND;
INSERT INTO QTE_ENREGISTREMENT_NC
(NUM_DOSS, DATE_NC, LAST_DATE_NC, REF_NORME)
SELECT DISTINCT CLE_DOSS, SYSDATE, SYSDATE, ID_NORME
FROM (
SELECT
DOSSIER.CLE_DOSS, SYSDATE, SYSDATE, ID_NORME
FROM TABLE_NORME
WHERE WHERE_NORME
MINUS
SELECT QTE_ENREGISTREMENT_NC.NUM_DOSS , SYSDATE, SYSDATE, ID_NORME
FROM QTE_ENREGISTREMENT_NC );
END LOOP;
CLOSE NORMES;
END;
/

You cannot build a where clause like this; you
need dynamic sql, e.g.
execute immediate ('QTE_ENREGISTREMENT_NC
(NUM_DOSS, DATE_NC, LAST_DATE_NC, REF_NORME)
SELECT DISTINCT CLE_DOSS, SYSDATE, SYSDATE, ID_NORME
FROM (
SELECT
DOSSIER.CLE_DOSS, SYSDATE, SYSDATE, ID_NORME
FROM TABLE_NORME
WHERE :W ' using where_norme;

Due to the overhead in parsing, etc, this is not
a way of coding I would advocate
--
Regards,
Frank van Bortel

[mrique]

thank you I did not know about dynamique sql so I found some tutorial and will learn

[mrique]

thank you I did not know about dynamique sql so I found some tutorial and will learn