Parsing - Troubleshooting Oracle Performance - page 433

Native Dynamic SQL: OPEN/FETCH/CLOSE
From a cursor management perspective, native dynamic SQL based on OPEN / FETCH / CLOSE is similar to static SQL with
explicit cursors. In other words, it's possible to control only the fetch phase. The following PL/SQL block shows an
example implementing test case 2:
DECLARE
TYPE t_cursor IS REF CURSOR;
l_cursor t_cursor;
l_pad VARCHAR2(4000);
BEGIN
FOR i IN 1..10000
LOOP
OPEN l_cursor FOR 'SELECT pad FROM t WHERE val = :1' USING i;
FETCH l_cursor INTO l_pad;
CLOSE l_cursor;
END LOOP;
END;
Without full control over the cursors, it's not possible to write code implementing test case 3. In addition, the
database engine isn't able to take advantage of client-side statement caching with native dynamic SQL based on
OPEN / FETCH / CLOSE . This means that the only way to solve a parsing problem caused by code using this method is to
rewrite it with EXECUTE IMMEDIATE or the dbms_sql package. As a workaround, you could also consider server-side
statement caching.
Dynamic SQL: dbms_sql Package
The dbms_sql package provides full control over the life cycle of cursors. In the following PL/SQL blocks
(implementing test case 2), notice how each step is explicitly coded:
DECLARE
l_cursor INTEGER;
l_pad VARCHAR2(4000);
l_retval INTEGER;
BEGIN
FOR i IN 1..10000
LOOP
l_cursor := dbms_sql. open_cursor ;
dbms_sql. parse (l_cursor, 'SELECT pad FROM t WHERE val = :1', 1);
dbms_sql. define_column (l_cursor, 1, l_pad, 10);
dbms_sql. bind_variable (l_cursor, ':1', i);
l_retval := dbms_sql. execute (l_cursor);
IF dbms_sql. fetch_rows (l_cursor) > 0
THEN
NULL;
END IF;
dbms_sql. close_cursor (l_cursor);
END LOOP;
END;