In typical Oracle EBS scenarios, it is a common requirement to insert computed values into a table. For example, one might want to populate the key column using a sequence, or insert something like SYSDATE into a date column. However, if the adapter makes it mandatory to provide constant values to be inserted, this becomes impossible to achieve in a single operation.

We came up with a simple solution. Every element in an InsertRecord takes an optional InlineValue attribute, which if populated, is used as-is in the insert statement. Let me illustrate with an example.

Consider the following insert operation XML snippet:

<InsertRecord xmlns="http://schemas.microsoft.com/OracleEBS/2008/05/TableViewRecord/SCOTT/EMP">

  <EMPNO>1024</EMPNO>

  <ENAME>SCOTT</ENAME>

  <MGR>512</MGR>

  <HIREDATE>2006-05-31T00:00:00</HIREDATE>

  <SAL>30000</SAL>

  <COMM>33</COMM>

  <DEPTNO>101</DEPTNO>

  </InsertRecord>


This results in an insert statement that is equivalent to:

INSERT INTO SCOTT.EMP VALUES (:P0, :P1, :P2, …);

Where P0, P1, P2 etc are OracleParameter instances bound to the OracleCommand. Now consider the following XML snippet:

<InsertRecord xmlns="http://schemas.microsoft.com/OracleEBS/2008/05/TableViewRecord/SCOTT/EMP">

  <EMPNO InlineValue="SCOTT.EMP_SEQ.NEXTVAL"/>

  <ENAME>SCOTT</ENAME>

  <MGR>512</MGR>

  <HIREDATE InlineValue="SYSDATE"/>

  <SAL InlineValue="SOME_API_TO_GET_SAL()"/>

  <COMM>33</COMM>

  <DEPTNO>101</DEPTNO>

  </InsertRecord>


This results in an insert statement that looks like this:

INSERT INTO SCOTT.EMP VALUES(SCOTT.EMP_SEQ.NEXTVAL, :P1, :P2, SYSDATE, SOME_API_TO_GET_SAL(), :P3, …);

Now, it is tempting to provide data values in the InlineValue attribute, but we recommend against that. <ENAME InlineValue="SCOTT"/> would result in an error as SCOTT is not a valid identifier. You’d have to use single quotes around the name as adapter merely puts this string in the insert statement without any sanity check on the value. Therefore, as a thumb rule, avoid using InlineValue attribute for constant values.


Update: If you want to use a select query in an inline value, you should enclose it in brackets. For example <ENAME InlineValue="(SELECT NAME FROM NEW_EMPLOYEES WHERE ID=123)"/> of course, you'll have to make sure that the select query returns only one field from a single row.