Getting CLOB completed from DB table via SQL in Oracle

Question

I have a table (actually a view) with few varchar2 fields. Say, v_report(id, name, profile_id, profile_name);

I need to collect id-groupped data into a string. So, I do:

SELECT
   id,
   'Name: ' || clobagg(DISTINCT name)
   || ' Profile_name: ' || clobagg(DISTINCT profile_name)
   as description
FROM
   v_report
GROUP BY
   id

clobagg is like described here: https://community.oracle.com/thread/2264483

(especially use clobagg not stragg due to it's returning value clob able to store >4000 chars)

It works fine when concatenated profile string isn't too large. But if it is i get fair Oracle exception:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 7680, maximum: 4000)

Is there some way out? I mean something like DBMS_LOB.APPEND function but available to call via SQL. Or any other way to concat many varchar2 strings into a large CLOB string w/o length limit.

Thanks in advance.

Pavel.

Answer 1

NOTE: initially posted as a Comment, but offered as an Answer now, since the OP has confirmed that this was, in fact, the problem.

So, are you able to create the CLOBs in the first place (without concatenation)? If you are, then what you are missing is wrapping the literals (and any other VARCHAR2 values you may have) within to_clob(). Good luck!