Document Type | Troubleshooting
Category | Patch/Upgrade
Applicable Product Version | 7FS02PS
Document Number | TPATS013
Issue
After patching from Tibero 7.2.1 to 7.2.2, when compiling objects using the RESULT CACHE syntax, the error TBR-15196: RESULT_CACHE OPTION is not allowed. occurs, causing the objects to change to INVALID status.
Compilation errors occur when using the result cache syntax under the following conditions.
- When the function has in or in/out parameters
- When the return type or parameter type includes BLOB, CLOB, NCLOB, REF CURSOR, COLLECTION, OBJECT, or RECORD
- When it is a PIPELINE function
Cause
In Tibero 7.2.2, patch 322411a was applied to improve the restrictions on result cache functions to align with Oracle specifications, which blocks compilation for such syntax and causes the error.
Solutions
1. Change to INVALID status
Attempt forced recompilation of PSMs using the result cache syntax, which will change the PSM objects to INVALID status due to compilation failure under the changed specifications.
SQL> set serveroutput on;
SQL> DECLARE
DDL VARCHAR2(2000);
begin
for rec in (select * from SYS._DD_PSMUNIT unit, SYS._DD_PSMMEMBER mem, ALL_OBJECTS obj where unit.obj_id = mem.OBJ_ID and unit.obj_id = obj.object_id and BITAND(mem.property , 67108864) = 67108864)
loop
begin
DDL := 'ALTER ' || rec.object_type || ' ' || rec.owner || '.' || rec.object_name || ' COMPILE FORCE';
DBMS_OUTPUT.PUT_LINE('recompile '||rec.object_type||' : '||rec.owner||'.'||rec.object_name);
EXECUTE IMMEDIATE DDL;
exception
when others then
DBMS_OUTPUT.PUT_LINE('ERROR recompiling'||rec.object_type||'.'||rec.object_name||'-'||SQLERRM);
end;
end loop;
end;
/2. Attempt Recompilation
For PSM objects changed to INVALID by step 1, remove the result cache option (modification of the original PSM syntax is required) and attempt recompilation.
The following is an example invalidated due to a PIPELINE function after patching.
Within the TIBERO.TEST PACKAGE, the syntax RETURN T_LIST RESULT_CACHE PIPELINED; exists.
2.1) After patching, execute the SQL from step 1 to force recompilation.
recompile PACKAGE : TIBERO.TEST ERROR recompilingPACKAGE.TEST-TBR-7283: Success with compilation errors.
2.2) TIBERO.TEST changes from VALID to INVALID status.
2.3) Remove the RESULT CACHE syntax and recompile.
- RETURN T_LIST RESULT_CACHE PIPELINED; -> RETURN T_LIST PIPELINED;
2.4) Confirm that TIBERO.TEST changes back to VALID status.