XmlType ODP.NET using Enterprise Library

Topics: Data Access Application Block
Aug 28, 2013 at 1:22 PM
Edited Aug 29, 2013 at 8:54 PM
Hi,

I want try use ODP.NET and Enterprise Library for get XmlType value.

I have this procedure
PROCEDURE GET_DATA_DEPLOY(pPROYECT IN VARCHAR2,
                  pDATE OUT TIMESTAMP,
                  pDATA OUT XMLTYPE) IS
And for testing: function and procedure.
FUNCTION FRKDATA_GET_XMLTYPE(num IN NUMBER, xml1 IN XMLTYPE) RETURN XMLTYPE IS
    xmlout XMLTYPE;
  BEGIN
    SELECT XMLTYPE_COL 
    INTO xmlout
    FROM POC_TEST_XML_TABLE
    WHERE NUM_COL = num;
    RETURN xmlout;
  END FRKDATA_GET_XMLTYPE;


PROCEDURE FRKDATA_TESTXML(qry in VARCHAR2, rslt OUT XMLTYPE) AS
BEGIN
 rslt := dbms_xmlgen.getxmltype(qry);
END FRKDATA_TESTXML;
I want try get pDATA value. I get errors about connection is closed.

Any full code sample using XmlType + Odp.net + Ent Lib ??

Simple sample in Oracle:
/*
create or replace function xmlproc(v1 in xmltype) return xmltype is
begin
 return v1;
end;
/
*/

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

class Program
{
    static void Main(string[] args)
    {
        using (OracleConnection con = new OracleConnection())
        {
            con.ConnectionString = "user id=scott;password=tiger;data source=orcl";
            con.Open();
            using (OracleCommand cmd = new OracleCommand("",con))
            {
                cmd.CommandText = "xmlproc";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new OracleParameter("return", OracleDbType.XmlType)).Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(new OracleParameter("v1", OracleDbType.XmlType)).Direction = ParameterDirection.Input;
                cmd.Parameters["v1"].Value = "<a>b</a>";
                cmd.ExecuteNonQuery();
                Console.WriteLine(((OracleXmlType)cmd.Parameters["return"].Value).Value);
            }
        }
    }
}
Update:

for EntLib, when I call ExecuteNonQuery, after the connection is closed and get error in GetParameterValue
            Trace.WriteLine("Conexion State1: " + (cm.Connection == null ? "Nulo" : cm.Connection.State.ToString()));
            int rowsAffected = int.MinValue;
             rowsAffected = db.ExecuteNonQuery(cm);
            Trace.WriteLine("Conexion State2: " + (cm.Connection== null ? "Nulo" : cm.Connection.State.ToString()));
            return rowsAffected;

            ...
            object value = Command.GetParameterValue(paramName);
Alex Keh from Oracle, The below works for me.
PROCEDURE "XMLPROC" (  "PARAM1" OUT XMLTYPE) IS
BEGIN
  PARAM1 := dbms_xmlgen.getxmltype('SELECT FIRST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID=100');
END;
OracleConnection con = new OracleConnection();
con.ConnectionString = conString;
con.Open();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "xmlproc";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new OracleParameter("xmlout", OracleDbType.XmlType)).Direction = ParameterDirection.Output;
            cmd.ExecuteNonQuery();
            Console.WriteLine(((OracleXmlType)cmd.Parameters["xmlout"].Value).Value);
Any version source code using Enterprise Library ?
Editor
Aug 30, 2013 at 4:22 PM
Have you tried replicating the exact ADO logic using the Enterprise Library database methods? For example, in the working code you are adding an output parameter while in the posted Enterprise Library code no parameters are added. As a fallback, you should be able to use DbCommand objects directly just as you would using ADO.NET.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Sep 4, 2013 at 1:34 PM
Any full source sample code using ADO logic and the Enterprise Library database methods? Then, I'll try to translate to my Oracle issue.