When to use InlineValue attribute in Insert in Oracle EBS adapter?
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.
