DROP INDEX COMMAND EXECUTED FROM WITHIN A FUNCTION RETURNS ORA-01418 - INDEX IS DROPPED BUT RETURNING THIS ERROR (Doc ID 2901072.1)

Last updated on JULY 20, 2024

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

Execution of DROP INDEX from within a function returns "ORA-01418: specified index does not exist".

Index is dropped but the issue is that the system returns an error nevertheless.

select <SCHEMA_NAME>.<PACKAGE_NAME>.<FUNCTION_NAME>('drop index <INDEX_NAME') from dual;

ERROR at line 1:

ORA-01418: specified index does not exist

ORA-06512: at "<SCHEMA_NAME>.<PACKAGE_NAME>", line 69

--------------

We have a package where the function is mentioned as:

function <FUNTION_NAME>(in_statement in varchar2) return integer;

Function is constructed following this model:
begin
execute immediate in_statement;
return 0;

Customer states that this is due to upgrading from 12c to 19c, but case is replicating in 12c as well.

Possibly that the RESULT_CACHE_MODE parameter has been changed from MANUAL (default value) to FORCE

	To view full details, sign in with your My Oracle Support account.
	Don't have a My Oracle Support account? Click to get started!

In this Document

Symptoms

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.