October 2005
Intermediate to advanced
454 pages
14h 44m
English
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;