On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,597 Users
  • 2,269,761 Discussions
  • 7,916,789 Comments

Discussions

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

user4985500
user4985500 Member Posts: 11
edited Oct 12, 2016 10:12AM in Database Ideas - Ideas

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.

5 votes

Active · Last Updated

Comments

  • Vlad Visan-Oracle
    Vlad Visan-Oracle Member Posts: 1,249 Bronze Crown

    The procedure from that package is p2, not p1.

    So you need to execute :

    exec scott.p2();

  • Sven W.
    Sven W. GermanyMember Posts: 10,562 Gold Crown
    edited Oct 26, 2016 7:10AM

    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.
    Sven W. GermanyMember Posts: 10,562 Gold Crown

    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
    BPeaslandDBA Member Posts: 4,615 Blue Diamond

    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

    Sven W.