PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -



I want to generate XML from some of my tables and stored as a CLOB file. When i try to run my query, it throws some errors. But I can't able to find where my mistake is. Anyone help me to solve my problem.


This is my Query:



CREATE OR REPLACE
PROCEDURE "test_Dinesh_page_1"
(
i_LRE_PeerGroup_ID IN number
)

as
l_outxml clob;

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"
) -- END xmlforest
from xmldata x
where x.LKP_reportEntity_PeerGroup_id = i_LRE_PeerGroup_ID
)
)
into l_outxml
from dual;
dbms_xslprocessor.clob2file(l_outxml, 'GLOBALSTRATEGY', 'GlobalStrategies.xml', nls_charset_id('UTF8'));
end;


The error code is:



PL/SQL: SQL Statement ignored
PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -


Thanks in advice.


No comments:

Post a Comment