How can I delete a member function created in oracle

Question

How can I delete a member function created inside oracle type object without dropping the entire type?

Answer 1

I never used it but according documentation you might be able to use

alter type {type_name} DROP MEMBER {function_spec} CASCADE;

Note, {function_spec} is the function name plus RETURN clause, e.g.

ALTER TYPE ttest DROP MEMBER FUNCTION f_name_2 RETURN VARCHAR2 CASCADE;

Afterwards you have to re-create the TYPE BODY without the dropped function, i.e. CREATE OR REPLACE TYPE BODY ...

Answer 2

As far as I can tell, you can't (unless you call CREATE OR REPLACE to be not dropping).

P.S. What's even worse, if there are dependent types that use the type with a member function you'd want to drop, you'll need to drop everything cascade. Which means that - as a function doesn't have to eat or drink - leave it alone.

[EDIT; after seeing Wernfried's answer]

It seems that I was wrong. Apparently, it really works. Syntax is somewhat strange (it requires to specify the RETURN clause (?!?)), but hey - that's what Oracle says. So, today I learnt something new.

An example:

SQL> create or replace type ttest is object
  2    (some_name varchar2(20),
  3     member function f_name return varchar2
  4    );
  5  /

Type created.

SQL> create or replace type body ttest as
  2    member function f_name
  3      return varchar2
  4    is
  5    begin
  6      return self.some_name;
  7    end f_name;
  8  end;
  9  /

Type body created.

SQL> alter type ttest drop member function f_name return varchar2;

Type altered.

SQL> desc ttest
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SOME_NAME                                          VARCHAR2(20)

SQL>