title: XML Return from an Oracle Stored Procedure
body: <p>Unfortunately most of my DB experience has been with MSSQL which tends to hold your hand a lot more than Oracle. What I'm trying to do is fairly trivial in tSQL, however, pl/sql is giving me a headache.</p>
  
  <p>I have the following procedure:</p>
  
  <pre><code>    CREATE OR REPLACE PROCEDURE USPX_GetUserbyID (USERID USERS.USERID%TYPE, USERRECORD OUT XMLTYPE) AS
  BEGIN
  
      SELECT XMLELEMENT("user"
          , XMLATTRIBUTES(u.USERID AS "userid", u.companyid as "companyid", u.usertype as "usertype", u.status as "status", u.personid as "personid")
          , XMLFOREST(  p.FIRSTNAME AS "firstname"
                      , p.LASTNAME AS "lastname"
                      , p.EMAIL AS "email"
                      , p.PHONE AS "phone"
                      , p.PHONEEXTENSION AS "extension")
          ,  XMLELEMENT("roles",
                  (SELECT XMLAGG(XMLELEMENT("role", r.ROLETYPE))
                      FROM USER_ROLES r
                      WHERE r.USERID = USERID
                          AND r.ISACTIVE = 1
                  )
              )
          , XMLELEMENT("watches",
                  (SELECT XMLAGG(
                      XMLELEMENT("watch",
                          XMLATTRIBUTES(w.WATCHID AS "id", w.TICKETID AS "ticket")
                      )
                  )
                  FROM USER_WATCHES w
                  WHERE w.USERID = USERID
                  AND w.ISACTIVE = 1
                  )
              )
          ) AS "RESULT"
      INTO USERRECORD
      FROM USERS u 
      LEFT JOIN PEOPLE p ON p.PERSONID = u.PERSONID
      WHERE u.USERID = USERID;
      END USPX_GetUserbyID;
  </code></pre>
  
  <p>When executed, it should return an XML document with the following structure:</p>
  
  <pre><code>&lt;user userid="" companyid="" usertype="" status="" personid=""&gt;
      &lt;firstname /&gt;
      &lt;lastname /&gt;
      &lt;email /&gt;
      &lt;phone /&gt;
      &lt;extension /&gt;
      &lt;roles&gt;
          &lt;role /&gt;
      &lt;/roles&gt;
      &lt;watches&gt;
          &lt;watch id="" ticket="" /&gt;
      &lt;/watches&gt;
  &lt;/user&gt;
  </code></pre>
  
  <p>When I execute the query itself, replacing the USERID parameter with a string and removing the "into" clause, the query runs fine and returns the expected structure.</p>
  
  <p>However, when the procedure attempts to execute the query, passing the results of the XMLELEMENT function into the USERRECORD output parameter, I get the following exception:</p>
  
  <pre><code>Error report: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at "USPX_GETUSERBYID", line 4 ORA-06512: at line 3
  01422. 00000 -  "exact fetch returns more than requested number of rows"
  *Cause:    The number specified in exact fetch is less than the rows returned.
  *Action:   Rewrite the query or change number of rows requested
  </code></pre>
  
  <p>I'm baffled trying to nail this down, and unfortunately my google-fu hasn't helped. I've found plenty of Oracle SQL|XML examples, but none that deal with XML returns from a procedure.</p>
  
  <p><strong>Note:</strong> I know that an alternate method of retrieving XML using DBMS methods exists, however, it's my understanding that that functionality is deprecated in favor of SQL|XML.</p>