Missing right parenthesis error for LISTAGG ON OVERFLOW TRUNCATE

I am using ON OVERFLOW TRUNCATE logic to implement LISTAGG function.

But while implementing like below, I am getting error as

ORA-00907: missing right parenthesis

Here is the query

SELECT IMG.IMG_TYPE, LISTAGG(TO_CHAR(IMG.IMG_NAME), ' ' ON OVERFLOW TRUNCATE )
 WITHIN GROUP (ORDER BY IMG.IMG_TYPE) AS IMAGES
      FROM TBL_VSAT_MST_DETAIL VT 
        INNER JOIN TBL_VSAT_IMAGE_DETAIL IMG ON VT.VSAT_DETAIL_ID = 2414
        WHERE VT.SAP_ID = 'I-OR-GCHP-ENB-9031'
    AND VT.CANDIDATE_ID = 'C1'
        GROUP BY IMG.IMG_TYPE;

How can I solve this error?

Solution

ON OVERFLOW TRUNCATE is only available since Oracle 12c, which got released about nine years ago. Latest version is 21c. You should consider an upgrade.

Anyway, your query makes it seems as if you were inner joining the tables, while you are actually cross joining them. You look at certain TBL_VSAT_MST_DETAIL rows, and to each of these you join all TBL_VSAT_IMAGE_DETAIL rows.

This is what you have, written with a proper join clause:

SELECT
  img.img_type,
  LISTAGG(TO_CHAR(img.img_name), ' ' ON OVERFLOW TRUNCATE )
    WITHIN GROUP (ORDER BY img.img_type) AS images
FROM tbl_vsat_mst_detail vt 
CROSS JOIN tbl_vsat_image_detail img
WHERE vt.sap_id = 'I-OR-GCHP-ENB-9031'
  AND vt.candidate_id = 'C1'
  AND vt.vsat_detail_id = 2414
GROUP BY img.img_type;

I suppose - but I can only guess here, as I don't even know what your tables look like exactly - that you want something like this:

SELECT
  img.img_type,
  LISTAGG(img.img_name, ' ') WITHIN GROUP (ORDER BY img.img_name) AS images
FROM tbl_vsat_image_detail img
WHERE img.vsat_id IN
(
  SELECT vt.vsat_id
  FROM tbl_vsat_mst_detail vt 
  WHERE vt.sap_id = 'I-OR-GCHP-ENB-9031'
    AND vt.candidate_id = 'C1'
    AND vt.vsat_detail_id = 2414
)
GROUP BY img.img_type
ORDER BY img.img_type;

Maybe without the cartesian join, there is no overflow anymore and the problem is solved for the moment.