René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

August 30, 2005: On a member function calling a member procedure

Consider the following (simple) object type which I create only to demonstrate that it needs a special syntax to call a member procedure from a member function.
create or replace type some_type as object (

  some_date date,

  constructor function some_type return self as result,

  member procedure set_some_date_to_1st_of_month
) not final;
/

create or replace type body some_type as 
  constructor function some_type return self as result is begin
    select sysdate into some_date from dual;
    return;
  end some_type;

  member procedure set_some_date_to_1st_of_month is begin
    select trunc(sysdate, 'mon') into some_date from dual;
  end set_some_date_to_1st_of_month;
end;
/
Another type is derived from some_type:
create or replace type derived_type under some_type (
  constructor function derived_type return self as result,

  member function get_1st_of_month return date
);
/

create or replace type body derived_type as 
  constructor function derived_type return self as result is begin
    select sysdate into some_date from dual;
    return;
  end derived_type;

  member function get_1st_of_month return date as begin
    self.set_some_date_to_1st_of_month;
    return some_date;
  end get_1st_of_month;
end;
/
Unfortunately, this derived type cannot be created:
show errors
Errors for TYPE BODY DERIVED_TYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5      PL/SQL: Statement ignored
4/5      PLS-00363: expression 'SELF' cannot be used as an assignment
         target
What's going on here? This error message doesn't seem to make much sense (now). What does the Oracle manual say about this error: PLS-00363: expression 'string' cannot be used as an assignment target
Cause: A literal, constant, IN parameter, loop counter, or function call was mistakenly used as the target of an assignment. For example, the following statement is illegal because the assignment target, 30, is a literal: SELECT deptno INTO 30 FROM dept WHERE ... -- illegal
Action: Correct the statement by using a valid assignment target.
However, there is certainly no literal, constant, in parameter, loop counter or function call being the target of an assignment.
Or is there?
On closer inspection, there is an implicit self parameter passed in get_1st_of_month. By default for member functions, this self parameter is in. This self parameter is (again implicitly) passed to the procedure set_some_date_to_1st_of_month when this procedure is called. This creates a conflict: the procedure might modify (and actually does modify in this case here) self. get_1st_of_month however mandates that self be not changed.
So, the derived type must be changed in order to compile:
create or replace type derived_type under some_type (

  constructor function derived_type return self as result,

  member function get_1st_of_month(self in out derived_type) return date
);
/

create or replace type body derived_type as 

  constructor function derived_type return self as result is begin
    select sysdate into some_date from dual;
    return;
  end derived_type;

  member function get_1st_of_month(self in out derived_type) return date as begin
    self.set_some_date_to_1st_of_month;
    return some_date;
  end get_1st_of_month;

end;
/
It now compiles and can be used:
declare
  d derived_type := derived_type();
begin
  dbms_output.put_line(d.get_1st_of_month);
end;
/
This prints the first day of the month.
01-AUG-05

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.