insert and not exists

[chrisrlong]

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:

INSERT INTO
   PROJECTCODE
      (
      PROJECTCODE_ITEM_CODE
      )
SELECT
   :PROJECTCODE_ITEM_CODE
FROM
   DUAL 
WHERE
   NOT EXISTS(
      SELECT
         1
      FROM
         PROJECTCODE
      WHERE
         PROJECTCODE_ITEM_CODE = :PROJECTCODE_ITEM_CODE
      )   ;

Of course, a unique index on PROJECTCODE_ITEM_CODE would have done the same thing, albeit a little more expensively.
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.

Enjoy,

- Chris

[sonaliak]

Thanks, it works great