PL / SQL for the implode function for custom types

Question

Is there a way to create an implode subroutine in PL / SQL that takes a user-defined data type as a parameter and concatenates its elements separated by a specific string?

For example, let's say I have the following type:

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

Then, say, the function returns an object of type myPerson , but I want the columns to be merged together:

 SELECT implode(getPerson(1234),'$$') from dual; 

(suppose the data in this far-fetched example is configured):

 John$$Doe$$55 

If the separator can be specified as an optional parameter, but the type of the first parameter can be any (not necessarily myPerson ).

Answer 1

A custom data type may support methods and methods that may 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; 

Answer 2

You can create a general way to handle these strings using inheritance and polymorphism. If we are going to use objects, we must use the capabilities of object-oriented programming.

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

 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 that inherits from STRINGABLE_TYPE. The OVERRIDING keyword is required, although a parent type declaration forces 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 that takes a generic type and calls a 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 transfer two different objects to functions that will actually perform 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> 

Enough work to get to this point. That would be neat if Oracle TYPE had at least the abstract TO_STRING () baked in it, which we could simply override. But this is only one of many shortcomings in the implementation of facilities. 8)