CODEWITHSUNDEEP
oracle-databaseplsql
Steven Li
Steven Li

Reputation: 9

- ORA-06550: line 9, column 12: PLS-00103: Encountered the symbol "FETCH" when expecting one of the following:

@C:\Users\4\Desktop\dbdrop;
@C:\Users\4\Desktop\dbcreate;
SET SERVEROUTPUT ON;
DECLARE
   ORDER_ID     ORDERS.ODID%TYPE;
   COMPANY_NAME ORDERS.CNAME%TYPE;
   ORDER_DATE   ORDERS.ODATE%TYPE;
   CURSOR ord_cursor IS 
        SELECT ODID, CNAME, ODATE
            FROM ORDERS
            WHERE ODER_DATE< TRUNC(SYSDATE);
           FETCH FIRST 5 ROWS ONLY;
BEGIN
   OPEN ord_cursor;
   LOOP
     FETCH ord_cursor into ORDER_ID, COMPANY_NAME, ORDER_DATE;
     DBMS_OUTPUT.PUT_LINE('           ');
     DBMS_OUTPUT.PUT_LINE('ODER ID: '|| TO_CHAR(Order_Id));
     DBMS_OUTPUT.PUT_LINE( 'ODER DATE: ' || ORDER_DATE );
     DBMS_OUTPUT.PUT_LINE('COMPANY NAME: '||  COMPANY_NAME );
     DBMS_OUTPUT.PUT_LINE( '------------');
     DBMS_OUTPUT.PUT_LINE( '------------');
     IF ord_cursor%NOTFOUND THEN 
        EXIT;
      END IF;
    END LOOP;
    CLOSE ord_cursor;
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Error report - ORA-06550: line 9, column 12: PLS-00103: Encountered the symbol "FETCH" when expecting one of the following:

begin function pragma procedure subtype type current cursor delete exists prior 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Upvotes: 0

Views: 2473

Answers (2)

Littlefoot
Littlefoot

Reputation: 143063

This:

       WHERE ODER_DATE< TRUNC(SYSDATE);
       FETCH FIRST 5 ROWS ONLY;

is wrong; either remove semi-colon in the first line (if your database supports FETCH clause), or entire second line.

Shorter version of your code is something like this (I don't have your tables so I fabricated one, based on Scott's EMP table):

SQL> create table orders as
  2  select empno ordid, ename cname, hiredate odate
  3  from emp
  4  where deptno = 10;

Table created.

Code itself:

SQL> set serveroutput on
SQL> begin
  2    for cur_r in
  3      (select ordid, cname, odate
  4       from orders
  5       where odate < trunc(sysdate)
  6         and rownum <= 5
  7      )
  8    loop
  9      dbms_output.put_line('------------');
 10      dbms_output.put_line('Order ID   = ' || cur_r.ordid);
 11      dbms_output.put_line('Order date = ' || to_char(cur_r.odate, 'dd.mm.yyyy'));
 12      dbms_output.put_line('Company    = ' || cur_r.cname);
 13    end loop;
 14  end;
 15  /
------------
Order ID   = 7782
Order date = 09.06.1981
Company    = CLARK
------------
Order ID   = 7839
Order date = 17.11.1981
Company    = KING
------------
Order ID   = 7934
Order date = 23.01.1982
Company    = MILLER

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Popeye
Popeye

Reputation: 35930

Your query ends before the FETCH clause and that is the issue.

You can use simple FOR loop and string concatenation as follows:

BEGIN
   FOR CUR IN (SELECT 'ODER ID: '|| TO_CHAR(ODID) || CHR(10)
                      || 'ODER DATE: ' || ODATE || CHR(10)
                      || 'COMPANY NAME: ' ||  CNAME || CHR(10)
                      || '------------' AS STR
                 FROM ORDERS
                WHERE ODER_DATE< TRUNC(SYSDATE)
                FETCH FIRST 5 ROWS ONLY)
    LOOP
     DBMS_OUTPUT.PUT_LINE(CUR.STR);
    END LOOP;
EXCEPTION 
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Upvotes: 0

Related Questions