ORA-00928: missing SELECT keyword

Question

In MYTABLE there are courses and their predecessor courses. What I am trying to is to find the courses to be taken after the specified course. I am getting missing SELECT keyword error. Why I am getting this error although I have SELECT statement in FOR statement ? Where am I doing wrong ?

DECLARE
  coursename varchar2(200) := 'COURSE_101';
  str        varchar2(200);
BEGIN
  WITH DATA AS
    (select (select course_name 
             from MYTABLE 
             WHERE predecessors like ('''%' || coursename||'%''') 
            ) str
     from dual
    )
    FOR cursor1 IN (SELECT str FROM DATA) 
    LOOP
      DBMS_OUTPUT.PUT_LINE(cursor1);
    END LOOP;
end;

Answer 1

Unless I'm wrong, WITH factoring clause can't be used that way; you'll have to use it as an inline view, such as this:

declare
  coursename varchar2(200) := 'COURSE_101';
  str        varchar2(200);
begin
  for cursor1 in (select str 
                  from (select (select course_name 
                                from mytable 
                                where predecessors like '''%' || coursename||'%'''
                               )  str
                        from dual
                       )
                 ) 
  loop
    dbms_output.put_line(cursor1.str);
  end loop;
end;
/

Apart from the fact that it doesn't work (wrong LIKE condition), you OVERcomplicated it. This is how it, actually, does something:

SQL> create table mytable(course_name  varchar2(20),
  2                       predecessors varchar2(20));

Table created.

SQL> insert into mytable values ('COURSE_101', 'COURSE_101');

1 row created.

SQL>
SQL> declare
  2    coursename varchar2(20) := 'COURSE_101';
  3  begin
  4    for cursor1 in (select course_name str
  5                    from mytable
  6                    where predecessors like '%' || coursename || '%'
  7                   )
  8    loop
  9      dbms_output.put_line(cursor1.str);
 10    end loop;
 11  end;
 12  /
COURSE_101

PL/SQL procedure successfully completed.

SQL>

Also, is that WHERE clause correct? PREDECESSORS LIKE COURSENAME? I'm not saying that it is wrong, just looks somewhat strange.

Answer 2

CREATE TABLE product ( PRODUCT_ID int Primary key, NAME VARCHAR (20) not null, Batchno int not null, Rate int not null, Tax int not null, Expiredate date not null );

INSERT INTO PRODUCT VALUSES(1 , 'vasocare', 32 , 15 , 2 , 01-JAN-2021);

Answer 3

To extend @Littlefoot's answer a bit: you can use a common table expression (WITH clause) in your cursor, but the WITH must be part of the cursor SELECT statement, not separate from it:

DECLARE
  coursename varchar2(200) := 'COURSE_101';
BEGIN
  FOR aRow IN (WITH DATA AS (select course_name AS str
                               from MYTABLE 
                               WHERE predecessors like '''%' || coursename||'%''')
               SELECT str FROM DATA)
  LOOP
    DBMS_OUTPUT.PUT_LINE(aRow.str);
  END LOOP;
END;

Also note that the iteration variable in a cursor FOR-loop represents a row returned by the cursor's SELECT statement, so if you want to display whatever was returned by the cursor you must use dotted-variable notation (e.g. aRow.str) to extract fields from the row.

Best of luck.