Home

Description

This rule detects any call to the legacy DBMS_RESULT_CACHE routines whose names contain BLACK_LIST or OBJECT_BLACK_LIST. These identifiers are deprecated starting with Oracle Database 23ai and have been replaced by the new BLOCK_LIST / OBJECT_BLOCK_LIST family of functions and procedures.

Deprecated names:

• BLACK_LIST → BLOCK_LIST
• BLACK_LIST_ADD → BLOCK_LIST_ADD
• BLACK_LIST_REMOVE → BLOCK_LIST_REMOVE
• BLACK_LIST_CLEAR → BLOCK_LIST_CLEAR
• OBJECT_BLACK_LIST → OBJECT_BLOCK_LIST
• OBJECT_BLACK_LIST_ADD → OBJECT_BLOCK_LIST_ADD
• OBJECT_BLACK_LIST_REMOVE → OBJECT_BLOCK_LIST_REMOVE
• OBJECT_BLACK_LIST_CLEAR → OBJECT_BLOCK_LIST_CLEAR

Key Benefits

• Upgrade Safety: Code continues to compile and run after upgrading to Oracle 23ai and future releases.
• Consistency: Aligns naming with modern inclusive terminology adopted by Oracle.
• Maintainability: Eliminates mixed use of old and new identifiers inside the codebase.

Non-compliant Code Example

DECLARE
  ok BOOLEAN;
BEGIN
  -- object-level
  DBMS_RESULT_CACHE.OBJECT_BLACK_LIST_ADD('HR', 'EMP'); --Non compliant code (OBJECT_BLACK_LIST_ADD is deprecated)
  ok := DBMS_RESULT_CACHE.OBJECT_BLACK_LIST('HR', 'EMP');   --Non compliant code (OBJECT_BLACK_LIST is deprecated)
  DBMS_OUTPUT.put_line('Black-listed? '||CASE WHEN ok THEN 'YES' ELSE 'NO' END);
  DBMS_RESULT_CACHE.OBJECT_BLACK_LIST_REMOVE('HR', 'EMP');  --Non compliant code (OBJECT_BLACK_LIST_REMOVE is deprecated)
  DBMS_RESULT_CACHE.OBJECT_BLACK_LIST_CLEAR;    --Non compliant code (OBJECT_BLACK_LIST_CLEAR is deprecated)

  -- session-level
  DBMS_RESULT_CACHE.BLACK_LIST_ADD('HR.EMP');   --Non compliant code (BLACK_LIST_ADD is deprecated)
  ok := DBMS_RESULT_CACHE.BLACK_LIST('HR.EMP'); --Non compliant code (BLACK_LIST is deprecated)
  DBMS_RESULT_CACHE.BLACK_LIST_REMOVE('HR.EMP');    --Non compliant code (BLACK_LIST_REMOVE is deprecated)
  DBMS_RESULT_CACHE.BLACK_LIST_CLEAR;   --Non compliant code (BLACK_LIST_CLEAR is deprecated)
END;
/

Compliant Code Example

DECLARE
  ok BOOLEAN;
BEGIN
  -- object-level
  DBMS_RESULT_CACHE.OBJECT_BLOCK_LIST_ADD('HR', 'EMP'); --Compliant code
  ok := DBMS_RESULT_CACHE.OBJECT_BLOCK_LIST('HR', 'EMP');   --Compliant code
  DBMS_OUTPUT.put_line('Block-listed? '||CASE WHEN ok THEN 'YES' ELSE 'NO' END);
  DBMS_RESULT_CACHE.OBJECT_BLOCK_LIST_REMOVE('HR', 'EMP');  --Compliant code
  DBMS_RESULT_CACHE.OBJECT_BLOCK_LIST_CLEAR;    --Compliant code

  -- session-level
  DBMS_RESULT_CACHE.BLOCK_LIST_ADD('HR.EMP');   --Compliant code
  ok := DBMS_RESULT_CACHE.BLOCK_LIST('HR.EMP'); --Compliant code
  DBMS_RESULT_CACHE.BLOCK_LIST_REMOVE('HR.EMP');    --Compliant code
  DBMS_RESULT_CACHE.BLOCK_LIST_CLEAR;   --Compliant code
END;
/