Whenever I type in a date strings in Oracle, I convert the strings to Date data type in my code as well as my queries without relying on the session setting for date format. I will explain why. Session date format setting: Session date format settings govern how date strings should be converted into dates (the … Continue reading Oracle – Easy Date Strings (and NOT)
Commit or rollback a SELECT, really? Occasionally, you would be working with your favorite Oracle tool and when you are ready to close the tool, it will prompt you to either commit or rollback your work. “What work?”, you wonder. The problem is, you have only done SELECT’s. Why is it prompting you to commit … Continue reading Oracle – Why Is Toad Asking Me To Commit/Rollback A SELECT With No Transaction?
Recently a situation came up in PL/SQL code where the format specifier for date was misplaced. Oracle did not complain because it can implicitly convert strings to date. It may not even fail at run-time under most circumstances (always recommend using an explicit format specification though). This is the SQL ..but the Developer meant this: … Continue reading Oracle – Find Hard-coded Dates In PL/SQL Code Using Regular Expressions
Sometimes we carry knowledge around that can hurt when doing similar things especially when dealing with the different programming languages and constructs. Today, let us see how Oracle’s ROWNUM is different from (as in, almost unrelated) SQL Server’s TOP. SQL Server - TOP: Basics Let us take the TOP function in SQL Server. It is … Continue reading Oracle ROWNUM vs. SQL Server TOP – Differences & How To Do Pagination In Both
NULL's will be eliminated! When doing a count() operation, it helps to understand what is counted and what is not. Count can be done in multiple ways COUNT(*) COUNT(1) COUNT([Column_Name]) COUNT(DISTNCT [Column_Name]) With and without GROUP BY While it is straight-forward to get a count, here are a couple of things to note NULL values … Continue reading Oracle & SQL Server: COUNT(*) vs. COUNT(1) vs. COUNT(ColumnName) – Differences