The result cache is enabled for the database instance.
Examine the following code for a PL/SQL function:
CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN VARCHAR
RESULT_CACHE RELIES_ON (HR.EMPLOYEES)
IS
date_hired DATE;
BEGIN
SELECT hire_date INTO date_hired
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = emp_id;
RETURN TO_CHAR(date_hired);
END;
Which statement is true in this scenario?
A.
If sessions have different NLS_DATE_FORMAT settings, cached results have different
formats.
B.
The function results are not cached because the query used in the function returns the DATE
data type.
C.
If sessions have different NLS_DATE_FORMAT settings, cached results have same formats
because the function’s return type is VARCHAR.
D.
If a function is executed with same argument value but different NLS_DATE_FORMAT for the
session, the cached result is overwritten with the new function result.
A
http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/subprograms.htm
The preceding function, get_hire_date, uses the TO_CHAR function to convert a DATE item to a VARCHAR item. The function get_hire_date does not specify a format mask, so the format mask defaults to the one that NLS_DATE_FORMAT specifies. If sessions that invoke get_hire_date have different NLS_DATE_FORMAT settings, cached results can have different formats. If a cached result computed by one session ages out, and another session recomputes it, the format might vary even for the same parameter value. If a session gets a cached result whose format differs from its own format, that result is probably incorrect.
Some possible solutions to this problem are:
Change the return type of get_hire_date to DATE and have each session invoke the TO_CHAR function.
If a common format is acceptable to all sessions, specify a format mask, removing the dependency on NLS_DATE_FORMAT. For example:
TO_CHAR(date_hired, ‘mm/dd/yy’);
Add a format mask parameter to get_hire_date. For example:
CREATE OR REPLACE FUNCTION get_hire_date
(emp_id NUMBER, fmt VARCHAR) RETURN VARCHAR
RESULT_CACHE
IS
date_hired DATE;
BEGIN
SELECT hire_date INTO date_hired
FROM HR.EMPLOYEES
WHERE EMPLOYEE_ID = emp_id;
RETURN TO_CHAR(date_hired, fmt);
END;
/
c
yes, to_char should neet fmt … ‘dd/mm/yyy for instance….
A