Forum Stats

  • 3,759,501 Users
  • 2,251,554 Discussions


Why "an INTO clause is expected in this SELECT statement"



  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,600 Silver Crown

    Every time you query data you have a cursor. A simple SELECT creates an implicit cursor.

    You can also define explicit cursor, either declaring them (and you need to open, loop, fetch, close - very similar to SQL Server) with the FOR xx IN (SELECT) syntax that automatically opens, loops, fetches and closes it.... (way more simple than SQL Server).

    Usually, performance problems exists when you process a cursor row by row, rather than doing things in a SET.

    For example, a procedure that declares an explicit cursor, opens it, loop through it and updates the column A setting the value to 123 will probably be slower than a simple UPDATE ... SET A=123 WHERE <<same conditions as explicit cursor>>. Both programs wil use a cursos, but one will process rows one at a time and the other all of them in a SET.

    Oracle also allows you to use a cursor using ROW by ROW logic but actually executing in batches (using BULK COLLECT, etc) that is a good way of doing things in a row by row way but optimizing resources, and not penalizing performance so much..... Thing about it as a intermediate solution, with the flexibility of row by row processing but with less performance impact.

    As a general rule of thumb, in Oracle is usually beter to do things in SQL, than in PL/SQL, but if you need to use PL/SQL is better to use bulk collect tthan process row by row. There are exceptions, obviously, but most of the times that won't fail.