The fundamental problem is that you are SELECTing your new value from the PROJECTCODE table. This will, as you noted, not work if there are no rows in the table. Further, this will get more and more expensive as the table gets larger. Basically, you never want to select a constant from a real table - that is what DUAL is there for.
What you want to do is:
Of course, a unique index on PROJECTCODE_ITEM_CODE would have done the same thing, albeit a little more expensively.
PROJECTCODE_ITEM_CODE = :PROJECTCODE_ITEM_CODE
Of course, you will want to index that column anyway to avoid a tablescan, and since the value should only be there once, it should be unique as well. So, in the end, the sub-select was not functionally necessary, although it is, as I said, faster than the straight insert.
Click Here to Expand Forum to Full Width