Query to Print this Data pattern in Pl/SQL

Question

How can I print data in this pattern in Pl/SQL

1~ABC~20-June-1990^2~DEF~03-Aug-1999^3~IJK~19-DEC-1976^....

Here this 1, ABC, 20-June-1990 likewise other 2, DEF, 03-Aug-1999 stored in table. I have to fetch this data and print in the above format in a ROW only.

I am done with data fetch but not getting any clue for this pattern printing.

Answer 1

PL/SQL reads "Oracle". If so, then concatenate separate column values and use listagg to aggregate them all into the final result.

Maybe you don't need PL/SQL as SQL is capable of doing it in at least two ways.

Sample data:

SQL> select empno, ename, to_char(hiredate, 'dd-Mon-yyyy') hiredate
  2  from emp
  3  where deptno = 10;

     EMPNO ENAME      HIREDATE
---------- ---------- -----------
      7782 CLARK      09-Jun-1981
      7839 KING       17-Nov-1981
      7934 MILLER     23-Jan-1982

SQL>

Result:

SQL> select listagg(empno || '~' || ename || '~' || to_char(hiredate, 'dd-Mon-yyyy'), '^')
  2         within group (order by empno) as result
  3  from emp
  4  where deptno = 10;

RESULT
--------------------------------------------------------------------------------
7782~CLARK~09-Jun-1981^7839~KING~17-Nov-1981^7934~MILLER~23-Jan-1982

---

If the final result is longer than 4000 characters (is it?), then listagg won't work because of its limits - in that case, you'd use XMLAGG function.

Or: if it really is PL/SQL, you can always concatenate that string into a locally declared CLOB datatype variable.