I'm using the Oracle SQL Developer application.

Version 2.1.0.63
Build MAIN-63.73

Via the application's grid view I am trying to paste a value into a field for a single record. Field type: VARCHAR2(4000 CHAR), although I've heard this problem can also happen with CLOB/BLOB type fields.

I paste, then hit the "commit" button at the top of the grid interface. Immediately I get the following error:

"Data got committed in another/same session, cannot update row."

I say, "huh?!" ... then proceed to try it again a few times mindlessly ignoring the fact that obviously something is wrong.

Google ended up being my saviour once again when I found: http://goo.gl/2vOL in the Oracle Forums.

The following is the solution that I gleaned from the forum link above (originally posted by 'gconley'):

1. Copy and edit the value to a safe place such as notepad or another sql developer worksheet, don't try to save in the table.
2. In a sql developer worksheet, make an update query to set the BLOB/CLOB field to NULL
3. Run the update query and commit
4. Refresh your table view, the BLOB/CLOB should be NULL now
5. Copy the value back in and commit

Share|

I've come across some thing a little *duh* and thought I'd put it up here to help me remember better in the future.

I had a small test application that allowed a user to upload an image, the that image was copied and resized, each resizing saved with a naming convention that would make it easy for the application to work with.

Example:

I upload my_image.jpg

The application creates:

• [large]_my_image.jpg
• [medium]_my_image.jpg
• [small]_my_image.jpg

Using brackets is not something I normally do in naming conventions and from now on will not do again. I really added it as it seemed to help visually when looking at my file system through Windows Explorer. Call it a brain fart, it just suited at the time.

The problem arose when I then went and tried running SQL with a LIKE statement in the WHERE clause.

Example:

I had not read the SQL Server LIKE statement details, therefore had not realised that using brackets in a LIKE statement actually tries to invoke wild card pattern matching on the part of the string between the brackets. Also, using an underscore calls for an 'any single character' pattern match.

Note: Microsoft SQL Server 2008 Books Online: LIKE - also checked back to the 2000 version and it does not seem anything has changed with this statement's syntax

So yeah, moral of the story, keep it simple.

There are lots of ways to make names unique and named within a convention. I do prefer using a UUID or a timestamp in the file name.

So I might end up with something like:

• small_{UUID}.jpg
• small_{ddmmyyhhmmss}.jpg

Then if you do need to run an SQL LIKE statement you could just use:

Anyway, just a note really for myself, but it's always nice if it help someone else.

Share|