Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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

user4985500Oct 12 2016 — edited Oct 12 2016

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.

Comments
Post Details
Added on Oct 12 2016
4 comments
89 views