title: iBatis.NET to insert record with Oracle stored procedure, return record ID
body: <p>I am attempting to insert a record in an Oracle table with a Function, which would be called through iBatis.NET. Function works as expected in Oracle when called directly.</p>
  
  <p>I have tried using <code>&lt;statement&gt;</code> and <code>&lt;insert&gt;</code> SqlMap but I can't get iBatis.NET to call the function, and Oracle doesn't support returning anything from Stored Procedure.</p>
  
  <p>I would need to pass properties of my object as parameters to a function/sproc and get back the ID of this new record.</p>
  
  <p>What would be a good combination of iBatis.NET call / SQLMap / Sproc or Function signature in Oracle?</p>
  
  <p>The documentation only has examples of in-line SQL and I can only use sprocs.</p>
  
  <p>Due to the number of properties in real objects, the hash-map and number of parameters is in the 30+.</p>
  
  <p>Ideally I would be able to do this (doesn't work):</p>
  
  <p><code>
  &lt;procedure id="InsertPerson" parameterClass="BOM.Person"&gt;
      TestDB.PERSON_PKG.InsertPerson(#Name#, #Age#)
  &lt;/procedure&gt;</code></p>
  
  <p>Domain object:</p>
  
  <pre><code>public class Person
  {
      int ID { get; set; }
      string Name { get; set; }
      decimal Age { get; set; }
  }
  </code></pre>
  
  <p>iBatis.NET call:</p>
  
  <pre><code>int personID = mapper.Insert("InsertPerson", person);
  </code></pre>
  
  <p>Oracle Stored Procedure:</p>
  
  <pre><code>   FUNCTION InsertPerson(
               p_Name IN Persons.Name%TYPE,
               p_Age IN Persons.Age%TYPE,
               ) RETURN NUMBER
     IS
               NEW_ID Persons.ID%TYPE;
     BEGIN
              SELECT Persons_SEQ.NEXTVAL INTO NEW_ID FROM DUAL; /* Get new ID*/
  
              INSERT INTO Persons(ID, Name, Age)
              SELECT NEW_ID, p_Name, p_Age from dual; /* Insert record */
              COMMIT;
  
              RETURN NEW_ID;
     END;
  </code></pre>