Oracle PL/SQL for DBAs

PL/SQL Cursor Reformatting

As I mentioned, the PL/SQL compiler makes some extra effort to facilitate cursor reuse by checking for small differences like extra whitespace, uppercase versus lowercase, and line breaks. For example, only a single compiled cursor is created and reused in the shared pool for the following procedure:

    CREATE OR REPLACE PROCEDURE forgiveness IS
      -- define two poorly structured cursors
      CURSOR curs_x IS
      SELECT order_date FROM orders;
      CURSOR curs_y IS
      SELECT order_date
        FROM     orders;
    BEGIN
      -- let PL/SQL work its reformatting magic
      OPEN curs_x;
      CLOSE curs_x;
      OPEN curs_y;
      CLOSE curs_y;
    END;

This preparsing is done to all cursors in PL/SQL to help with matching among all stored code. For example, the following cursor would reuse the compiled cursor from the forgiveness procedure.

      CURSOR curs_x IS
      SELECT order_date FROM ORDers;