title: Oracle stored procedure needs recompilation to fetch table data
body: <p>Problem: To get results from my SP I need to recompile every time. </p>
  
  <p>Purpose of the SP: to construct XML out of each row in a table.</p>
  
  <p>Description: After running the SP for the first time post compilation the SP does not return any data unless recompiled even though there are valid rows in the table. </p>
  
  <p>The outline of my code is..</p>
  
  <pre><code>  qry := 'select * from mytable where flag2 = 0 and flag1 = 1';
    qContext := DBMS_XMLGEN.newContext(qry);
    DBMS_XMLGEN.setrowtag(qContext, 'RowFromDB');
    DBMS_XMLGEN.setrowsettag(qContext, 'RowTag');
    dbms_xmlgen.setmaxrows(qContext, 1);  
    retXML := '';
  
    loop
        tmprowxml := DBMS_XMLGEN.getxml(qContext);      
        exit when dbms_xmlgen.getnumrowsprocessed(qContext) = 0;      
        tmprowxml := replace(tmprowxml, '&lt;?xml version="1.0"?&gt;');                 
        retXML := retXML || tmprowxml;    
  
        // code to update flag2 to 1          
    end loop;  
  
    DBMS_XMLGEN.closecontext(qcontext);
  
    COMMIT;
  
    return retXML;
  </code></pre>
  
  <p>Appreciate any pointers. </p>