Here are three ways to get information about a table’s columns in Oracle, including their data types.
Continue readingCategory: Oracle
How to Format Numbers with Commas in SQL
Most of the major RDBMSs have functions that enable us to format numbers with commas as either the group separator, or the decimal separator.
Some RDBMSs also output certain numeric data types with commas in the relevant place.
Below are examples of formatting numbers with commas in some of the more popular DBMSs.
Continue readingReturn Query Results as a Comma Separated List in Oracle
In Oracle, we can use the LISTAGG() function to convert our query results to a comma separated list.
So, instead of each value being output in a separate row, all values are output in a single row, separated by a comma (or some other delimiter of our choosing).
Continue readingFormat a Number as a Percentage in SQL
There are several ways to format a number as a percentage in SQL, depending on the DBMS being used.
Here are examples of adding a percent sign to a number in some of the most popular DBMSs.
Continue readingOracle DROP TABLE IF EXISTS Alternatives
The very useful DROP TABLE IF EXISTS syntax was finally introduced in Oracle Database – Oracle Database 23c to be precise. This syntax allows us to run a DROP TABLE statement without getting an error if the table doesn’t exist.
Earlier versions of Oracle don’t support the IF EXISTS clause. Therefore, if we want to avoid any nasty errors resulting from trying to drop a non-existent table, we need to do a bit of extra work.
Below are three options for dropping a table if it exists in Oracle.
Continue readingHow to Prettify JSON Formatted Query Results in SQLcl (Oracle)
If you use SQLcl to query Oracle Database, you might be aware of the SET SQLFORMAT json option, that makes query results come back as JSON documents.
But there’s also a json-formatted option, which returns the results in a more human readable format.
How to List All Stored Procedures in Oracle Database
There are several data dictionary views that we can use to return a list of stored procedures in Oracle Database.
Continue readingHow to Remove Leading Zeros From Dates in Oracle
In Oracle Database, you can use the fm (fill mode) format modifier to suppress any leading zeroes that might be applied to a date. This format modifier suppresses padding, which includes leading zeros and trailing blanks.
To use the fm format modifier, include it in your format model when specifying how dates should be formatted. For example when using the TO_CHAR() function to format dates, or in NLS parameters such as the NLS_DATE_FORMAT parameter when setting the date format for your current session.
SQL LPAD()
In SQL, LPAD()is a commonly used function that pads the left part of a string with a specified character. The function can be used on strings and numbers, although depending on the DBMS, numbers may have to be passed as a string before they can be padded.
DBMSs that have an LPAD() function include MySQL, MariaDB, PostgreSQL, and Oracle.
DBMSs that don’t have an LPAD() function include SQL Server and SQLite (although there are other ways to apply left padding in these DBMSs).
SQL RPAD()
In SQL, RPAD()is used to pad the right part of a string with a specified character. The function can be used on strings and numbers, although depending on the DBMS, numbers may have to be passed as a string before they can be padded.
DBMSs that have an RPAD() function include MySQL, MariaDB, PostgreSQL, and Oracle.
DBMSs that don’t have an RPAD() function include SQL Server and SQLite.