在Oracle存储过程中将XmlType转换为CLOB [英] Convert XmlType into CLOB in Oracle Stored Procedure

问题描述

在我的过程中，变量l_outxml包含一个XMLType值.我想将其转换为CLOB类型.我怎样才能做到这一点?如果更改变量的数据类型，则会引发错误:

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:数据类型不一致:得到了预期的NUMBER-

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

有人可以帮我吗?

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;

推荐答案

使用xmlelement(...).getClobVal()可以转换为CLOB.

Use xmlelement(...).getClobVal() in order to convert to CLOB.

这篇关于在Oracle存储过程中将XmlType转换为CLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！