Skip to Main Content
  • Questions
  • TO_CHAR(TIMESTAMP, 'FF') return different length between SQL and PL/SQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Charles.

Asked: April 28, 2022 - 4:38 pm UTC

Last updated: May 11, 2022 - 5:39 am UTC

Version: 19c

Viewed 100+ times

You Asked

As stated in SQL Language Reference, the default fractional_seconds_precision is 6 in a TIMESTAMP data type.
"where fractional_seconds_precision optionally specifies the number of digits Oracle stores in the fractional part of the SECOND datetime field. When you create a column of this data type, the value can be a number in the range 0 to 9. The default is 6."

Also stated in Format Models, FF will use the precision of the data type.
"Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in DATE formats."

So in thoery, TO_CHAR(TIMESTAMP, 'FF') should return fractional seconds in 6 digits, and if use TO_CHAR with a TIMESTAMP with specified precision, the result length should be equal to the precision.

BUT, this is not the case as you can see in the LiveSQL demo:
1. With data type defined in table and a SQL directly select from it, the behaviour is as expected.
2. With data type defined in PL/SQL and to_char in PL/SQL, TO_CHAR(TIMESTAMP, 'FF') will always return 9 digits.
3. With data type defined in PL/SQL and to_char in SQL, TO_CHAR(TIMESTAMP, 'FF') will always return 9 digits too.
4. TO_CHAR(SYSTIMESTAMP, 'FF') also return different length between SQL and PL/SQL.

So the question is: What's the reason behind the inconsistent behaviour? Is this intentional or BUG?

Thank you.

with LiveSQL Test Case:

and Connor said...

Unfortunately a known bug (5108020).

Sorry I don't have a timeframe for a fix.

Rating

  (1 rating)

Comments

A reader, May 06, 2022 - 1:17 am UTC

Thank you for the response. Is BUG 5108020 internal? Since I can't find it at MOS.
Connor McDonald
May 11, 2022 - 5:39 am UTC

Looks like it is.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library