Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
| General PL/SQL Utilities Library |
There are times when, after bouncing a database, you want certain rows brought into memory with a
specific statement. This sample code demonstrates one way it can be done. If you were to want to automate this code ideally you
would run it by putting the contents of the anonymous block into an AFTER STARTUP DATABASE level trigger. That said the package
below demonstrates four separate ways to accomplish the goal: Only the first procedure being required for the doing so based on the
use of an AFTER STARTUP trigger.
Supporting Table |
CREATE TABLE cached_lookup_tab (
rid NUMBER(10) NOT NULL,
descr VARCHAR2(50) NOT NULL);
ALTER TABLE cached_lookup_tab
ADD CONSTRAINT pk_cached_lookup_tab
PRIMARY KEY (rid)
USING INDEX; |
| Insert table's demo data |
DECLARE
type t_tab IS TABLE OF NUMBER;
l_tab t_tab := t_tab();
BEGIN
FOR i IN 1 .. 1000 LOOP
l_tab.extend;
l_tab(l_tab.last) := i;
END LOOP;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO cached_lookup_tab
(rid, description)
VALUES(l_tab(i), 'Description for ' || l_tab(i));
COMMIT;
END;
/
exec dbms_stats.gather_table_stats(USER, 'CACHED_LOOKUP_TAB', CASCADE=>TRUE);
SELECT * FROM cached_lookup_tab; |
| Use Defined Package |
CREATE OR REPLACE PACKAGE cached_lookup_api AUTHID DEFINER IS
PROCEDURE populate_tab;
PROCEDURE get_cached_info(p_id cached_lookup_tab.id%TYPE, p_info OUT NOCOPY cached_lookup_tab%ROWTYPE);
PROCEDURE get_db_info(p_id cached_lookup_tab.id%TYPE, p_info OUT NOCOPY cached_lookup_tab%ROWTYPE);
END cached_lookup_api;
/
CREATE OR REPLACE PACKAGE BODY cached_lookup_api IS
TYPE t_tab IS TABLE OF cached_lookup_tab%ROWTYPE
INDEX BY BINARY_INTEGER;
g_tab t_tab;
PROCEDURE populate_tab IS
BEGIN
FOR i IN (SELECT * FROM cached_lookup_tab) LOOP
g_tab(i.id) := i;
END LOOP;
END populate_tab;
PROCEDURE get_cached_info(p_id cached_lookup_tab.id%TYPE, p_info OUT NOCOPY cached_lookup_tab%ROWTYPE) IS
BEGIN
p_info := g_tab(p_id);
END get_cached_info;
PROCEDURE get_db_info(p_id cached_lookup_tab.id%TYPE, p_info OUT NOCOPY cached_lookup_tab%ROWTYPE) IS
BEGIN
SELECT *
INTO p_info
FROM cached_lookup_tab
WHERE id = p_id;
END get_db_info;
BEGIN
populate_tab;
END cached_lookup_api;
/ |
| Anonymous block for demo purposes |
DECLARE
l_loops CONSTANT NUMBER := 10000;
l_seed BINARY_INTEGER;
l_start NUMBER;
l_id cached_lookup_tab.id%TYPE;
l_row cached_lookup_tab%ROWTYPE;
BEGIN
l_seed := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYDDMMSS'));
dbms_random.initialize(val => l_seed);
l_start := dbms_utility.get_time;
FOR i IN 1 .. l_loops LOOP
l_id := TRUNC(dbms_random.value(low => 1, high => 1000));
cached_lookup_api.get_cached_info(p_id=>l_id, p_info=>l_row);
END LOOP;
dbms_output.put_line('Cached Lookup(' || l_loops || ' rows): ' || (dbms_utility.get_time - l_start));
l_start := dbms_utility.get_time;
FOR i IN 1 .. l_loops LOOP
l_id := TRUNC(dbms_random.value(low=>1, high=>1000));
cached_lookup_api.get_db_info(p_id=>l_id, p_info=>l_row);
END LOOP;
dbms_output.put_line('DB Lookup(' || l_loops || ' rows) : ' || (dbms_utility.get_time -l_start));
dbms_random.terminate;
END;
/ |
| ... |
|
