SQL & PL/SQL

Hi, Good Afternoon.

Oracle Environment Is Oracle 12c

I am stuck at coming up with a Logic, where I have to Create Dynamic Table, with Dynamic Columns names and The table name is from a Query data field.I Tried with Pivot Query but with no luck,

I keep getting "ORA-56901: non-constant expression is not allowed for pivot|unpivot values"

I am Copy pasting my query which involves XMLDB and XQUERY, I am using the result Set of the XML Query result in to my Pivot Query, but I am not Able to get the required result.

SELECT * FROM (

SELECT   1 AS  study_id

       , sd.*

       , 1 AS print_id

       , current_Timestamp AS crtd_dt

       , current_timestamp AS updt_dt

       , 'robot_user'    AS crtd_by

       , 'robot_user'    AS updtd_dt

  FROM   odm_file odf,

         XMLTABLE(XMLNAMESPACES (DEFAULT 'http://www.cdisc.org/ns/odm/v1.3')

                 , 'for $h  in /ODM/ClinicalData

                        ,$i in $h/SubjectData/StudyEventData/FormData

                        ,$j in $h/$i/ItemGroupData

                        ,$k in $h/$i/$j/ItemData                                                                                    

                     return element r {$h,$i,$j,$k}'                    

         PASSING  odf.xml

         COLUMNS  STDY_NM              VARCHAR2(30)   PATH  'ClinicalData/@StudyOID'                                      

                  --,VISIT_RPT_KEY     VARCHAR2(20)          

                   ,FRM_OID            VARCHAR2(30)   PATH  'FormData/@FormOID'       

                   ,FRM_RPT_KEY        VARCHAR2(30)   PATH  'FormData/@FormRepeatKey'

                   ,ITM_GRP_OID        VARCHAR2(30)   PATH  'ItemGroupData/@ItemGroupOID'                 

                   ,ITM_GRP_RPT_KEY    VARCHAR2(30)   PATH  'ItemGroupData/@ItemRepeatKey'

                   ,ITM_OID            VARCHAR2(30)   PATH  'ItemData/@ItemOID'

                   ,ITM_VAL            VARCHAR2(30)   PATH  'ItemData/@Value'

                   ,IS_ITM_NULL        VARCHAR2(30)   PATH  'ItemData/@IsNull'

                   ,TRNSCTN_TYP        VARCHAR2(30)   PATH  'ItemData/@TransactionType'                 

                 )sd

WHERE  odf.odm_file_id = 2

)

--SELECT * FROM PIVOT_DATA

   PIVOT( COUNT(ITM_GRP_OID)

            FOR  ITM_GRP_OID

               IN(ITM_OID,OTM_VAL)

         );

I am Copy pasting the result Data of the inner query, the query starts from the Bold parenthesis and italicized

My Mockup Data Should look like

THe Form Name from the Result set becomes the Table Name that has to be Created, here HFR6HDFS