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:
Remember, we are working in schema ABC with logon-user ABC !!!
create or replace procedure a as
create table abc (a number);
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.
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:
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.