Forward and Backward Navigation in Oracle PL/SQL- Expert Examples

Navigating through rows of data in Oracle PL/SQL is a common requirement when working with large datasets or developing applications that involve data traversal. Oracle provides multiple ways to handle result sets, with the ability to move forward through rows being straightforward. However, backward navigation (moving to the previous rows) requires a bit more effort. In this blog, we’ll explore various methods for Forward and Backward Navigation in Oracle, with practical examples, tips, and best practices to ensure you can implement these techniques effectively in your PL/SQL programs.

By the end of this post, you’ll have a clear understanding of:

• How to perform forward navigation with explicit cursors.
• How to implement backward navigation using PL/SQL collections.

Why is Forward and Backward Navigation in Oracle PL/SQL important?

When developing applications that require data presentation, it’s common to fetch records from the database in batches. For example, a user might want to see the next set of records or go back to the previous set. Efficient data traversal is crucial in such scenarios, and while forward navigation is commonly used, there are instances where users might need to revisit past records. Having backward navigation allows developers to create a seamless and flexible user experience.

---

Forward Navigation in Oracle PL/SQL

Forward navigation in PL/SQL is straightforward. When using explicit cursors, you can fetch rows in a forward-only manner, meaning you retrieve rows one after the other. Once a row is fetched, you cannot go back to a previous one unless you use special techniques like collections or scrollable cursors (which we’ll cover later).

Example: Forward Navigation with an Explicit Cursor

In this example:

• We define a cursor emp_cursor to select employee numbers (empno) and employee names (ename) from the emp table.
• The cursor is opened, and rows are fetched one by one using the FETCH statement.
• The loop continues until no more rows are left (%NOTFOUND), and each employee’s details are printed.

This is an example of forward-only navigation—we can only move forward through the result set, and there is no way to revisit previous rows using this approach alone.

---

Backward Navigation in Oracle PL/SQL

Oracle PL/SQL doesn’t provide backward navigation in standard cursors out of the box. However, you can achieve this functionality using PL/SQL collections, such as index-by tables or nested tables. Collections allow you to store multiple rows in memory and access them in any order, enabling both forward and backward navigation.

Example: Using PL/SQL Collections Backward Navigation

In this example:

• We first declare a PL/SQL table type to hold the rows from the emp table.
• A FOR loop loads the query result into the PL/SQL collection, allowing us to store and retrieve rows later.
• For forward navigation, we start from the first element and loop through to the last.
• For backward navigation, we reset the index to the last row and loop back to the first.

This method gives you complete control over the order in which rows are accessed.

---

Key Points to Remember

• Explicit Cursors: Best for simple forward-only navigation.
• PL/SQL Collections: Ideal for storing data in memory and enabling flexible forward and backward navigation.

---

Conclusion

In this blog, we explored different ways to implement forward and backward navigation in Oracle PL/SQL, ranging from explicit cursors for forward-only navigation to more advanced techniques like PL/SQL collections and scrollable cursors for backward and random row access. Each method has its own use case, and your choice should depend on the complexity of the data traversal required in your applications.

Mastering these navigation techniques will make your PL/SQL programs more dynamic and flexible, enabling you to handle complex data retrieval and user interactions more efficiently.