Database.ca SQL Server Blog

My Blog

Monday, October 21 2013

Keywords: Oracle, Commit;, Application Frozen, Waiting, Locks

Oracle tips for SQL Server developers who are NEW to Oracle

Help! My Oracle Query or App looks frozen!
 
If you work in an enterprise that uses SQL Server as well as Oracle...then you will need to know that it is not always easy for SQL developers and those who come from the Microsoft stack background, to work with Oracle.
 
If you ever see or hear a QA tester or a developer wonder why their Oracle driven application suddenly doesn't respond or looks frozen, the first culprit is: COMMIT.

 

Oracle, Commit;, Application Frozen, Waiting, Locks

COMMIT is used to literally commit a transaction, meaning saving permanently the changes performed by PL/SQL commands within the transaction.

 

Oracle, Commit;, Application Frozen, Waiting, Locks

The table(s) affected could stay locked, until the COMMIT is issued, thus appearing frozen.
 
Since SQL Server does not have such behaviour, then this is something that is at first quite awkward to a SQL Server developer and especially to a Quality Assurance tester who is familiar with SQL Server but is new with Oracle.

 

QA Staff often open client tools such as SQL Developer to issue such commands as Delete/Update/Insert to test the application. Forgetting or ignoring COMMIT could then sometimes cause locks and frustrations.
 
 
"In SQL Developer when I view DATE or DATETIMESTAMP columns I only see the date value and not the time!"

 

Oracle, Commit;, Application Frozen, Waiting, Locks

Oracle could sometimes hide the time part of a date and time stamp. There are various ways to display or extract the time parts...one easy way is to use TO_CHAR...as you see in the example.
 
Another easy way is by modifying your SQL Developer's settings at Tools-->Preferences-->Database-->NLS Parameter,, update Date format field to DD-MON-RR HH:MI:SS value, and now you can see timestamp in all query results.

 

Major NULL vs Empty String difference between Oracle and SQL Server
 
In SQL Server, two single quotes ('') denote an empty string...which is something...it is not nothing...it's an empty string as it says...and NULL means just that...null...nothingness! Null and Empty String do not equal to each other...and comparisons between the two will fail. NULL is known to create havoc in aggregates, joins, group by clauses amongst many other places; and not handling the potential NULL values in a column or in a variable is usually a recipe for silent sporadic inconsistent bugs and wrong values being returned by queries...the type of error that QA staff hate the most...the ones that are hard to recreate every time.

 

Oracle, Commit;, Application Frozen, Waiting, Locks

In Oracle...there is no empty string...all empty strings will be intrinsically converted to NULL. if you set a variable equal to two single quotes, then it will hold NULL in it as a value.
 
Some SQL Server and MS Access developer have the custom of setting their string columns' default values to '' and not allow NULL on these columns. What SQL Server does when NULL value is being inserted into such field is to actually convert it to an empty string...and everyone is happy that the dreaded NULL is gone...If you attempt to do the same trick in Oracle...it will not work.
 
Basically beware that Oracle does not have the notion of Empty String.

 

PL/SQL is weird & too different from T-SQL! Yes it may seem to to a T-SQL developer...I've been working with SQL Server since it came out...but I will tell you honestly that PL/SQL is a much richer, thorough and mature language than T-SQL, at the time of writing at least...Microsoft is still going hard at it with T-SQL enhancements and it is just getting better and better with every release...albeit not as fast as I would like.
 
One example I could give is Triggers. In Oracle you have granular control over them by actually choosing to let the trigger fire for each individual row! Now that is an amazing power, and yes it does simplify the logic to a great extent and removes lots of potential bugs. I have seen a plethora of problems created in trigger scripts as developers have the tendency to forget that triggers are for handling a bunch and not for one single record only.

 

Merge in Oracle is not a real Merge! That is true. T-SQL (at the time of writing) performs a real merge:

  • Insert new record into destination, if not found in destination
  • Update destination, if different than source
  • Delete in source, if not matched by destination
  • Delete in destination, if not matched by source

Oracle only performs the first two commands...thus is not performing a real merger. By definition a merge must be able to fully synchronize the two parties...and Oracle is unable to do so.

 

Self-inserting self-looking self-updating self-deleting triggers are not allowed in Oracle! Well it's actually worse...the system does allow you to create them...only to throw a 1001 bugs at you later on...and in my experience the bugs were not consistent and were not thrown every time.
Basically the lesson here is do not let the code inside your trigger body to reference its own table in any shape or form.
 
When I translated my BOM/BOS model from T-SQL to PL/SQL...I had a few self-referring triggers in T-SQL (they would go only 2 levels deep at most)...that one-to-one direct translation did not work in Oracle. I was forced to completely re-design the schema...actually in Oracle's defence I must say that the newly redesigned model outperformed the SQL version and looked cleaner!
 
 
Why do my Oracle queries return nothing?!? They must! There are records in the table! The usual suspect here is: Case-Sensitivity. Oracle is by default case sensitive in its text comparisons. I have a separate blog on how to perform case-insensitive Oracle searches.
 
 
I can't debug my Oracle stored procedures like I'm used to in SQL Server! You can! I have prepared a step-by-step Oracle stored procedure debug from Visual Studio right here.

 

Written by Ramin Haghighat