CODEWITHSUNDEEP
sqloracle-databasecachingplsqloracle10g
janasainik
janasainik

Reputation: 831

How to cache the query result of a function in oracle 10g?

I am using oracle 10g. I have following function in a package and it will be invoked within the package for many times. Instead, i want to maintain "function result cache"(available with 11g) and i will look up in cache first before executing the query. Is this possible with 10g or i have to choose some other option to achieve it?

function f_get_col_data_type(tab_name varchar,
                              col_name varchar
                              ) return varchar is
    v_col_data_type user_tab_columns.data_type%type;
  begin
    select data_type
      into v_col_data_type
      from user_tab_columns
     where upper(table_name) = upper(tab_name)
       and upper(column_name) = upper(col_name);

    return v_col_data_type;
end f_get_col_data_type;

Upvotes: 3

Views: 1935

Answers (2)

TenG
TenG

Reputation: 4004

Put the function in a package, and also add a PLSQL table in the package, index by VARCHAR2.

At first execution, check the table using "tab_name || '.' || col_name" combined as the index (hash).

If empty, run the query and write the dat to the PLSQL table, using the tab_name || '.' || col_name as the index.

When the query runs which calls this fucntion within each session, the first call will be longer as it builds the table, subsequent ones will be much quicker.

Things to watch out for with this approach is memory management (large data, number of sessions), stale data - not suitable for volatile data tables. You should consider a "free" fucntion in the package too to clear the cache.

Upvotes: 1

user2759222
user2759222

Reputation: 42

You can use "DETERMINISTIC" clause to store the values in cache. Please see example below 

    CREATE OR REPLACE FUNCTION text_length(a CLOB) 
   RETURN NUMBER DETERMINISTIC IS
BEGIN 
  RETURN DBMS_LOB.GETLENGTH(a);
END;

Upvotes: 0

Related Questions