Oracle should warn or block creating DB objects identical to schema name.

user4985500

Hi All,

Following example demonstrates the issue in oracle 11G ( may find same issue in other versions too ).

SQL> show user

USER is "SCOTT"

SQL> create or replace

  2    procedure p1

  3      is

  4      begin

  5          null;

  6  end;

  7  /

Procedure created.

SQL> create or replace

  2    package scott

  3      is

  4        procedure p2;

  5  end;

  6  /

Package created.

SQL> create or replace

  2    package body scott

  3      is

  4        procedure p2

  5          is

  6          begin

  7              null;

  8        end;

  9  end;

10  /

Package body created.

SQL> exec p1();

PL/SQL procedure successfully completed.

SQL> exec scott.p1();

BEGIN scott.p1(); END;

            *

ERROR at line 1:

ORA-06550: line 1, column 13:

PLS-00302: component 'P1' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Similar incident was reported by one of our ERP customer and part of ERP system had been failed for few days due to this.  

I don't understand why this was allowed by Oracle. Please review.

Ref :Schema Owner couldn't access own objects with schema prefix but works fine without schema.

Thank you,

Rohana.

Vlad Visan-Oracle

The procedure from that package is p2, not p1.

So you need to execute :

exec scott.p2();

Sven W.

The procedure from that package is p2, not p1.

So you need to execute :

exec scott.p2();

Vlad Visan-Oracle wrote:The procedure from that package is p2, not p1.So you need to execute :exec scott.p2();

You misunderstood. She wants to call procedure p1 but prefixed by the SCHEMA name.

The error happens because object name resolution first finds the package SCOTT inside the current schema SCOTT.

But package scott has no submodule p1.

The "idea" here is to prevent package scott to be created inside schema scott, because that will potentially lead to such a nameing conflict.

Sven W.

I'm slightly against this idea. Although a compiler warning might be helpful.

For several reasons.

1) One should avoid hardcoding the schema name when calling a procedure. I rarely found the need to do so, it is almost always a bug and not needed.

2) Responsibilities: Imagine what could happen if name resolution did work the other way aroud, or if it would not be possible to create a object with the same name as an existing schema.

If you have a package scott.ABC in your DEV environment and you wanted to install that package to PROD. Now in prod there exists another schema ABC already. Then this other schema would prevent you from creating the package ABC in scott. That means you suddenly become dependend from forces outside of your control.

BPeaslandDBA

If properly formed names are used, this should not be much of an issue. The schema name is normally related to the application or the functionality of that application. The object names in the schema are normally named in relation to their aspect within that application. Tables are typically named after nouns because they model entities, i.e. objects, i.e. people, places or things. Stored procedures typically model actions of those objects, i.e. the verbs.

Just my 2 cents,

Brian