How to return user-defined type as varchar2?

Question

Let's consider a table with user defined type.

create or replace type reftype is object (id number, name varchar2(40), details varchar2(1000));
create table testref(c1 reftype);
insert into testref values (REFTYPE(4, 'asd', 'aaa'));
insert into testref values (REFTYPE(3, 'asf', 'baa'));
insert into testref values (REFTYPE(2, 'asg', 'aba'));
insert into testref values (REFTYPE(1, 'ash', 'aab'));
/
select * from testref;

Select returns column with objects of user type. When I execute it in SQL*plus I will see:

SQL> select * from testref
REFTYPE(4, 'asd', 'aaa')
REFTYPE(3, 'asf', 'baa')
REFTYPE(2, 'asg', 'aba')
REFTYPE(1, 'ash', 'aab')

How can I write my query to return such output as a text (let's say varchar2).

SQL> select substr(c1,1,4) from testref;
select substr(c1,1,4) from testref
              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got KACPER.REFTYPE

The same with:

select substr(cast(c1 as varchar2(1000)),1,4) from testref;

And I would like to send string representing user defined type to application as a text not as a UDT. Can you advise me how to write a query that will return varchar2 representaion same as I can see in SQL*PLUS?

EDIT

My real case is using:

create or replace type reftypetab as table of reftype;

and query:

select cast(collect(c1) as reftypetab) from testref;

I would like to have that output either as varchar2: 'KACPER.REFTYPETAB(KACPER.REFTYPE(4,'asd','aaa'),KACPER.REFTYPE(3,'asf','baa'),KACPER.REFTYPE(2,'asg','aba'),KACPER.REFTYPE(1,'ash','aab'))' or as an XML. But when calling:

select xmltype(cast(collect(c1) as reftypetab)) from testref;

I got:

ORA-06553: PLS-306: wrong number or types of arguments in call to 'XMLTYPE'

Do you have any suggestions how can I get XML or text representation of my tabletype?

Answer 1

You can use this one:

SELECT T.c1.ID, T.c1.NAME, T.c1.details
FROM TESTREF T;

If you like to get all in one (XML string) you can also use

SELECT XMLTYPE(c1)
FROM TESTREF;

Another way is this one:

CREATE OR REPLACE TYPE reftype IS OBJECT (ID NUMBER, NAME VARCHAR2(40), details VARCHAR2(1000),
    MEMBER FUNCTION TO_VARCHAR2 RETURN VARCHAR2);

CREATE OR REPLACE TYPE BODY reftype IS 

MEMBER FUNCTION TO_VARCHAR2 RETURN VARCHAR2 IS
BEGIN
    RETURN SELF.ID||','||SELF.NAME||','||SELF.details;
END TO_VARCHAR2;

END;
/


SELECT t.c1.TO_VARCHAR2()
FROM TESTREF t;