<p>We are bringing a new project in house and whereas previously all our work was on SQL Server the new product uses an oracle back end.</p>

<p>Can anyone advise any crib sheets or such like that gives an SQL Server person like me a rundown of what the major differences are - Would like to be able to get up and running as soon as possible.</p>

[{'answer_id': 39463, 'author': 'hamishmcn', 'author_id': 3590, 'author_profile': 'https://Stackoverflow.com/users/3590', 'pm_score': 1, 'selected': False, 'text': "<p>Watch out for the difference in the way the empty string is treated.<br>\n<code>INSERT INTO atable (a_varchar_column) VALUES ('');</code> </p>\n\n<p>is the same as  </p>\n\n<pre><code>INSERT INTO atable (a_varchar_column) VALUES (NULL);\n</code></pre>\n\n<p>I have no <code>sqlserver</code> experience, but I understand that it differentiates between the two</p>\n"}, {'answer_id': 39584, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': "<p>The main difference I noticed in moving from SQL Server to Oracle was that in Oracle you need to use cursors in the SELECT statements.\nAlso, temporary tables are used differently. In SQL Server you can create one in a procedure and then DROP it at the end, but in Oracle you're supposed to already have a temporary table created before the procedure is executed.</p>\n\n<p>I'd look at datatypes too since they're quite different.</p>\n"}, {'answer_id': 39610, 'author': 'hamishmcn', 'author_id': 3590, 'author_profile': 'https://Stackoverflow.com/users/3590', 'pm_score': 0, 'selected': False, 'text': '<p>If you need to you can create and drop temporary tables in procedures using the <a href="http://www.dbasupport.com/oracle/ora9i/execute_immediate.shtml" rel="nofollow noreferrer">Execute Immediate</a> command.</p>\n'}, {'answer_id': 41091, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 1, 'selected': False, 'text': "<p>@hamishmcn</p>\n\n<p>Generally that's a bad idea.. Temporary tables in oracle should just be created and left (unless its a once off/very rarely used). The contents of the temporary table is local to each session and truncated when the session is closed. There is little point in paying the cost of creating/dropping the temporary table, might even result in clashes if two processes try to create the table at the same time and unexpected commits from performing DDL.</p>\n"}, {'answer_id': 41100, 'author': 'andy47', 'author_id': 2661, 'author_profile': 'https://Stackoverflow.com/users/2661', 'pm_score': 3, 'selected': True, 'text': '<p>@hamishcmcn</p>\n\n<p>Your assertion that \'\' == Null is simply not true. In the relational world Null should only ever be read to mean "I don\'t know". The only result you will get from Oracle (and most other decent databases) when you compare a value to Null is \'False\'.</p>\n\n<p>Off the top of my head the major differences between SQL Server and Oracle are:</p>\n\n<ul>\n<li>Learn to love transactions, they are your friend - auto commit is not.</li>\n<li>Read consistency and the lack of blocking reads</li>\n<li>SQL Server Database == Oracle Schema</li>\n<li>PL/SQL is a lot more feature rich than T-SQL</li>\n<li>Learn the difference between an instance and a database in Oracle</li>\n<li>You can have more than one Oracle instance on a server</li>\n<li>No pointy clicky wizards (unless you really, really want them)</li>\n</ul>\n\n<p>Everyone else, please help me out and add more.</p>\n'}, {'answer_id': 41122, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 1, 'selected': False, 'text': '<p>What you have asked here is a huge topic, especially since you haven\'t really said what you are using the database for (eg, are you going to be going from TSQL -> PL/SQL or just changing the backend database your java application is connected to?)</p>\n\n<p>If you are serious about using your database choice to its potiential, then I suggest you dig a bit deeper and read something like <a href="https://rads.stackoverflow.com/amzn/click/com/1590595300" rel="nofollow noreferrer" rel="nofollow noreferrer">Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions</a> by Tom Kyte.</p>\n'}, {'answer_id': 43876, 'author': 'John Smithers', 'author_id': 1069, 'author_profile': 'https://Stackoverflow.com/users/1069', 'pm_score': 2, 'selected': False, 'text': '<p>String concatenation:<br>\nOracle: || or concat()<br>\nSql Server: +  </p>\n\n<p>These links could be interesting:<br>\n<a href="http://www.dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm" rel="nofollow noreferrer">http://www.dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm</a><br>\n<a href="http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm" rel="nofollow noreferrer">http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm</a> (old one: Ora9 vs Sql 2000)</p>\n'}, {'answer_id': 47730, 'author': 'hamishmcn', 'author_id': 3590, 'author_profile': 'https://Stackoverflow.com/users/3590', 'pm_score': 0, 'selected': False, 'text': '<p>to andy47, I did not mean that you can use the empty string in a comparison, but oracle treats it like null if you use it in an insert. \nRe-read my entry, then try the following SQL:</p>\n\n<pre><code>CREATE TABLE atable (acol VARCHAR(10));\nINsERT INTO atable VALUES( \'\' );\nSELECT * FROM atable WHERE acol IS NULL;\n</code></pre>\n\n<p>And to avoid a "yes it is, no it isn\'t" situation, <a href="http://www.adp-gmbh.ch/ora/misc/null.html" rel="nofollow noreferrer">here is an external link</a> </p>\n'}]
['https://Stackoverflow.com/questions/39438', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/770/']