0

We had a small glitch which we tracked down easily. But we try to find the documentation for this behaviour and are not able to get this neither do we understand why Oracle behaves like this.

What we have:

  1. Oracle 19c
  2. A schema named ABC
  3. A procedure in that schema named a
  4. We call the procedure with the schema prefix
  5. All works great
  6. As soon as we add a table with the same name as the schema, the procedure call no longer works and we get the error PLS-00221

Remember, we are working in schema ABC with logon-user ABC !!!

create or replace procedure a as
begin
    null;
end a;
/

begin
    abc.a();
end;
/

create table abc (a number);

begin
    abc.a();
end;
/

PLS-00221: 'A' is not a procedure or not defined

It is obvious that the newly created table produces the issues but what is the reason for this? Calling a procedure with a schema prefix should not be harmed by a table with the same name. The Oracle documentation states, that schema.objects is a proper way of referencing objects, we couldn't find any disclaimer why this should not work.

Jörg
  • 69
  • 8
  • Hi run this command desc a; and see if you are able to see the table or procedure , as when i tried to create a table with the same name as a procedure I got the message ORA-00955: name is already used by an existing object – Himanshu Kandpal Aug 16 '23 at 13:49

1 Answers1

3

I'm guessing create table abs should actually read create table abc.

Oracle's name resolution will find an object matching the name before it looks for an owner by the name. It sees an object with the name abc and interprets the .a as a procedure within that object (as if it were a package, for example), but tables don't have procedures, hence the error.

For docs, see:

Managing Object Name Resolution

Excerpt:

a. In the current schema, the database searches for an object whose name matches the first piece of the object name. If it does not find such an object, it continues with step b.

b. The database searches for a public synonym that matches the first piece of the name. If it does not find one, it continues with step c.

c. The database searches for a schema whose name matches the first piece of the object name. If it finds one, it returns to step b, now using the second piece of the name as the object to find in the qualified schema. If the second piece does not correspond to an object in the previously qualified schema or there is not a second piece, the database returns an error.

Paul W
  • 5,507
  • 2
  • 2
  • 13