CODEWITHSUNDEEP
oracle-databaseplsqldatabase-cursor
Comic Coder
Comic Coder

Reputation: 549

PLS-00103 error when trying to fetch from a cursor

I'm trying to write a cursor. I try to match the syntax of examples but always getting compile failure on the FETCH statement.

CREATE OR REPLACE PROCEDURE IFSAPP.CLEAR_OLD_PURCHASE_ORDERS (cPlannedDelDate in varchar2) IS

     -- cursor to get all the purchase orders's that have lines in released state that 
    CURSOR c1 IS 
        SELECT DISTINCT PO.ORDER_NO
        FROM PURCHASE_ORDER PO, PURCHASE_ORDER_LINE_NOPART POLN
        WHERE PO.ORDER_NO = POLN.ORDER_NO 
        AND POLN.STATE = 'Released'
        AND POLN.PLANNED_DELIVERY_DATE < TO_DATE(cPlannedDelDate, 'DD/MM/YYYY');

BEGIN

    DECLARE corder_no varchar2(12);

    OPEN c1;    
    LOOP
        FETCH c1 INTO corder_no;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(corder_no);
    END LOOP;
    CLOSE c1;
END CLEAR_OLD_PURCHASE_ORDERS;
/

LINE/COL ERROR
-------- -----------------------------------------------------------------
17/9     PLS-00103: Encountered the symbol "FETCH" when expecting one of the following:
         constant exception <an identifier>
         <a double-quoted delimited-identifier> table columns long
         double ref char time timestamp interval date binary national
         character nchar
21/5     PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following:
         end not pragma final instantiable order overriding static
         member constructor map

Can anyone see where I am going wrong?

Upvotes: 0

Views: 880

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

The problem is actually where you're declaring your local variable, and the use of the DECLARE keyword. That's starting a new inner PL/SQL block, but you then have the OPEN etc. without continuing that pattern with a new BEGIN.

You don't need a sub-block though, just move the local variable declaration up before the existing BEGIN, and lose the extra DECLARE:

CREATE OR REPLACE PROCEDURE IFSAPP.CLEAR_OLD_PURCHASE_ORDERS (cPlannedDelDate in varchar2) IS

     -- cursor to get all the purchase orders's that have lines in released state that 
    CURSOR c1 IS 
        SELECT DISTINCT PO.ORDER_NO
        FROM PURCHASE_ORDER PO, PURCHASE_ORDER_LINE_NOPART POLN
        WHERE PO.ORDER_NO = POLN.ORDER_NO 
        AND POLN.STATE = 'Released'
        AND POLN.PLANNED_DELIVERY_DATE < TO_DATE(cPlannedDelDate, 'DD/MM/YYYY');

    corder_no varchar2(12);

BEGIN

    OPEN c1;    
    LOOP
        FETCH c1 INTO corder_no;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(corder_no);
    END LOOP;
    CLOSE c1;
END CLEAR_OLD_PURCHASE_ORDERS;
/

Incidentally, you should consider using ANSI join syntax, not the ancient comma-separated-FROM clause syntax. And it would be simpler to use an implicit cursor loop:

CREATE OR REPLACE PROCEDURE IFSAPPCLEAR_OLD_PURCHASE_ORDERS (cPlannedDelDate in varchar2) IS
BEGIN
    FOR r1 IN (
        SELECT DISTINCT PO.ORDER_NO
        FROM PURCHASE_ORDER_LINE_NOPART POLN
        JOIN PURCHASE_ORDER PO
        ON PO.ORDER_NO = POLN.ORDER_NO 
        WHERE POLN.STATE = 'Released'
        AND POLN.PLANNED_DELIVERY_DATE < TO_DATE(cPlannedDelDate, 'DD/MM/YYYY')
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(r1.order_no);
    END LOOP;
END CLEAR_OLD_PURCHASE_ORDERS;
/

I'd also generally prefer to have the procedure argument declared as the data type you need, i.e. as a DATE, so you can use that in your query without converting it; and make it the caller's problem to pas the correct data type in.

Upvotes: 1

Related Questions