title: Convert XmlType into CLOB in Oracle Stored Procedure
body: <p>In my procedure, my variable <code>l_outxml</code> 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:</p>
  
  <blockquote>
    <p>PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -</p>
  </blockquote>
  
  <p>Can anyone help me to do this?</p>
  
  <pre><code>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;
  </code></pre>