title: Can not execute stored procedure with nHibernate
body: <p>Firstly, I'm sorry for asking this question AGAIN when there are several resources that (theoretically)
  explain it.  I've listed the references I used at the bottom of this question, hopefully they will 
  help someone else if nothing else.</p>
  
  <p>I am trying to execute a simple stored proc on an oracle 11 database.  My intent is to create
  a List{T} object from data returned via a SYS_REFCURSOR.
  I get this error message as soon as I try to to create an nhibernate session object:
  {"Errors in named queries: {GET_COLLATERAL}"}</p>
  
  <p>Here is my mapping.  The namespace, schema, assembly, query name are spelled correctly.
  The file is named GetCollateral.hbm.xml and is marked as an embedded resource.</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt;
   &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                     assembly="Poolman" namespace="Poolman.Entities" schema="poolman_own"&gt;
    &lt;sql-query name="GET_COLLATERAL" callable="true"&gt;
      &lt;return class="Poolman.Entities.IDNamePair"&gt;
        &lt;return-property name="ID" column="sort_order"&gt;&lt;/return-property&gt;
        &lt;return-property name="Name" column="collateral"&gt;&lt;/return-property&gt;
      &lt;/return&gt;
      {GET_COLLATERAL(?)}
    &lt;/sql-query&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>After quite a bit of troubleshooting I managed to get a session object to be created 
  by removeing the return element from the mapping as shown below.  Apparently there is something wrong with it
  but I don't know what.</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt;
  &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                     assembly="Poolman" namespace="Poolman.Entities" schema="poolman_own"&gt;
    &lt;sql-query name="GET_COLLATERAL" callable="true"&gt;
      {GET_COLLATERAL(?)}
    &lt;/sql-query&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>I don't expect to get a result set back with no return mapping, but using the mapping above allows nhibernate to create its session obect and to try to execute the query. However, nHibernate cannot get it's parameters right.  I get this error message:</p>
  
  <p>{"Expected positional parameter count: 1, actual parameters: [] [{GET_COLLATERAL(?)}]"}</p>
  
  <p>I've tried:</p>
  
  <p>CALL GET_COLLATERAL()</p>
  
  <p>BEGIN GET_COLLATERAL(); END;</p>
  
  <p>The above wrapped in CDATA</p>
  
  <p>Here is my stored proc:</p>
  
  <pre><code>create or replace
  PROCEDURE GET_COLLATERAL(p_cursor OUT SYS_REFCURSOR)
  IS
  
  BEGIN
    OPEN p_cursor for
    SELECT collateral, sort_order
    FROM
    (
      -- Long query omitted.  The query executes when pasted into a command window.
  
    )  ORDER BY sort_order ;
  
  END;
  </code></pre>
  
  <p>Here is my entity class.  This class does not map to any one table but I tried to create a mapping for it anyhow.</p>
  
  <pre><code>namespace Poolman.Entities
  {
      public class IDNamePair
      {
          public virtual int ID { get; set; }
          public virtual string Name { get; set; }
      }
  }
  
  
  
  &lt;?xml version="1.0" encoding="utf-8" ?&gt;
  &lt;hibernate-mapping assembly="Poolman"
                     namespace="Poolman.Entities"
                     schema="poolman_own"
                    xmlns="urn:nhibernate-mapping-2.2"&gt;
    &lt;class name="IDNamePair" table="x"&gt;
      &lt;id&gt;&lt;/id&gt;
      &lt;property name="ID" column="sort_order"/&gt;
      &lt;property name="Name" column="collateral"/&gt;
    &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Here is the code I'm using to call the query:</p>
  
  <pre><code>public List&lt;Entities.IDNamePair&gt; GetCollateral()
          {
              IQuery query = (IQuery)Session.GetNamedQuery("GET_COLLATERAL");
              List&lt;Entities.IDNamePair&gt; list = new List&lt;Entities.IDNamePair&gt;();
              System.Collections.IList result = query.List();
              list = result.OfType&lt;Entities.IDNamePair&gt;().ToList();
              return list;
          }
  </code></pre>
  
  <p>I really appreciate any help with this.  I'm stuck.
  Here are links to other resources I've found, none can help me however:
  Sorry stackoverflow only allows me to post two links:</p>
  
  <p><a href="https://stackoverflow.com/questions/635848/oracle-stored-procedures-sys-refcursor-and-nhibernate">Oracle stored procedures, SYS_REFCURSOR and NHibernate</a></p>
  
  <p><a href="http://www.techonthenet.com/oracle/questions/cursor1.php" rel="nofollow noreferrer">http://www.techonthenet.com/oracle/questions/cursor1.php</a></p>