Referencing Oracle Database PL/SQL Functions in a WHERE Clause

A user-defined PL/SQL function in the Oracle database can be invoked indirectly in a WHERE clause within a CREATE CACHE GROUP, LOAD CACHE GROUP, or REFRESH CACHE GROUP (for dynamic cache groups only) statement.

After creating the function, create a public synonym for the function. Then grant the EXECUTE privilege on the function to PUBLIC.

For example, in the Oracle database:

CREATE OR REPLACE FUNCTION get_customer_name
(c_num sales.customer.cust_num%TYPE) RETURN VARCHAR2 IS
c_name sales.customer.name%TYPE;
BEGIN
  SELECT name INTO c_name FROM sales.customer WHERE cust_num = c_num;
  RETURN c_name;
END get_customer_name;

CREATE PUBLIC SYNONYM retname FOR get_customer_name;
GRANT EXECUTE ON get_customer_name TO PUBLIC;

Then in the TimesTen database, for example, you can create a cache group with a WHERE clause that references the Oracle Database public synonym that was created for the function:

CREATE READONLY CACHE GROUP top_customer
FROM sales.customer
 (cust_num NUMBER(6) NOT NULL,
  region   VARCHAR2(10),
  name     VARCHAR2(50),
  address  VARCHAR2(100),
  PRIMARY KEY(cust_num))
WHERE sales.customer.name = retname(100);

For cache group types that allow a WHERE clause on a LOAD CACHE GROUP or REFRESH CACHE GROUP statement, you can invoke the function indirectly by referencing the public synonym that was created for the function. For example, you can use the following LOAD CACHE GROUP statement to load the AWT cache group new_customers:

LOAD CACHE GROUP new_customers WHERE name = retname(101) COMMIT EVERY 0 ROWS;