TRANSLATE(… USING) Function in Oracle

In Oracle, the TRANSLATE(...USING) function converts its argument into the character set specified for conversions between the database character set and the national character set.

This function is not to be confused with the TRANSLATE() function, which allows you to make several single-character, one-to-one substitutions in one operation.

The TRANSLATE(...USING) function is supported in Oracle primarily for ANSI compatibility. Oracle recommends that we use the TO_CHAR() and TO_NCHAR() functions instead.

Continue reading →

How to Change the Date Format in your Oracle Session

When working with functions that return dates, Oracle Database returns these dates based on the value of the NLS_DATE_FORMAT parameter.

There is also an NLS_TIMESTAMP_FORMAT parameter and an NLS_TIMESTAMP_TZ_FORMAT parameter, both of which have a datetime format mask that can be specified separately.

All of these parameters have their default values derived from the NLS_TERRITORY parameter (which by default, is operating system-dependent).

So, to change the date format for your session, you can either update the NLS_TERRITORY parameter, or explicitly update each parameter individually.

This article provides examples of checking and changing these values, and viewing the results.

Continue reading →