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

by

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, 
       assignee, 
       case_info, 
       users 
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.

More Related Contents:

  1. Place to store PL/SQL block [closed]
  2. ANYONE CAN TELL ME THAT WHY THIS CODE IS GIVING ME AN ERROR OF TABLE OT CLUSTER KEY WORD IS MISSING? [closed]
  3. Get counts of all tables in a schema
  4. Can you SELECT everything, but 1 or 2 fields, without writer’s cramp?
  5. PL/SQL – Use “List” Variable in Where In Clause
  6. Oracle PL/SQL – Raise User-Defined Exception With Custom SQLERRM
  7. UTL_FILE.FOPEN() procedure not accepting path for directory?
  8. Get resultset from oracle stored procedure
  9. Oracle PL/SQL – How to create a simple array variable?
  10. ways to avoid global temp tables in oracle
  11. Number of rows affected by an UPDATE in PL/SQL
  12. PL/SQL print out ref cursor returned by a stored procedure
  13. Create Pivot view in SQL from a SQL table
  14. How to send email using Oracle 10 g Forms
  15. SQL Fiddle Output Error
  16. Why doesn’t PL/SQL respect privileges granted by Roles?
  17. oracle PL/SQL how to calculate range ip for IPv6 cidr
  18. Can We use threading in PL/SQL?
  19. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  20. Oracle: how to INSERT if a row doesn’t exist
  21. Functions vs procedures in Oracle
  22. What is the algorithm used by the ORA_HASH function?
  23. How to see refcursor result/output in Oracle SQL Developer? [duplicate]
  24. How to output result of SELECT statement which is executed using native dynamic SQL?
  25. passing in table name as plsql parameter
  26. Cursor For Loop with dynamic SQL-Statement
  27. How to use parameters in a ‘where value in…’ clause?
  28. BEGIN – END block atomic transactions in PL/SQL
  29. Why cannot I create triggers on objects owned by SYS?
  30. Escaping single quote in PLSQL

Leave a Comment