locked
Concate all values of table in single row RRS feed

  • 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.

    Monday, February 20, 2012 3:32 AM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 21, 2012 10:21 PM

All replies

  • 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.  

    Monday, February 20, 2012 12:48 PM
  • 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

    Tuesday, February 21, 2012 4:56 AM
  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 21, 2012 10:21 PM