Convert XmlType into CLOB in Oracle Stored Procedure
11,581
Use xmlelement(...).getClobVal()
in order to convert to CLOB.
Author by
Admin
Updated on June 13, 2022Comments
-
Admin almost 2 years
In my procedure, my variable
l_outxml
contains an XMLType value. I want to convert it into a CLOB type. How can I do this? If I change the datatype of the variable, it throws an error:PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -
Can anyone help me to do this?
CREATE OR REPLACE PROCEDURE "test_Dinesh_page_1" ( i_LRE_PeerGroup_ID IN out number ) as l_outxml xmltype := NULL; BEGIN with xmldata as ( select es.d_reportentity_id , es.d_reportperiod_id , es.avgoralhealthscore , es.ScorePercentile , es.CostPMPM , es.topClaimPercent , es.topClaimState , l.LKP_reportEntity_PeerGroup_id , l.d_PeerGroup_ID , rp.EndYear , rp.EndQuarter from f_ReportEntityScore es join LKP_ReportEntity_PeerGroup l on es.d_reportperiod_id = l.d_reportperiod_id and es.d_reportEntity_id = l.d_reportEntity_id join d_reportPeriod rp on es.d_reportperiod_id = rp.d_reportperiod_id where l.LKP_reportEntity_PeerGroup_id = i_LRE_PeerGroup_ID ) select xmlelement("ReportPage" , xmlelement("PageNumber",1) , (select xmlforest( cast(round(x.avgoralhealthscore,0) as int) as "DentalScore" , x.ScorePercentile as "DentalREINPercentile" ) from xmldata x where x.LKP_reportEntity_PeerGroup_id = i_LRE_PeerGroup_ID ) ) into l_outxml from dual; end;
-
Admin over 9 yearsIts working Wormbo, Wernfried. Thanks for your advice.
-
awilkinson about 7 years