Oracle – Page 5

Oracle ISNULL() Equivalent

May 3, 2022 by Ian

Most major DBMSs provide us with a function for replacing null values with another value.

But the name of the function tends to differ across DBMSs. For example, SQL Server has an ISNULL() function, while others (such as MySQL, MariaDB, and SQLite) have an IFNULL() function for doing the same thing.

However, to confuse matters, MySQL and MariaDB each have an ISNULL() function that works differently to SQL Server’s function of the same name (MySQL and MariaDB’s implementation only accept a single argument, and return 1 if its null and 0 if it’s not).

Convert a Unix Timestamp to a Date Value in Oracle

April 26, 2022 by Ian

In Oracle Database, we can use the following technique to return a date from a Unix timestamp value.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC).

2 Ways to Convert to Uppercase in Oracle

April 23, 2022 by Ian

Below are two functions that convert lowercase characters to their uppercase equivalent in Oracle Database.

Both functions work in a similar way, but with a minor difference.

How to Return the Unix Timestamp in Oracle

April 21, 2022 by Ian

Here’s an option for returning the Unix timestamp when using Oracle Database.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC).

2 Ways to Get the Number of Days in a Month in Oracle

April 19, 2022 by Ian

Here are a couple of ways to return the number of days in a given month in Oracle Database. This could be the number of days in the current month, or the number of days in a month based on a specified date.

How to Get the Last Day of the Month in Oracle

April 14, 2022 by Ian

In Oracle Database we can use the LAST_DAY() function to return the last day of a given month. This can be the last day of the current month or the last day of month based on a specified date.

2 Ways to Delete Duplicate Rows in Oracle

March 20, 2022 by Ian

The following options can be used to delete duplicate rows in Oracle Database.

These examples delete duplicate rows but keep one. So if there are three identical rows for example, it deletes two of them and keeps one. This is often referred to as de-duping the table.

2 Ways to Return Rows that Don’t Contain Numeric Values in Oracle

March 14, 2022 by Ian

The following examples return only those rows that don’t contain any numeric digits in a given column in Oracle Database.

11 Ways to Find Duplicate Rows that have a Primary Key in Oracle

July 23, 2023March 3, 2022 by Ian

Here are eleven options for returning duplicate rows in Oracle Database when those rows have a primary key or some other unique identifier column and you want to ignore it.

Fix Error “ORA-01789: query block has incorrect number of result columns”

February 26, 2022 by Ian

If you’re getting the error “ORA-01789: query block has incorrect number of result columns” in Oracle Database, then it’s probably because you’re trying to use an operator such as UNION, INTERSECT, or EXCEPT to run a compound query, but the SELECT statements on either side of the operator return a different number of columns.

To fix this, simply ensure that both queries return the same number of columns.