Re: PL SQL Cursor beginner

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Wed, 09 Mar 2005 19:30:53 +0100
Message-ID: <d0nf86$1n3$1_at_news3.zwoll1.ov.home.nl>


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
Received on Wed Mar 09 2005 - 19:30:53 CET

Original text of this message