我有以下程序：

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;

执行时,它应返回具有以下结构的XML文档：

<user userid="" companyid="" usertype="" status="" personid="">
    <firstname />
    <lastname />
    <email />
    <phone />
    <extension />
    <roles>
        <role />
    </roles>
    <watches>
        <watch id="" ticket="" />
    </watches>
</user>

当我执行查询本身,用字符串替换USERID参数并删除“into”子句时,查询运行正常并返回预期的结构.

但是,当该过程尝试执行查询时,将XMLELEMENT函数的结果传递给USERRECORD输出参数,我得到以下异常：

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

我很困惑,试图解决这个问题,不幸的是我的google-fu没有帮助.我发现了大量的Oracle sql | XML示例,但没有任何处理XML的过程返回.

注意：我知道存在使用DBMS方法检索XML的替代方法,我的理解是不推荐使用该功能而使用sql | XML.