oracle-databaseplsqlstatic-methods

Oracle PLSQL: call static method from member one


I have two methods of my Oracle type with name myType:

  create or replace type myType as object (

    dummy varchar2(1),

    constructor function myType(something varchar2) return self as result,

    member procedure mp(self in myType,force boolean := false),

    static procedure sp

  );

  create or replace type body myType is

    constructor function myType(something varchar2) return self as result
      is
    begin
      dummy := something;

      return;
    end;

    member procedure mp(self in myType,force boolean := false) is
    begin
      if self.dummy is null and force then
        myType.sp();
      end if;
    end;

    static procedure sp is
    begin
      null;
    end;

  end;

This causes an Oracle error: myType is out of scope (PLS-00225). If I remove "myType." it thinks I am trying to call sm() as a member method and also gives an error (PLS-00587).

I've found out how to solve the issue:

create or replace synonym mySynonym for myType;

and then call instead of myType.sp(); -> mySynonym.sp(); and it works fine.

Still I would prefer to find some solution without a garbage synonym if one exists.

Oracle version: 11.2

Important: the constructor causes the fail and it can't be eliminated in my case


Solution

  • Sorry, can't reproduce this, on Oracle XE 11.2:

    SQL> create or replace type myType as object (
      2      z   char(1),
      3      member procedure mp,
      4      static procedure sp
      5  );
      6  /
    
    Type created.
    
    SQL> create or replace type body myType is
      2    member procedure mp is
      3    begin
      4      myType.sp();
      5    end;
      6
      7    static procedure sp is
      8    begin
      9      dbms_output.put_line('We''re here');
     10    end;
     11  end;
     12  /
    
    Type body created.
    
    SQL> set serveroutput on
    SQL> declare
      2    x     myType;
      3  begin
      4    x := myType(z => 'X');
      5    x.mp();
      6  end;
      7  /
    We're here
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    How are you defining your type header?

    EDIT: Now that you've added the constructor, I can reproduce the error.

    One way to fix the problem is to qualify myType with the schema owner when calling the static procedure:

        member procedure mp(self in myType,force boolean := false) is
        begin
          if self.dummy is null and force then
            luke.myType.sp();
          end if;
        end;
    

    This change allowed me to compile the type successfully.

    I created your procedure while connected to my 11g XE database as user luke. This username is very likely to be different for your system.

    Of course, your project may have different usernames for dev, test and production, so this might not be the ideal approach for you. If so, creating the synonym is probably the best thing to do.