oracle存储过程入参为xml,从Oracle存储过程返回XML

不幸的是,我的大部分数据库经验都是使用MSsql,它往往比Oracle更耐心.我想要做的是在tsql中相当简单,但是,pl / sql让我很头疼.

我有以下程序：

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文档：

当我执行查询本身,用字符串替换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.