From Wikipedia (which has great and detailed examples for this): A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads. and A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection … Read more
You can change this in preferences: From Oracle SQL Developer’s menu go to: Tools > Preferences. From the Preferences dialog, select Database > NLS from the left panel. From the list of NLS parameters, enter DD-MON-RR HH24:MI:SS into the Date Format field. Save and close the dialog, done! Here is a screenshot:
COALESCE is more modern function that is a part of ANSI-92 standard. NVL is Oracle specific, it was introduced in 80‘s before there were any standards. In case of two values, they are synonyms. However, they are implemented differently. NVL always evaluates both arguments, while COALESCE usually stops evaluation whenever it finds the first non-NULL … Read more
I believe the answer is that Oracle is very, very old. Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/VARCHAR2 columns were NULL and that there was only one sense of NULL (there are relational theorists that would differentiate between data that has … Read more
Your table is already locked by some query. For example, you may have executed “select for update” and have not yet committed/rollbacked and fired another select query. Do a commit/rollback before executing your query.
It’s a sort of dummy table with a single record used for selecting when you’re not actually interested in the data, but instead want the results of some system function in a select statement: e.g. select sysdate from dual; See http://www.adp-gmbh.ch/ora/misc/dual.html
There are 3 main ways to perform a case-insensitive search in Oracle without using full-text indexes. Ultimately what method you choose is dependent on your individual circumstances; the main thing to remember is that to improve performance you must index correctly for case-insensitive searching. 1. Case your column and your string identically. You can force … Read more
Version I am using Update 5th May 2012 Jeff Smith has blogged showing, what I believe is the superior method to get CSV output from SQL Developer. Jeff’s method is shown as Method 1 below: Method 1 Add the comment /*csv*/ to your SQL query and run the query as a script (using F5 or … Read more
SQL Developer can start a new tab every time you select a database object to view; but, you will have to close the tab manually. Set this in: Tools->Preferences->Database->ObjectViewer->Automatically Freeze Object Viewer Windows
SELECT ( SELECT COUNT(*) FROM tab1 ) AS count1, ( SELECT COUNT(*) FROM tab2 ) AS count2 FROM dual