We tried to deploy an application to a customer’s test system yesterday and needed to clean up the full database. We agreed before that they have to delete the schema and the user with all table etc. but they missed to notify their ORACLE adminstrators. We had only the permissions to work in, but not with the schema. So how to drop a few hundred tables?

DROP TABLE * CASCADE CONSTRAINTS!?

No. No such statement is possible. The solution of my workmate was a little funny:

1. Open ORACLE SQL Developer and connect to the database / schema.


2. Mark all tables to delete.


3. Choose “edit / copy full path” from the applications menu.


4. Paste the stuff into the script editor. You get something like “TABLE MySchema.MyTable@MyDb”.


5. Open Search/replace and replace “TABLE MySchema” with “DROP TABLE MySchema”.


6. Replace also “@MyDb” with “ CASCADE CONSTRAINTS;”.


7. Run the script.

Lesson for today: Do not update 341443 records at once.

java.sql.BatchUpdateException: I/O Error: Connection reset by peer: socket write error

Better split the update in parts.

This week we had a very confusing problem with a SQL subselect in a JDBC PreparedStatement for MS SQL Server. I looks like the following:

UPDATE table_a

SET intCol_A = (SELECT intCol_B

                FROM table_b

                WHERE strCol_B = ?)

WHERE strCol_A = ?

Very simple and easy to understand. It’s works fine until the first question mark was replaced by the null value. Boom!

strCol_B has no null value and should not have one. So logically the subselect should not select anything and return the null value. But it does not. It selected always the same value from strCol_B! (Not intCol_B!) A very weird behaviour. The string was a guid made of capitals and digits, so it could not be casted as an integer, which leads to an error.

How we fixed this? Instead of null we now use a string strCol_B can not contain: $NIL%. The subselect now does not select anything and returns the null value. All fine now.

Weird, isn’t it?