2

Problem: To get results from my SP I need to recompile every time.

Purpose of the SP: to construct XML out of each row in a table.

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.

The outline of my code is..

  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, '<?xml version="1.0"?>');                 
      retXML := retXML || tmprowxml;    

      // code to update flag2 to 1          
  end loop;  

  DBMS_XMLGEN.closecontext(qcontext);

  COMMIT;

  return retXML;

Appreciate any pointers.

Rkka
  • 41
  • 4
  • is the procedure part of a package? as it sounds to me like you're probably getting an error the first time you run it saying session state cleared? or do you get no errors and a successful call? – DazzaL Dec 04 '12 at 11:15
  • the procedure is not part of any package. standalone. no errors and successful run as long as I compile it again and again. when i rerun the SP without the recompile I get no data. no errors reported by oracle. – Rkka Dec 04 '12 at 11:51
  • do you have a repeatable concise test case (ie show it failing with a test sqlplus run) that you can put in lieu of that sample code? as if you're not getting a session state error, it should work just fine. I tested your code on 10g and even with recompiling it works just fine (i used select * from user_objects where rownum <= 2 as my query) – DazzaL Dec 04 '12 at 11:59
  • I tried calling the SP from Toad (which is new to me) and I got the same result. No XML was returned. However when I ran the SQL in toad I got 1 row in return. Does this sound like "a repateable concise test case"? The oracle version I'm working on is 11gR2. – Rkka Dec 04 '12 at 13:08
  • i see. its probably that TOAD is disabling serveroutput after compilation (ie it may be doing an internal dbms_session.reset_package call unbenownst to you). after compiling, but before running the call again, toggle the serveroutput button off and on, then give it go :) – DazzaL Dec 04 '12 at 13:15
  • I have now tried this in both SQl Developer and Toad. What I found is that I have to recompile else the SP wont throw up any result when run for the second time and thereafter. A little debugging threw up unexplainable results (well, atleatst to me they were!). The dbms_xmlgen.getnumrowsprocessed(qContext) statement returns 0 in the second run of the SP. The first time it returns 1. There is one record in the table. Oh and by the way the dbms output is enabled and I've reduced the polling frequency to 2 seconds. – Rkka Dec 04 '12 at 13:30
  • The update to flag2=0 (in another session), so that the SP picks up the record, was not committed in my session. The SP probably runs in a session of its own and it couldn't see the update I made to the table field. Hence no result from second time onwards. But what I still cant explain is, when the table field update was not yet committed in my session how could the recompiling of the SP make it fetch the umcommitted update I made to the table field. Stumbling and fumbling through my early days in oracle! – Rkka Dec 04 '12 at 13:50
  • Do you use "deterministic" in your procedure create statement? – Sergey N Lukin Nov 27 '13 at 07:21

1 Answers1

0

Are you dropping and recreating the table between runs? A long shot, but this would invalidate the procedure.

Andrew Brennan
  • 333
  • 1
  • 9