PLSQL is not returning the expected pivot table in oracle apex release 20.2

Question

I am working on building a classic report where I want to view the results in a table format. I am using the source as "Function Body Returning SQL Query" Language is set to PL/SQL. This is the PL/SQL I am using to generate the table and it validates successfuly. What I am having issues with is when this runs it is only returning first column sample_id and not the pivoted results. When I change the :P20_ASSAY_ID to the actual ASSAY_ID like 801 this code then returns the pivoted results.

This code only results the first column and not pivoted results:

 declare
  sqlqry clob;
  cols  clob;

begin
 select  listagg( '''' || instrument || ''' as "' || instrument || '"', ',' ) within group (order by instrument) 
        into cols
            from 
                ( select distinct instrument
                from calibration_samples_onsite
                where assay_id = :P20_ASSAY_ID) ;

  sqlqry :=
        
   'select *
  from ( select sample_id, round((pre_cal_result-post_cal_result),3) Absolute_Change, round((((pre_cal_result-post_cal_result)/ pre_cal_result)*100),3) as Percent_Change ,instrument
    from calibration_samples_onsite
    where assay_id = :P20_ASSAY_ID
   )
   pivot  (
    max(Absolute_Change) as Absolute_Change , max(Percent_Change) as Percent_Change
   for instrument in               
            (' || cols  || ')
   )';
  
   
return (sqlqry);

end;

Results with Page item :P20_ASSAY_ID

when I change the code to the actual assay 801 I then get pivoted results? I am just trying to figure out what I am doing wrong here. Thanks

The last image are the results with 801 hard coded as the ASSAY_ID

Adding ddl for the table

CREATE TABLE  "CALIBRATION_SAMPLES_ONSITE" 
   (    "ID" NUMBER, 
    "SAMPLE_ID" VARCHAR2(30) COLLATE "USING_NLS_COMP", 
    "PRE_CAL_RESULT" NUMBER, 
    "POST_CAL_RESULT" NUMBER, 
    "ASSAY_ID" NUMBER, 
    "INSTRUMENT" VARCHAR2(150) COLLATE "USING_NLS_COMP",
     CONSTRAINT "CALIBRATION_SAMPLES_ONSITE_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP"
/

enter image description here

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(463,'1',0.73,0.7,801,'V511');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(464,'1',0.68,0.69,801,'V512');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(465,'2',1.47,1.65,801,'V511');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(466,'2',1.44,1.56,801,'V512');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(467,'3',10.51,10.97,801,'V511');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(468,'3',10.28,10.56,801,'V512');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(469,'4',0.38,0.39,801,'V511');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(470,'4',0.36,0.36,801,'V512');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(471,'1U',27.3,27.1,801,'V511');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(472,'1U',26.5,26.9,801,'V512');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(473,'2U',107.1,110.4,801,'V511');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(474,'2U',101.9,108.4,801,'V512');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(475,'3U',132.9,135.3,801,'V511');

INSERT INTO CALIBRATION_SAMPLES_ONSITE
(ID, SAMPLE_ID, PRE_CAL_RESULT, POST_CAL_RESULT, ASSAY_ID, INSTRUMENT)
VALUES
(476,'3U',127,129,801,'V512');

Answer 1

In my case I got it work by setting the "Generic Column" names attribute to on. That means that the column names will have generic names. That can be solved by using page items as headers for the columns and calculate those values separately:

This gives:

Now change add one page item per column and initialize them in the pl/sql block. Order needs to be the same as the listagg function. In my example the column headers are named P1_COLHEAD1, P1_COLHEAD2, etc...

...
               where assay_id = :P1_ASSAY_ID) ;  

  FOR r IN (select distinct instrument
                from calibration_samples_onsite
                where assay_id = :P1_ASSAY_ID ORDER BY instrument )
  LOOP
    apex_util.set_session_state (p_name => 'P1_COLHEAD'||i, p_value => r.instrument );
    i := i  + 1;
  END LOOP;                  
  sqlqry :=
...

Then change the column headers. Column1 is "Sample ID" and from column1 on the columns are pivoted. So header of column2 will be &P1_COLHEAD1., column3 will be &P1_COLHEAD2., etc...

Example (only set 2 column headers):