How to convert CLOB to VARCHAR2 in Oracle

Question

I am trying to transform a data that has been saved in a table as CLOB to varchar2. When the query is executed the value that is returned is still a CLOB. What can I be doing wrong when it comes to building the query?

I tried

select l.user_id, l.login, l.is_active, l.is_locked, 
(SELECT rtrim(xmlagg(xmlelement(e,lav.ATTR_VALUE,',').extract('//text()') order by TO_CHAR(lav.ATTR_VALUE)).getClobVal,',') 
FROM LOGIN l1,LOGIN_ATTRIBUTES la,LOGIN_ATTRIBUTE_VALUES lav 
WHERE l1.LOGIN_ID = la.LOGIN_ID AND la.ID = lav.LOGIN_ATTRIBUTE_ID AND la.NAME = 'email') as EMAIL 
from IAMUSER.LOGIN l 
left join IAMUSER.MANAGED_SYS ms on ms.managed_sys_id = l.managed_sys_id 
where l.managed_sys_id = 'MOODLE' and login = 'ex392310'

Results:

USER_ID                         |LOGIN   |IS_ACTIVE|IS_LOCKED|EMAIL |
--------------------------------+--------+---------+---------+------+
8a8c9321799d403d01799e401527011c|ex392310|Y        |N        |[CLOB]|

Where do you think you're converting the value to `VARCHAR2`? You `RTRIM` a CLOB, which means the result is a CLOB. — Mark Rotteveel, Jun 23 '22 at 09:12

score 0 · Answer 1 · answered Jun 23 '22 at 12:07

Change getClobVal() to getStringVal():

select l.user_id,
       l.login,
       l.is_active,
       l.is_locked, 
       ( SELECT rtrim(
                  xmlagg(
                    xmlelement(e,lav.ATTR_VALUE,',').extract('//text()')
                    order by TO_CHAR(lav.ATTR_VALUE)
                  ).getStringVal(),
                  ','
                ) 
         FROM   LOGIN l1
                INNER JOIN LOGIN_ATTRIBUTES la
                ON (l1.LOGIN_ID = la.LOGIN_ID)
                INNER JOIN LOGIN_ATTRIBUTE_VALUES lav
                ON (la.ID = lav.LOGIN_ATTRIBUTE_ID)
         WHERE  la.NAME = 'email'
       ) as EMAIL 
from   IAMUSER.LOGIN l 
       left join IAMUSER.MANAGED_SYS ms
       on ms.managed_sys_id = l.managed_sys_id 
where  l.managed_sys_id = 'MOODLE'
and    login = 'ex392310'

Or, use LISTAGG:

select l.user_id,
       l.login,
       l.is_active,
       l.is_locked, 
       ( SELECT LISTAGG(lav.ATTR_VALUE,',')
                  WITHIN GROUP (ORDER BY TO_CHAR(lav.ATTR_VALUE)) 
         FROM   LOGIN l1
                INNER JOIN LOGIN_ATTRIBUTES la
                ON (l1.LOGIN_ID = la.LOGIN_ID)
                INNER JOIN LOGIN_ATTRIBUTE_VALUES lav
                ON (la.ID = lav.LOGIN_ATTRIBUTE_ID)
         WHERE  la.NAME = 'email'
       ) as EMAIL 
from   IAMUSER.LOGIN l 
       left join IAMUSER.MANAGED_SYS ms
       on ms.managed_sys_id = l.managed_sys_id 
where  l.managed_sys_id = 'MOODLE'
and    login = 'ex392310'

How to convert CLOB to VARCHAR2 in Oracle

1 Answers1