How to return user-defined type as varchar2?
<p>Let's consider a table with user defined type.</p>

<pre><code>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;
</code></pre>

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

<pre><code>SQL&gt; select * from testref
REFTYPE(4, 'asd', 'aaa')
REFTYPE(3, 'asf', 'baa')
REFTYPE(2, 'asg', 'aba')
REFTYPE(1, 'ash', 'aab')
</code></pre>

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

<pre><code>SQL&gt; 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
</code></pre>

<p>The same with:</p>

<pre><code>select substr(cast(c1 as varchar2(1000)),1,4) from testref;
</code></pre>

<p>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?</p>

<p><strong>EDIT</strong></p>

<p>My real case is using: </p>

<pre><code>create or replace type reftypetab as table of reftype;
</code></pre>

<p>and query:</p>

<pre><code>select cast(collect(c1) as reftypetab) from testref;
</code></pre>

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

<pre><code>select xmltype(cast(collect(c1) as reftypetab)) from testref;
</code></pre>

<p>I got:</p>

<pre><code>ORA-06553: PLS-306: wrong number or types of arguments in call to 'XMLTYPE'
</code></pre>

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