Trying to execute a dynamic pivot in pl sql

Question

I am trying to execute a dynamic pivot in pl sql. This is what I have done so far

DECLARE
  col_names VARCHAR2(4000);
  pivot_table VARCHAR2(4000);
BEGIN
  SELECT LISTAGG('''' || base_name || '''', ',') WITHIN GROUP (ORDER BY base_name)
  INTO col_names
  FROM (SELECT DISTINCT base_name FROM vfact_basis WHERE vfact_id = 'CA000123-2822') t;

  pivot_table := 'WITH base_value AS (
    SELECT *
    FROM (
      SELECT vfact_base_period.vfact_id, amount, base_name
      FROM VFACT_BASE_PERIOD
      INNER JOIN VFACT_BASIS ON VFACT_BASE_PERIOD.VFACT_ID = VFACT_BASIS.VFACT_ID AND VFACT_BASE_PERIOD.BASE_ID = VFACT_BASIS.BASE_ID
      WHERE VFACT_BASE_PERIOD.VFACT_ID = ''CA000123-2822''
    )
    PIVOT (
      SUM(amount)
      FOR base_name
      IN (' || col_names || ')
    )
  )
  select * from base_value';

  dbms_output.put_line(pivot_table);
  
  EXECUTE IMMEDIATE pivot_table;

END;
/

Everything runs as it should and when I copy and paste the string contained in col_names it runs how it should. However when I run this Query nothing shows up other then the put_line statement. It does not seem like the EXECUTE IMMEDIATE statement is working or doing anything.

I am expecting this query to be run and the resulting table to be outputted.

WITH base_value AS (
    SELECT *
    FROM (
      SELECT vfact_base_period.vfact_id, amount, base_name
      FROM VFACT_BASE_PERIOD
      INNER JOIN VFACT_BASIS ON VFACT_BASE_PERIOD.VFACT_ID = VFACT_BASIS.VFACT_ID AND VFACT_BASE_PERIOD.BASE_ID = VFACT_BASIS.BASE_ID
      WHERE VFACT_BASE_PERIOD.VFACT_ID = ''CA000123-2822''
    )
    PIVOT (
      SUM(amount)
      FOR base_name
      IN (' || col_names || ')
    )
  )
  select * from base_value';

Answer 1

I am expecting this query to be run

The query is being run by EXECUTE IMMEDIATE.

and the resulting table to be outputted.

You are not asking for it to be output anywhere; so it is being run but you don't output it so of course you will not see anything.

If you want to display it then you need to either:

1. Use EXECUTE IMMEDIATE your_query BULK COLLECT INTO a_collection_variable; and then loop through that collection and print it to the console using DBMS_OUTPUT;
2. Use a cursor loop and print each row of the output to the console using DBMS_OUTPUT;
3. On later versions that support it, use DBMS_SQL and DBMS_SQL.RETURN_RESULT; or
4. If you are using SQL*Plus or SQL Developer, you could create a cursor bind variable and then open that cursor for your dynamic SQL and then use the client-side print command to display the result set from the cursor (but that only works for certain client applications; there may be equivalent solutions for different client applications using their own syntaxes).