I would like to synchronize access to a particular insert. Hence, if multiple applications execute this "one" insert, the inserts should happen one at a time. The reason behind synchronization is that there should only be ONE instance of this entity. If multiple applications try to insert the same entity,only one should succeed and others should fail. One option considered was to create a composite unique key, that would uniquely identify the entity and rely on unique constraint. For some reasons, the dba department rejected this idea. Other option that came to my mind was to create a stored proc for the insert and if the stored proc can obtain a global lock, then multiple applications invoking the same stored proc, though in their seperate database sessions, it is expected that the stored proc can obtain a global lock and hence serialize the inserts. My question is it possible to for a stored proc in oracle version 10/11, to obtain such a lock and any pointers to documentation would be helpful.
If you want the inserted entities to be unique, then in Oracle you don't need to serialise anything - a unique constraint is perfectly designed and suited for exactly this purpose. Oracle handles all the locking required to ensure that only one entity gets inserted.
I can't think of a reason why the dba department rejected the idea of a unique constraint, this is pretty basic - perhaps they rejected some other aspect of your proposed solution.
If you want to serialise access for some reason (and I can't think of a reason why you would), you could (a) get a lock on the whole table, which would serialise all DML on the table; or (b) get a user-named lock using DBMS_LOCK - which would only serialise the particular process(es) in which you get the lock. Both options have advantages and disadvantages.