Why isn't my dynamic pivot table query result showing up on the screen in Oracle SQL?

I got a problem with a sql query.

DECLARE
    pivot_cols VARCHAR2(2000);
    sql_stmt VARCHAR2(4000);
BEGIN
    -- Erzeuge eine kommaseparierte Liste der Datumsfelder
    SELECT LISTAGG('''' || datum || '''', ',')
    INTO pivot_cols
    FROM copi_bestand_hk;

    -- Erstelle die dynamische SQL-Abfrage
    sql_stmt :=
        'SELECT *
        FROM (
            select * from copi_bestand_hk
        )
        PIVOT (
            SUM(bestandswert_hk)
            FOR datum IN (' || pivot_cols || ')
        )
        ';

    -- Führe die dynamische SQL-Abfrage aus
    EXECUTE IMMEDIATE sql_stmt;
END;

After executing the query i want the result to be shown on the screen.
But i only get the confirmation message that the query was successfully executed.

What can I do to show the pivot table?

Thanks for your help

You executed it, but - where to?

I don't have your tables so I'll illustrate it on Scott's sample schema.

SQL> set serveroutput on
SQL> DECLARE
  2     pivot_cols  VARCHAR2 (4000);
  3     sql_stmt    VARCHAR2 (4000);
  4  BEGIN
  5     SELECT LISTAGG (CHR (39) || job || CHR (39), ',') WITHIN GROUP (ORDER BY NULL)
  6       INTO pivot_cols
  7       FROM emp
  8      WHERE deptno = 10;
  9
 10     sql_stmt := 'select * from (select deptno, job, sal from emp)
 11                  pivot (sum(sal) for job in (' || pivot_cols || '))';
 12
 13     DBMS_OUTPUT.put_line (sql_stmt);
 14
 15     EXECUTE IMMEDIATE sql_stmt;
 16  END;
 17  /
select * from (select deptno, job, sal from emp)
                pivot (sum(sal)
for job in ('CLERK','MANAGER','PRESIDENT'))

PL/SQL procedure successfully completed.

That query (when manually executed) returns

SQL> select * from (select deptno, job, sal from emp)
  2                  pivot (sum(sal)
  3  for job in ('CLERK','MANAGER','PRESIDENT'))
  4  /

    DEPTNO    'CLERK'  'MANAGER' 'PRESIDENT'
---------- ---------- ---------- -----------
        30        950       2850
        20       1940       2975
        10       1300       2450        5000

SQL>

What to do? One option (rather simple) is to convert anonymous PL/SQL block into a function that returns refcursor.

SQL> CREATE OR REPLACE FUNCTION f_test
  2     RETURN SYS_REFCURSOR
  3  IS
  4     pivot_cols  VARCHAR2 (4000);
  5     sql_stmt    VARCHAR2 (4000);
  6     rc          SYS_REFCURSOR;
  7  BEGIN
  8     SELECT LISTAGG (CHR (39) || job || CHR (39), ',') WITHIN GROUP (ORDER BY NULL)
  9       INTO pivot_cols
 10       FROM emp
 11      WHERE deptno = 10;
 12
 13     sql_stmt := 'select * from (select deptno, job, sal from emp)
 14                  pivot (sum(sal) for job in (' || pivot_cols || '))';
 15
 16     OPEN rc FOR sql_stmt;
 17
 18     RETURN rc;
 19  END;
 20  /

Function created.

Testing:

SQL> SELECT f_test FROM DUAL;

F_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO    'CLERK'  'MANAGER' 'PRESIDENT'
---------- ---------- ---------- -----------
        30        950       2850
        20       1940       2975
        10       1300       2450        5000


SQL>

Your PL/SQL block does not display anything because you only tell PL/SQL to run the query and do not tell it to store the output anywhere or to display that output.

In SQL*Plus and SQL Developer, you can use a bind variable to store a cursor and then print it:

VARIABLE cur REFCURSOR;

DECLARE
    pivot_cols VARCHAR2(2000);
    sql_stmt VARCHAR2(4000);
BEGIN
    SELECT LISTAGG('''' || datum || '''', ',')
    INTO pivot_cols
    FROM copi_bestand_hk;

    sql_stmt :=
        'SELECT *
        FROM (
            select * from copi_bestand_hk
        )
        PIVOT (
            SUM(bestandswert_hk)
            FOR datum IN (' || pivot_cols || ')
        )';

    OPEN :cur FOR sql_stmt;
END;
/

PRINT cur;

In other languages (C++, Java, Python, PHP, etc) you can use the features of those languages to return a cursor into a bind variable and display it using an appropriate technique from that language.