Search code examples
databaseoracle-databaseoracle11gxml-parsingrdbms

Check Oracle Query Execution Count

I'm using this loop to parse XML. 

        FOR elementdetails in     
                (SELECT ExtractValue(Value(elementdetails),'/details/Id/text()') AS Id,
                        Extract(Value(elementdetails),'/details/GrpId') AS Idgrp,
                        ExtractValue(Value(elementdetails),'/details/SrCd/text()') AS PId  
           FROM TABLE(XMLSequence(Extract(Block,'/Records/Body/details)))
                       elementdetails)

Format of xml :

    <Records>
      <Body>
        <details>
          <Id>1</Id>
          <GrpId>15</GrpId>
          <SrCd>2</SrCd>
    </details>
    <details>
          <Id>2</Id>
          <GrpId>5</GrpId>
          <SrCd>6</SrCd>
    </details>
         </Body>
    </Records>

After parsing I'm passing the element details values like this: elementdetails.Id to other procedure. So, in above example loop will run twice.
My question is: whenever loop runs, it does the select query to parse the whole xml or only the first details tag and then the second?

Solution

  • In FOR loop query executed once.
    Then Oracle iterate using cursor.
    (it execute loop body for each row of query result).