Clever Geek Handbook

PL / SQL Private Object Method

I'm a bit new to Oracle PL / SQL (using 10g), I was wondering if there is a way to make a private method on an object type, as is often done for private helper methods in other languages ​​(Java, C ++, C #, etc.) etc.). I know it is possible to create private methods in packages, but I cannot find a way to do this for object types. I keep getting compiler errors:

Error: PLS-00539: subprogram 'FOO' is declared in an object type body 
and must be defined in the object type specification.
+2


source to share


4 answers


Ok, here is a potential solution that I tested very briefly and seems to work so far:

Create a parent object type to be marked NOT FINAL and NOT INSTANTIABLE, and then put all the closed code there. Private methods won't be truly private, but including them in a type that's not final and not instantaneous prevents them from being called. In the current subtype, reference the "private" methods in the supertype via SELF. Example:


create or replace type PrivateFoo under SuperFoo
(

  member procedure setUpCommonFoo
) NOT INSTANTIABLE NOT FINAL;

create or replace type body PrivateFoo is
  -- Member procedures and functions
  member procedure setUpCommonFoo is
        begin
            SELF.someAttrib:='Some Common Default Value';
        end;
end;

create or replace type Foo under PrivateFoo
(
    CONSTRUCTOR FUNCTION Foo RETURN SELF AS RESULT,
    CONSTRUCTOR FUNCTION Foo(fkey FooKey) RETURN SELF AS RESULT -- assume fkey is defined in SuperFoo, and FooKey type is defined somewhere else ;)
)

create or replace type body Foo is
    --no-arg Constructor For basic Foo set up.
    CONSTRUCTOR FUNCTION PartyConvertor RETURN SELF AS RESULT AS
    BEGIN
        self.setUpCommonFoo;
        RETURN;
    END;
        --alt constructor for other situations...
    CONSTRUCTOR FUNCTION PartyConvertor(fkey FooKey) RETURN SELF AS RESULT AS
    BEGIN
        self.setUpCommonFoo;
                SELF.rarelyUsedAttrib:='Special Value!'; --just assume that someAttrib and rarelyUsedAttrib actually exist ;)
        self.fkey := fkey;
        RETURN;
    END;
        --Other Members go here...
end;


Now I have to admit that I don't like this pattern. It seems awkward and awkward. I'm probably going to just avoid object types as much as I can and stick with packages (or very simplex object types). The ratifier package helps me solve a particular problem with common code for constructors and not for other types of code refactorings.

... if there is no better way to work with objects ... anyone? anyone?

+2


source


You cannot have private methods on pl / sql objects, you can have polymorphism and inheritance but not encapsulation.

If you want encapsulation (private methods) you can use pl / sql packages.



All three are impossible at once.

+1


source


The answer to this question is somewhat tricky, but I will explain it as best I can. First, this is not entirely Oracle's fault; ANSI SQL defines a thing called abstract data types (ADTs) that can be used to extend SQL. I think Oracle follows its specifications. Part of the disadvantage of encapsulation stems from the difficulty of binding and storing objects in SQL. However, I will not go into details as I myself do not understand.

ADTs are useful for giving structure to your data, both in code and in tables, but they can't be very complex. For example, you cannot have object A, which has object B, which again has object A. It cannot be stored in a SQL table. You can work around this using REF in Oracle (not sure how other vendors say it), but it becomes another problem to solve in code.

I've found that ADTs are good for very simple structures and very simple member methods. Anything more complex requires packages. Often I will write a package that implements member methods for a given type of object, since you cannot call private methods inside an object.

It is a pain...

0


source


If you just need to use a subroutine (function / procedure) from one subroutine, PL / SQL allows you to nest a subroutine within another in a declaration block.

This is not as ideal as having private methods or functions, but it might be worth trying before creating an inheritance hierarchy.

create or replace type body some_t
as

member function foo
    return varchar2
    as
        function some_private_foo
            return varchar2
            as
            begin
                return 'Foo!';
            end some_private_foo;
    begin
        return some_private_foo();
    end foo;

end;

If you're on Oracle 12, you're in luck. You can create a package that only your type can use using the ACCESSIBLY BY clause. In the example below, the PL / SQL compiler only allows code from FOO_T to reference FOO_PRIVATE_PKG.

CREATE OR REPLACE package foo_private_pkg 
accessible by ( foo_t )
as

function some_private_foo ( object_in in out nocopy foo_t )
    return varchar2;
end;
0


source






More articles:


All Articles