Another useful ability is to be able to see what is actually stored in the cache, so we will create a log_cache procedure:
PROCEDURE log_cache AS l_cache_idx PLS_INTEGER; BEGIN l_cache_idx := g_dept_cache.FIRST; DBMS_OUTPUT.put_line ('Name Location'); DBMS_OUTPUT.put_line ('-------------- -------------'); <<all_records_in_cache>> LOOP EXIT all_records_in_cache WHEN NOT g_dept_cache.EXISTS (l_cache_idx); DBMS_OUTPUT.put_line ( LPAD (g_dept_cache (l_cache_idx).NAME, 14) || ' ' || LPAD (g_dept_cache (l_cache_idx).LOCATION, 13 ) ); l_cache_idx := g_dept_cache.NEXT (l_cache_idx); END LOOP all_records_in_cache; END log_cache;
I am sending the output of this procedure to the screen, but you might have another mechanism for this, e.g. maybe you log trace messages in a trace table. You can modify the procedure to use your own logging mechanism. It is always very handy to be able to peek into the cache, especially when you are debugging.