XML Return from an Oracle Stored Procedure
- by Tequila Jinx
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.
I have the following procedure:
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;
When executed, it should return an XML document with the following structure:
<user userid="" companyid="" usertype="" status="" personid="">
<firstname />
<lastname />
<email />
<phone />
<extension />
<roles>
<role />
</roles>
<watches>
<watch id="" ticket="" />
</watches>
</user>
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.
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:
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
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.
Note: 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.
