CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlcursor
ionutab
ionutab

Reputation: 444

How many times does a query defined in a cursor execute?

I have a stored procedure in a Oracle database.
In this stored procedure I have defined a cursor.

I iterate through the result set using:

FOR item IN cursor_name LOOP
END LOOP;

How many times does the query execute ? Is there a way for me to know ? and also is this the best approach, or should I iterate in a different way ?

Thanks.

Upvotes: 2

Views: 2029

Answers (1)

Rob van Wijk
Rob van Wijk

Reputation: 17705

The query in the cursor cursor_name is executed only once. How many times you fetch from that cursor depends. Each fetch means a context switch. From Oracle version 10 onwards, if you set parameter PLSQL_OPTIMIZE_LEVEL to its default of 2 or higher, an optimization kicks in and you will fetch 100 rows at a time. Without this, you would fetch each row separately. Which would harm performance considerably when fetching a lot of rows.

Also beware that you don't put SQL statements inside the loop. When you do that, you will obviously execute those statements as many times as there are rows fetched from your cursor.

Regards,
Rob.

Upvotes: 5

Related Questions