Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I am trying to use 'WITH' clause inside PL-SQL block :

Cursor using WITH clause as follows :

  CURSOR c_API_MSG
      WITH SAMI AS (SELECT * FROM NAGENDRA WHERE STATUS = 'NEW')
      SELECT * FROM SAMI WHERE ROWNUM <= TO_NUMBER (10);
Execution :
FOR v_Rec IN c_API_MSG
     BEGIN
        -- My LOGIC
END LOOP;
It is not executing properly. It is not going inside loop. seems like cursor not able to fetch rows & that's why exiting.
Normal sub-query without with clause working fine. With clause working fine on editor.
Is there any limitation of using 'WITH' clause or am I missing something here ?
Observations :
Inside toad editor with below query :
If I Use f9 (normal execute) : 5 rows found (Correct result)
If I use f5 : No rows found (This is I am worried about)
      WITH SAMI AS (SELECT * FROM NAGENDRA WHERE STATUS = 'NEW')
      SELECT * FROM SAMI WHERE ROWNUM <= TO_NUMBER (10);
                as an aside: TO_NUMBER (10) ... you're converting a number into a number? Why bother? just use 10 instead.
– Boneist
                Jun 22, 2016 at 11:44
                Please show the complete and real code you're running. With > 99% probability, you have a flaw in your logic. Strip it down to a minimal reproducible example, and provide the table definition and content (possibly with dummy data if you can't post the real data).
– Frank Schmitt
                Jun 22, 2016 at 12:03
                Also, according to the TOAD documentation, F9 runs a single statement, whereas F5 runs all statements. My guess would be that you execute multiple statements and the last one returns no data. But again: it's impossible to help you unless you provide more information.
– Frank Schmitt
                Jun 22, 2016 at 12:10
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.