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.

Comments (1)

  1. Good information. Thanks.