Concate all values of table in single row

Question

User64117442 posted

Hi,

I am using this query to concate the value of table called "COUNTRY".

SELECT SUBSTR(SYS_CONNECT_BY_PATH(COUNTRY_NAME,','),2) TABLE_LIST
FROM (
SELECT ROWNUM ROWNO,COUNTRY_NAME
FROM COUNTRY )
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR ROWNO = ROWNO - 1
START WITH ROWNO = 1;

but it is showing error message

ORA-01489: result of string concatenation is too long

Please help

Thanks in Advance.

Answer 1

User269602965 posted

Oracle VARCHAR2 datatype is limited to 4000 bytes.

What is the estimated size of your concatenations?

If it is more than 4000 bytes, you will have to CAST the result into a CLOB object (character large binary object).

In that scenario you will have to read Oracle documentation to see if CONNECT_BY supports CLOB datatype.

Try this first

SELECT

CAST(SUBSTR(SYS_CONNECT_BY_PATH(COUNTRY_NAME,','),2) AS VARCHAR2(4000)) AS TABLE_LIST

FROM ( etc.  

Answer 2

User64117442 posted

IF I AM USING THIS QUERY 

SELECT CAST(SUBSTR(SYS_CONNECT_BY_PATH(COUNTRY_NAME,','),2) AS VARCHAR2(4000)) AS TABLE_LIST
FROM (
SELECT ROWNUM ROWNO,COUNTRY_NAME
FROM COUNTRY )
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR ROWNO = ROWNO - 1
START WITH ROWNO = 1;

IT IS SHOWING SAME ERROR 

ORA-01489: result of string concatenation is too long

BUT IF I AM USING IT LIKE

SELECT CAST(SUBSTR(SYS_CONNECT_BY_PATH(COUNTRY_NAME,','),2) AS CLOB) AS TABLE_LIST
FROM (
SELECT ROWNUM ROWNO,COUNTRY_NAME
FROM COUNTRY )
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY PRIOR ROWNO = ROWNO - 1
START WITH ROWNO = 1;

SHOWING ERROR MESSAGE

ORA-00932: inconsistent datatypes: expected - got CLOB

Answer 3

User269602965 posted

I suspect your return result is greater than 4000 bytes.

And as I expected, I checked Oracle documentation, and CLOB IS NOT SUPPORTED in CONNECT BY type statements.

You will have to rethink what you are trying to accomplish and how to accomplish it.