PL/SQL for implode function on custom types

Question

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).

Beals

Answer 1

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;

Armlet

Answer 2

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-)

Devoid

Great inheritance example of object types! – Snaggy