My Oracle Support Banner

[ADB] PROCEDURE CALL PREFIXED WITH SCHEMA NAME FAILS WITH PLS-00221 FROM INSIDE PL/SQL BLOCK FOR A SPECIFIC SCHEMA IN ADW DATABASE. (Doc ID 2653575.1)

Last updated on APRIL 01, 2020

Applies to:

Autonomous Database on Shared Infrastructure - Version N/A to N/A
Information in this document applies to any platform.

Symptoms

The procedure/function call prefixed with schema name does not work from inside pl/sql block.

Example:

set serveroutput on
DECLARE
xx number;
BEGIN
xx := <schema>.test_total(10);
END;

Here's the error:

Error report -
ORA-06550: line 0, column 0:
PLS-00221: '<schema>' is not a procedure or is undefined
ORA-06550: line 4, column 4:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Observations:

- The procedure/function exists in the schema.
- Same pl/sql block executes fine when the schema name is removed as prefix.
- The issue is with specific schema only. From other schemas the issue does not reproduce.
- We tested by creating new procedure/functions in this schema and its same behavior.
- If the same function is called from out side or used for compilation with schema name prefixed, it works fine like below:
SQL> select <schema>.test_total(30) from dual; ---> test_total being a function in that schema.
Or
SQL> alter procedure <schema>.TEST1 compile;
- So the issue is when it is called from inside pl/sql block.



Cause

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
Cause
Solution
References


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