Here are two ways you can use SQLcl or SQL*Plus to check your Oracle Database compatibility level.
Continue readingCategory: Oracle
List All Functions in Oracle Database
Here are several ways to get a list of functions in Oracle Database.
Continue readingAdd the Ordinal Indicator to a Date in Oracle
In Oracle Database, we can use the TH format element to add the ordinal number suffix to the result of a TO_CHAR() operation when formatting dates.
For example, instead of outputting 10 Feb we could output 10th Feb. Or, instead of outputting 21 century, we can output 21st century. The same applies for spelled date components. For example, instead of Twenty One, we can output Twenty First.
Find Out Which Currency Symbol your Session Uses in Oracle
In Oracle Database, the following NLS parameters can be used to determine how currencies are displayed for the current session:
NLS_CURRENCYspecifies the string to use as the local currency symbol for theLnumber format element.NLS_ISO_CURRENCYdetermines what to use for theCformat element.NLS_DUAL_CURRENCYspecifies what to use for theUformat element.
The default value for these is determined by the NLS_TERRITORY parameter.
The L, C, and U number format elements can be used in functions like TO_CHAR() to format a number as a currency.
How to Spell Out the Year when Formatting a Date in Oracle
Oracle Database includes a good selection of datetime format elements. that enable us to format dates and times when using the TO_CHAR(datetime) function.
One of these (the YEAR format element) allows us to format a date with the year spelled out. For example, instead of 1972, we’d get NINETEEN SEVENTY-TWO.
We can also use SYEAR so that BC dates are prefixed with a minus sign (-).
3 Ways to Return the Time Zone from a Datetime Value in Oracle
Below are three ways to return time zone information from a datetime value in Oracle Database.
Continue readingAbout the RM Format Element in Oracle
In Oracle Database, the RM format element allows you to return the month in roman numerals when using the TO_CHAR() function.
You can also use the fm modifier to suppress any padding.
How to Remove the Zero when the Integer Part is Zero in Oracle
When using the TO_CHAR() function to format a number in Oracle Database, you can use the B format element to return blanks for the integer part of a fixed-point number when the integer part is zero.
This works regardless of zeros in the format model.
Continue readingHow to Get the Century from a Date in Oracle
With Oracle Database, we can use the TO_CHAR(datetime) function to return the century from a datetime value.
To do this, we can use either the CC format element, or the SCC format element. We can combine these with other format elements to do things like, display the ordinal number, spell out the century, etc.
Check if a Table Exists in Oracle
In Oracle Database, there are a number of views that we can query to find out whether a table exists.
Continue reading