Is there any way to have the JBoss connection pool reconnect to Oracle when connections go bad?

Whilst you can use the old “select 1 from dual” trick, the downside with this is that it issues an extra query each and every time you borrow a connection from the pool. For high volumes, this is wasteful. JBoss provides a special connection validator which should be used for Oracle: <valid-connection-checker-class-name> org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker </valid-connection-checker-class-name> This … Read more

In SQL Server, how can I lock a single row in a way similar to Oracle’s “SELECT FOR UPDATE WAIT”?

You’re probably looking forwith (updlock, holdlock). This will make a select grab an exclusive lock, which is required for updates, instead of a shared lock. The holdlock hint tells SQL Server to keep the lock until the transaction ends. FROM TABLE_ITEM with (updlock, holdlock)

Inserting into Oracle and retrieving the generated sequence ID

Expanding a bit on the answers from @Guru and @Ronnis, you can hide the sequence and make it look more like an auto-increment using a trigger, and have a procedure that does the insert for you and returns the generated ID as an out parameter. create table batch(batchid number, batchname varchar2(30), batchtype char(1), source char(1), … Read more

“column not allowed here” error in INSERT statement

You’re missing quotes around the first value, it should be INSERT INTO LOCATION VALUES(‘PQ95VM’, ‘HAPPY_STREET’, ‘FRANCE’); Incidentally, you’d be well-advised to specify the column names explicitly in the INSERT, for reasons of readability, maintainability and robustness, i.e. INSERT INTO LOCATION (POSTCODE, STREET_NAME, CITY) VALUES (‘PQ95VM’, ‘HAPPY_STREET’, ‘FRANCE’);