PL/SQL: ORA-00904: invalid identifier &PL/SQL: Statement ignored&PLS-00364: loop index variable use is invalid

Question

@C:\Users\4\Desktop\dbdrop;
@C:\Users\4\Desktop\dbcreate;
SET SERVEROUTPUT ON;
 begin
     for cur_r in
      (select order_id, company_name, order_date
        from orders
        where order_date < trunc(sysdate)
          and rownum <= 5
        )
     loop
        dbms_output.put_line('------------');
       dbms_output.put_line('Order ID   = ' || cur_r.order_id);
       dbms_output.put_line('Order date = ' || to_char(cur_r.order_date, 'dd.mm.yyyy'));
       dbms_output.put_line('Company    = ' || cur_r.company_name);
     end loop;
   end;
  /

Error report -

ORA-06550: line 3, column 27: PL/SQL: ORA-00904: "COMPANY_NAME": invalid identifier
ORA-06550: line 3, column 10: PL/SQL: SQL Statement ignored
ORA-06550: line 10, column 49: PLS-00364: loop index variable 'CUR_R' use is invalid
ORA-06550: line 10, column 7: PL/SQL: Statement ignored
ORA-06550: line 11, column 58: PLS-00364: loop index variable 'CUR_R' use is invalid
ORA-06550: line 11, column 8: PL/SQL: Statement ignored
ORA-06550: line 12, column 49: PLS-00364: loop index variable 'CUR_R' use is invalid
ORA-06550: line 12, column 7: PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Here is the ORDERS table

CREATE TABLE ORDERS
(
    ORDER_ID        NUMBER(9)   NOT NULL,
    CUSTOMER_CODE   VARCHAR(5)  NOT NULL,
    EMPLOYEE_ID     NUMBER(9)   NOT NULL,
    ORDER_DATE      DATE        NOT NULL,
    REQUIRED_DATE   DATE,
    SHIPPED_DATE    DATE,
    SHIP_VIA        VARCHAR(40),
    FREIGHT         NUMBER(10,2)    DEFAULT 0,
    SHIP_NAME       VARCHAR(40),
    SHIP_ADDRESS    VARCHAR(60),
    SHIP_CITY       VARCHAR(15),
    SHIP_REGION     VARCHAR(15),
    SHIP_POSTAL_CODE    VARCHAR(10),
    SHIP_COUNTRY    VARCHAR(15),
    CONSTRAINT PK_ORDERS PRIMARY KEY (ORDER_ID),
    CONSTRAINT FK_CUSTOMER_CODE FOREIGN KEY (CUSTOMER_CODE) REFERENCES CUSTOMER(CUSTOMER_CODE),  
    CONSTRAINT FK_EMPLOYEE_ID FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID),  
    CONSTRAINT FK_SHIP_VIA FOREIGN KEY (SHIP_VIA) REFERENCES SHIPPER(COMPANY_NAME)  
);

Answer 1

In SQL Developer you can't copy/paste what I wrote (previously) in SQL*Plus; you'd omit line numbers (and SQL> prompts) and use this only:

set serveroutput on
begin
  for cur_r in
    (select order_id, ship_via as company_name, order_date
     from orders
     where order_date < trunc(sysdate)
       and rownum <= 5
    )
  loop
    dbms_output.put_line('------------');
    dbms_output.put_line('Order ID   = ' || cur_r.order_id);
    dbms_output.put_line('Order date = ' || to_char(cur_r.order_date, 'dd.mm.yyyy'));
    dbms_output.put_line('Company    = ' || cur_r.company_name);
  end loop;
end;
/

Answer 2

ORA-06550: line 3, column 27: PL/SQL: ORA-00904: "COMPANY_NAME": invalid identifier

This indicates that your query is referencing a column which doesn't exist in the table. Now that you have posted the description of ORDERS we can see that indeed it doesn't have a column called COMPANY_NAME. Because the query is invalid the cursor is invalid and so references to the cursor are also invalid. That's why you have so many compilation errors. It is common in programming for one syntax error to trigger a whole bunch more.

However, ORDERS does have a foreign key to CUSTOMERS. So probably what you need to do is join to that table in your cursor query:

for cur_r in
      (select o.order_id, c.company_name, o.order_date
        from orders o
             join customers c on c.customer_code = o.customer_code
        where o.order_date < trunc(sysdate)
          and rownum <= 5
        )
     loop