PL/SQL for implode function on custom types

Is there any way to create an implode routine in PL/SQL that takes any custom datatype as a parameter and concatenates its members, delimited by some specified string?

For example, say I've got the following type:

CREATE TYPE myPerson AS OBJECT(
  forename VARCHAR2(50),
  surname  VARCHAR2(50),
  age      NUMBER
);

Then, say a function returns an object of type myPerson, but I want the columns concatenated together:

SELECT implode(getPerson(1234),'$$') from 开发者_运维百科dual;

to return (supposing the data in this contrived example is set up):

John$$Doe$$55

Where the delimiter can be specified as an optional parameter, but the type of the first parameter could be anything (not necessarily myPerson).

Your custom datatype can support methods and methods can have parameters.

CREATE TYPE myPerson AS OBJECT(   
  forename VARCHAR2(50),   
  surname  VARCHAR2(50),   
  age      NUMBER,

  MEMBER FUNCTION
  get_record(pGlue IN varchar2)   RETURN VARCHAR2 );

CREATE TYPE BODY myPerson 
AS 
   MEMBER FUNCTION get_record(pGlue varchar2) RETURN VARCHAR2

 IS
BEGIN
 RETURN forename || pGlue  || surname  || pGlue || age ;
END get_record;

END;

It is possible to build a generic way of handling these strings, by using inheritance and polymorphism. If we're going to use objects, we should leverage the capabilities of object-orineted programming.

Firstly we need a root object. This TYPE is not instantiable, which means we cannot actually declare an instance of it. Note that the TO_STRING() member function is also declared as NOT INSTANTIABLE. This means any TYPE which inherits from STRINGABLE_TYPE must have its own implementation of the method.

SQL> create or replace type stringable_type as object
  2        ( id number(7,0)
  3          , NOT INSTANTIABLE member function to_string
  4                          return varchar2
  5      )
  6  not final not instantiable
  7  /

Type created.

SQL>

Here is one type which inherits from STRINGABLE_TYPE. The OVERRIDING keyword is mandatory, even though the declartion of the parent type compels us to implement it.

SQL> create or replace type emp_type under stringable_type
  2   ( empno number(7,0)
  3     , ename varchar2(20)
  4     , sal number(7,2)
  5     , OVERRIDING member function to_string
  6                          return varchar2
  7      );
  8  /

Type created.

SQL> create or replace type body emp_type
  2  is
  3      OVERRIDING member function to_string
  4                          return varchar2
  5      is
  6      begin
  7          return 'EMP>>'||self.id||'='||self.empno||'::'||self.ename||'::'||self.sal;
  8      end;
  9  end;
 10  /

Type body created.

SQL>

Here is another type...

SQL> create or replace type dept_type under stringable_type
  2   ( deptno number(2,0)
  3     , dname varchar2(30)
  4     , OVERRIDING member function to_string
  5                          return varchar2
  6      );
  7  /

Type created.

SQL> create or replace type body dept_type
  2  is
  3      OVERRIDING member function to_string
  4                          return varchar2
  5      is
  6      begin
  7          return 'DEPT>>'||self.id||'='||self.deptno||'::'||self.dname;
  8      end;
  9  end;
 10  /

Type body created.

SQL>

Now, we can create a function which takes the generic type and invokes the generic method:

SQL> create or replace function type_to_string
  2      (p_obj in stringable_type)
  3      return varchar2
  4  is
  5  begin
  6      return p_obj.to_string();
  7  end;
  8  /

Function created.

SQL>

Through the wonders of polymorphism we can pass two different objects to the function, which will actually execute the overriding method:

SQL> set serveroutput on
SQL> declare
  2     obj1 emp_type;
  3     obj2 dept_type;
  4  begin
  5      obj1 := emp_type(1, 8000, 'VAN WIJK', 3500);
  6      obj2 := dept_type(2, 20, 'COMMUNICATIONS');
  7      dbms_output.put_line(type_to_string(obj1));
  8      dbms_output.put_line(type_to_string(obj2));
  9  end;
 10  /
EMP>>1=8000::VAN WIJK::3500
DEPT>>2=20::COMMUNICATIONS

PL/SQL procedure successfully completed.

SQL>

It's quite a lot of work to get to this point. It would be neat if Oracle's TYPE at least had an abstract TO_STRING() baked into it, which we could just override. But that is just one of many loose ends in their object implementation 8-)