Hi Gurus,
length of a column I am extracting is > 4000 bytes, resulting concatenation error.
I am breaking this column into multiple pieces and converting as a clob.
I created a proc and length as a clob now is 6000 bytes, however if I execute and check length of a line it is not 6000.
My query generates fixed length column > 400 bytes.
Is there any solution to this issue?
Error:
SELECT RPAD('X', 2000, 'X')||RPAD('X', 2001, 'X')FROM DUAL; -- concatenation error
ORA-01489: result of string concatenation is too long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.
SELECT LENGTH(TO_CLOB(RPAD('X', 4001, 'X'))||TO_CLOB(RPAD('X', 4001, 'X'))) FROM DUAL; -- works
PROC:
CREATE OR REPLACE PROCEDURE MY_PROC( OUT_REFCURSOR OUT SYS_REFCURSOR)
IS
BEGIN
OPEN OUT_REFCURSOR FOR
SELECT TO_CLOB(RPAD('X', 2000, 'X'))|| TO_CLOB(RPAD('X', 4000, 'X'))FROM DUAL
UNION ALL
SELECT TO_CLOB(RPAD('Y', 2000, 'Y'))|| TO_CLOB(RPAD('Y', 4000, 'Y'))FROM DUAL
UNION ALL
SELECT TO_CLOB(RPAD('Z', 2000, 'X'))|| TO_CLOB(RPAD('Z', 4000, 'Z'))FROM DUAL;
END;
/
Validate length: length <> 6000
VARIABLE OUT_REFCURSOR REFCURSOR;
EXEC MY_PROC(:OUT_REFCURSOR);
PRINT OUT_REFCURSOR
Thanks