Commits with calls to Oracle stored procedures

I too agree with Phil.. To make your system fool proof, include your commit and rollbacks in the PL SQL in case if you use a stored procedure.

You can use cftransaction if you use couple of cfqueries in your code. As much as possible try to make a bunch of continuous CFqueries in your code to a stored procedure and avoid cftransaction.

Because different operating environments may make when you perform a COMMIT ambiguous, you should explicitly include your COMMITs within your PL/SQL procedures, and also any ROLLBACKs such as in the case of an EXCEPTION, etc. This give your SPs more independence, and allows you to exercise more control within the database, especially if your stored procedures are intended to be used by multiple front ends and not just from ColdFusion. Generally, I will perform my COMMIT at the end of any procedure that updates, inserts, or deletes any data, and I will also usually include a ROLLBACK (if appropriate) within my EXCEPTION, along with setting a return status value in an OUT parameter, to let my calling application know that something in the procedure raised an exception, etc. With newer versions of Oracle (after 8.1), you also have the option of using autonomous transactions to commit a change in isolation without committing everything in a session, but that is a subject for a different discussion.

Phil