How to completely drop this function from Oracle database

I have used the following code to create a function, however, it contains many sub functions, etc. How can I completely drop all the stuff that it created? Thanks! I got this code from the web.
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),
 
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,
 
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,
 
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,
 
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS
 
 
CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;
 
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;
 
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;
 
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS
 
 
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS

Open in new window

mawinghoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MarkusIdConnect With a Mentor Commented:
Just the way you created it:

drop function string_agg;
drop type body  t_string_agg;
drop type t_string_agg;
0
 
mawinghoAuthor Commented:
becuase it contains MEMBER FUNCTION ODCIAggregateMerge, I don't know how to drop it...
0
 
MarkusIdConnect With a Mentor Commented:
The MEMBER FUNCTIONs are part of the 't_string_agg'-type-body, so dropping this type body should also drop the included member functions (the last 'END' before the second 'SHOW ERRORS' belongs to the 'CREATE OR REPLACE TYPE BODY t_string_agg IS'.

It's like a package where you can have several procedures/functions, dropping the package also drops the included procedures/functions.
0
 
mawinghoAuthor Commented:
Thanks for your explanation!
0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Technically speaking, the drop type body is not necessary.  Dropping the type will drop the body, just like with a package.
0
All Courses

From novice to tech pro — start learning today.