Raju, I may not be able to give an exact answer (never faced this exact situation) but thought would express my opinion/understanding of the situation.
A single entity bean (either BMP or CMP) instance essentially represents one single row (may be that of a single table incase of a CMP or of a join of multiple tables incase of a BMP) w.r.to the database.
When you are saying "we have a stored procedure to be called in the mean while in the business logic of the CMP", I am slightly confused. What we essentially do on a CMP entity bean is that we get and set values after obtaining a remote interface bandle of it. Is it that - in one of the setter method, you are required to call a stored procedure ?
If you donot want to disturb your existing CMP, my suggession would be that you define a database trigger on that table for BEFORE/AFTER UPDATE and 'call' that stored procedure. So when you 'set' a value on that CMP bean, that correspondig 'update' would trigger/call that stored procedure. For the transaction mgmt, if the stored procedure fails it throws an exception (assuming it would) and sure the trigger would fail and therefore the 'update' should fail and throw an database trigger failure exception. Not sure if the remote interface would throw us that exception (I assume it would).
I am not sure how to achieve this with a BMP but without acquiring a seperate database connection. Can we define a new Entity Bean to represent a 'call to store procedure' ? And then obtain its remote interface which can call the stored procedure in our entity bean setter method ?