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.
A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.
You can use one or more RETURN statements in a stored procedure. The RETURN statement can be used anywhere after the declaration blocks within the SQL-procedure-body. To return multiple output values, parameters can be used instead. Parameter values must be set prior to the RETURN statement being executed.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With