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;

score 4 · Accepted Answer · answered Apr 07 '18 at 19:41

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.

Thank you. Your second example worked for me. Actually like condition is not wrong, the reason why it looks complicated is that I want it to be dynamic value as there are lots of data in my table. Thanks again! — asdfg, Apr 07 '18 at 20:08

score 1 · Answer 2 · answered Apr 07 '18 at 20:12

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.

score 0 · Answer 3 · answered Nov 30 '21 at 05:19

0

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);

answered Nov 30 '21 at 05:19

user17548829

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 30 '21 at 07:44
This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/30466358) – Ana GH Nov 30 '21 at 11:09

ORA-00928: missing SELECT keyword

3 Answers3