XML Return from an Oracle Stored Procedure

Question

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.

Solution 1

Your code includes the following :

u.USERID = USERID;

While you intend the bare USERID to be the procedure's parameter, Oracle actually gives preference to the USERID that is the column in the table. In effect it interprets it as

u.USERID = u.USERID;

You can use

u.USERID = USPX_GetUserbyID.USERID;

but it is good practice to use a prefix for PL/SQL variables to avoid confusion. I tend towards v_ for variables and i_, o_, io_ for input, output and input/output parameters.

Solution 2

Your error has nothing to do with XML. In PL/SQL if you have a query which returns multiple rows, you must loop through the rows with a cursor. You have used the INTO keyword which can handle only a single row (or XML in your case) result.