Calling a member procedure in Oracle 11g

Question

Lets say i have:

create type address as object (
   line1 varchar2(50),
   city varchar2(50),
   member procedure insert_address(line1 varchar2, city varchar2)
)
/

create table address_table of address;

create type body address as
   member procedure insert_address(line1 varchar2, city varchar2) is
   begin
       insert into address_table values (line1, city);
       commit;
   end insert_address;
end;
/

How do i call insert_address?

By doing the following i get invalid number or types of arguments

begin
   address.insert_address('123 my road','london');
end;

i can do this and it works, but seems like a bad idea:

declare
  v_address address := new address(null,null);
begin
  v_address.insert_address('123 my road','london');
end;

Thanks

Answer 1

Use static instead of member for your procedure:

static procedure insert_address(line1 varchar2, city varchar2)

Then you can call it on the object type instead of the instance:

address.insert_address('123 my road','london');

See Using PL/SQL Object Types for more information.

Answer 2

As you have built it (which is bizarrely), the procedure insert_address can only be called in the context of an object of type address, and must be called with parameters line1 and city, with values that have no connection with the object you called it "for". This is how I would build the table and code, and use it:

create table address_table (line1 varchar2(50), city varchar2(50));

create package address_pkg as
   procedure insert_address(p_line1 varchar2, p_city varchar2);
end;
/

create package body address_pkg as
   procedure insert_address(p_line1 varchar2, p_city varchar2) is
   begin
      insert into address_table (line1, city) values (p_line1, p_city);
   end;
end;
/

exec address_pkg.insert_address ('123 my road', 'london');

With your funkier model, it seems that the insert_address procedure should insert "the address object itself" into the table. Something like:

create type address as object (
   line1 varchar2(50),
   city varchar2(50),
   member procedure insert_address
)
/

create table address_table of address;

create type body address as
   member procedure insert_address is
   begin
       insert into address_table values (line1, city);
       commit;
   end insert_address;
end;
/

Then the insert would be like:

declare
  v_address address := new address('123 my road','london');
begin
  v_address.insert_address;
end;