Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
Comments
- Anonymous
February 21, 2013
Good information. Thanks.