title: XMLType out parameter and ODP.NET
body: <p>I try using XMLType column in my table and ODP.NET.</p>
  
  <p>any full sample source code using ODP.NET and XMLTYPE out parameter?</p>
  
  <p>My procedure:</p>
  
  <pre><code>PROCEDURE GET_DATA_DEPLOY(pPROYECT IN VARCHAR2,
                    pETIQ IN VARCHAR2 DEFAULT NULL,
                    pMOMENT OUT TIMESTAMP,
                    pDATA OUT XMLTYPE)
  </code></pre>
  
  <p>And for testing: function and procedure.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>View in <a href="https://entlib.codeplex.com/discussions/454750" rel="nofollow">https://entlib.codeplex.com/discussions/454750</a></p>
  
  <p>I get "connection is closed" errors</p>
  
  <p>Only, I've found this sample, but without OUT parameters.</p>
  
  <pre><code>/*
  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 = "&lt;a&gt;b&lt;/a&gt;";
                  cmd.ExecuteNonQuery();
                  Console.WriteLine(((OracleXmlType)cmd.Parameters["return"].Value).Value);
              }
          }
      }
  }
  </code></pre>
  
  <p><strong>Update:</strong></p>
  
  <p>Alex Keh from Oracle, The below works for me.</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Any version source code using Enterprise Library? </p>
  
  <p>References: <a href="https://entlib.codeplex.com/discussions/454750" rel="nofollow">https://entlib.codeplex.com/discussions/454750</a></p>