Hello Experts,
Good Day to all...
I need your help on following scenarios. The below query returns set of titleID strings. Instead of printing them one below the other as query output, I want the output to be in batch of 25 values.i.e each row should have 25 values separated by comma. i.e If there are 100 titles satisfying the output, then there should be only four rows with and each row having 25 titles in comma separated manner.
SELECT DISTINCT title_id
FROM pack_relation
WHERE package_id IN ( SELECT DISTINCT fa.package_id
FROM annotation fa
GROUP BY fa.package_id
HAVING COUNT
(fa.package_id) <100);
I tried with the PL/SQL block; whereas it is printing all the values continously :(
I need to stop with 25 values and display.
If its possible with SQL block alone; then it would be of great help
DECLARE
v_str VARCHAR2 (32767) := NULL;
CURSOR c1
IS
SELECT DISTINCT title_id
FROM pack_relation
WHERE package_id IN ( SELECT DISTINCT fa.package_id
FROM annotation fa
GROUP BY fa.package_id
HAVING COUNT
(fa.package_id) <100);
BEGIN
FOR i IN c1
LOOP
v_str := v_str || ',' || i.title_id;
END LOOP;
v_str := SUBSTR (v_str, 2);
DBMS_OUTPUT.put_line (v_str);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error-->' || SQLERRM);
END;
Thanks...