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
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;
pratikasthan...
Member
67 Points
57 Posts
Concate all values of table in single row
Feb 20, 2012 07:32 AM|LINK
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.
Lannie
Contributor
3840 Points
749 Posts
Re: Concate all values of table in single row
Feb 20, 2012 04:48 PM|LINK
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.
pratikasthan...
Member
67 Points
57 Posts
Re: Concate all values of table in single row
Feb 21, 2012 08:56 AM|LINK
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
Lannie
Contributor
3840 Points
749 Posts
Re: Concate all values of table in single row
Feb 22, 2012 02:21 AM|LINK
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.