SQL & PL/SQL

Hi,

Apologies for the SA I've written below - just wanted to explain the context around my problem.

I'm working on an application which replaces the Micro Focus COBOL file handler & allows data in COBOL VSAM files to be migrated to Oracle in such a way that the COBOL source code doesn't have to change.

The last problem we're having is in replicating COBOL's pessimistic locking paradigm in that a COBOL READ with LOCK statement will take a lock on the row that's read, and won't release the lock until an explicit UNLOCK statement is issued.

Each IO command from COBOL ends up calling an Oracle procedure, so what I'm after is ideas on how to create a procedure which when called will select one record & create a row lock, and hold that lock until another stored procedure call (to the unlock) procedure is made.

Using Select ... for update will work provided we're in a database transaction, however that causes other headaches because currently we're sharing one database connection across all files that are opened by a running COBOL program, & the files need to be locked & unlocked independently from each other.

If that's the only way to achieve the lock I can work with that by changing our application to create a connection for a file when it needs to lock a record, but does anyone have any out of the box ideas?

thanks, Darren.