When to use InlineValue attribute in Insert in Oracle EBS adapter?

  • Article

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="**https://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="**https://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.