Problems caused by simultaneous Oracle schema names and object names

The table name and schema name are the same:

The schema and table names are both Arwen.
Schema Arwen contains a PKG package with the plus function. The result of adding two numbers is returned.
Select Arwen. PKG. Plus (1, 2) from dual; -- no error occurred while executing this SQL statement. Result 3 is returned.
However, an error occurs in a PL/SQL block.
Declare
Result int;
Begin
Select Arwen. PKG. Plus (1, 2) into result from dual;
Dbms_output.put_line (result );
End;

If you drop the table Arwen, the PL/SQL block is correctly executed. (If the names of other objects such as functions or views are the same as the schema, an error occurs)

 

 

Explanations

 

I thought it was a bit strange. I searched the internet and saw an introduction to the PL/SQL name resolution method on the oralce official website. Here is a passage:
PL/SQL and SQL resolve qualified names differently.
For example, when resolving the table name HR. Jobs:
• PL/SQL searches first for packages, types, tables, and views named HR in the current schema, and then for objects named jobs in the HR schema.
• SQL searches first for objects named jobs in the HR schema, and then for packages, types, tables, and views named HR in the current schema.

It seems that you can understand the problem above. in SQL name parsing, when such a prefix with a dot number first resolves the schema before the DoT number, then Arwen. PKG. plus (1, 2) is the first statement to set Arwen
Schema, and then PKG is parsed into the objects in the schema. Here is the package. however, in PL/SQL, the preceding vertex is regarded as the object in the current schema, so that Arwen. PKG. in Plus (1, 2), Arwen is first parsed into the Arwen table, and then PKG is parsed into fields in the Table. An error occurs because no such field exists.

It seems that the Oracle document is correct. However, if you try another example, you still have a problem. The document says HR. jobs is a table, which is not a good example. I will change jobs to a function. example:
With schema HR, in HR
There is a function job, (No parameter, the return string 'jobs of scheme ').
There is a package HR, and there is a function jobs in the package (No parameter, the return string 'jobs of PKG ').
If, according to the document, select HR. jobs from dual; -- the returned value is jobs of schema.
In PL/SQL blocks
Declare
V_txt varchar2 (100 );
Begin
Select HR. Jobs into v_txt from dual;
Dbms_output.put_line (v_txt); -- print jobs of PKG.
End;
However, the actual results are jobs of PKG in SQL or PL/SQL.

 

Is it said in the official Oracle document that it is a pitfall?

No matter whether it is difficult or not, try not to make the object name and schema name the same, otherwise it is not easy to know what went wrong when there is a problem,
In fact, I didn't really like the syntax to study this problem, but I did encounter this problem at work. After a long time, I found the cause of this pain.
I encountered this problem in such a scenario. I used the FGA function and created some audit policies to record all schema DML operations. the Audit Policy calls a function in a package. The schema prefix is added before the package name. this prefix must be added. because DML operations in other schemas trigger Audit Policy calls, the function cannot be found without the schema prefix. As a result, the table name and schema name in the schema where the audit policy is located are the same, the result is a tragedy. all schemas trigger the audit policy when performing DML operations, and then call the function, but an error occurs during the call. this means that all schema DML operations cannot be completed, and it means that almost the whole database is useless. Think about the serious impact and then sweat, if the whole production environment is like this, I would probably be scolded ........