Oracle

I’ve had a lot of feedback on my Oracle Update with Join post. The most common problem people have encountered with this approach is the dreaded “SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table”. Tom Kyte has a detailed explanation of this at Ask Tom. However, I thought I would take a moment to address my example scenario. There are actually a couple of ways to update based on data joined from multiple tables. The first is outlined in my first post. But, let’s...

So I was hoping this post would be a glorious review of my first attempt at working with WebParts and Oracle. Alas, the experience has not been so glorious. I am working on a "dashboard" of sorts as a demo for my boss. I spent a few hours on it this afternoon as a spike to see if the approach is feasible. I have to say that I believe it is, but there is one major kink I have to work out. ASP.Net WebParts uses the SqlPersonalizationProvider by default. Unfortunately, we are developing on Oracle. However,...

In the application I've been working on, we have the requirement to handle unique constraint errors gracefully. It wasn't really hard, I just had to check for OracleException.Code == 1. The trick was the testing. Testing a unique constraint error was not a problem. The issue is verifying that other exceptions are bubbled up properly. The basic exception handling code is like this: 1 protected bool HasUniqueConstraintError( Action databaseAction ) 2 { 3 try 4 { 5 databaseAction(); 6 return false;...

[Update Oct ‘09 In response to a number of comments on this post regarding problems with this approach, I’ve added a new blog entry with more details. Check it out.] Those with a SqlServer background will be familiar with the UPDATE .. FROM syntax. For example (totally made up) 1: update employee_bonus 2: set bonus = 0 3: from employee_bonus b 4: inner join employees e on b.employee_id = e.employee_id 5: where e.bonus_eligible = 'N' Those who transitioned from SqlServer to Oracle might find the absence...

I encountered a problem with Oracle's MERGE DML yesterday. What I was trying to do is use the "Upsert" feature of the merge, except with no distinct source. All of the searching I did for the MERGE showed examples of how to merge data from a source into a target. In my case, I thought the source and the target were the same. This is what I initially tried. It didn't work. It turns out that if the source (s) returns an empty set, then the merge is effectively a no-op 1: merge into MY_TARGET t 2: using...

Well, I have been playing with Linq, specifically with Oracle. First of all, the only way I could find to make this work is to use a third party software, OraDirect.Net by Core Lab. Their product is great. Perhaps that statement is biased by the fact that they are the only one's building a library to support Linq to Sql (as far as I know). There is certainly room for improvement. I've crashed VS a few times because I was trying to force OraDirect to do something it doesn't support. So, error handling...