Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

XML Return from an Oracle Stored Procedure

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.

like image 450
Tequila Jinx Avatar asked Mar 24 '10 22:03

Tequila Jinx


People also ask

Can Oracle stored procedure return value?

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.

Can we use RETURN statement in procedure?

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.


2 Answers

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.

like image 151
Gary Myers Avatar answered Sep 25 '22 05:09

Gary Myers


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.

like image 36
dacracot Avatar answered Sep 22 '22 05:09

dacracot