How to read in c# oracle XmlType from store procedure

Question

I'm trying to create a XML in stored procedure in this way:

PROCEDURE DeviceSearched(
   xml_out OUT XMLTYPE
)
IS
BEGIN

    SELECT 
      XMLELEMENT("Values", 
          XMLFOREST(de_brand)
    ) 
    INTO xml_out
    FROM 
      tbldevice de 
    ;  

END DeviceSearched;

And I trying to read xml_out in c# in this way:

...
OracleCommand command = new OracleCommand(name, conn);
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
...
command.Parameters.Add(new OracleParameter("xml_out", OracleDbType.XmlType, ParameterDirection.Output));

With this approach the issues are two:

Oracle exception: "ORA-01422: exact fetch returns more than requested number of rows"
If I modify the query to obtain one row, the procedure is ok (I think), but in c# I don't have any result.

What am I doing wrong?

Thanks in advance

any full source code sample about it?

Kiquenet
– Kiquenet

2013-08-28 15:43:11 +00:00
Commented Aug 28, 2013 at 15:43 — Kiquenet
– Kiquenet, Commented Aug 28, 2013 at 15:43

Mohsen Heydari · Accepted Answer · 2013-03-18 18:45:17Z

1

Running
SELECT XMLELEMENT("Values",XMLFOREST(de_brand)) FROM tbldevice de In plsql will NOT result a single value, So trying to fetch the result in to single variable (INTO xml_out) will result run time error ORA-01422
Using a store procedure to select data in Oracle is not apperciated, its a SQL Server approch, why not using a simple select
Examples here and here will show how ODP.Net works with XML
You may need to mix using ref corsur and XMLType to solve the matter

edited Mar 18, 2013 at 18:45

answered Mar 18, 2013 at 18:36

Mohsen Heydari

7,2924 gold badges33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to read in c# oracle XmlType from store procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related