What is the replacement of cursor foreach in oracle pl/sql?

here lopping through each row and get non-null Comments,Assignee,Case_Info,Users values and return one row

Isn’t that simply finding NOT NULL values in plain SQL

SELECT comments, 
FROM   sometable 
WHERE  id = :id 
       AND comments IS NOT NULL 
       AND assignee IS NOT NULL 
       AND case_info IS NOT NULL 
       AND users IS NOT NULL

This whole code is in a funtion used in join with another table. Thie leads to performance issues. i would like to know alternate ways to do this

Why are you wrapping it in a function? Why using a LOOP? Row-by-row is slow-by-slow. No need of PL/SQL when you could do the same in SQL.

Use the above query to join with other tables. If you find performance bottlenecks, then generate the explain plan and do the needful.

Leave a Comment