Hi,
The issue that I am going to explain here is bit weird and strange.
Coming straight to the issue; There is a package which consists of 10 procedures. One of them calls rest 9 procedures one by one.
In one of the 9 procedure, it throws exception as:
ERROR: ORA-01489: result of string concatenation is too long Failed
Object: TABLE_1
And the query wherein the error arises is:
SELECT COLUMN1 ,
LISTAGG(COLUMN2, '<>') WITHIN GROUP( ORDER BY ID) TITLE
FROM TABLE2 B
WHERE B.COLUMN1 NOT IN ('111222','333444')
AND TRIM(COLUMN1) IS NOT NULL
GROUP BY COLUMN1But, when I execute the same procedure separately in plsql developer, it runs absolutely fine without any error.
Am unable to figure out what and how does same procedure behave differently; one when executed through autosys job(or directly from server) and other when executed through plsql developer tool.
This is first time am facing such strange issue. Any guidance/help would be greatly appreciated.
I can easily handle that error, but first need to figure out why does the same procedure doesn't give any error when executed through plsql developer.
Pardon me if this sound stupid to anyone, but believe me this is what I have observed since a week.
Thanks in advance. :)
You get the ORA-01489 error when listagg returns a string longer than the varchar2 limit (4,000 or 32,767 bytes, depending on your max_string_size setting).
Most likely cause is the call inside the procedure is part-way through a transaction, so sees different data to you.
Are you sure the values in the table are the same when you run the query vs. when it errors?
You can avoid the error using the overflow clause:
listagg ( ... on overflow truncate )
But this results in data loss. There are other workarounds available - see:
https://blogs.oracle.com/datawarehousing/managing-overflows-in-listagg