The Fractional Part of a TIMESTAMP. Be careful.

This discovery came in from an AskTom customer but I figured it’s worth passing on to any PL/SQL programmers out there.

The FF element of the format mask is for the fractional part of a second, and thus only applies to the TIMESTAMP series of data types. A timestamp can contain up to 9 digits of precision, but as per the docs, the default fractional part is 6 digits, even if the platform you are running on cannot provide that level of precision. For example, on my Windows machine, I get 6 digits but only 3 digits of precision (unless I was really lucky ).

SQL> select to_char(systimestamp,'FF') from dual;

TO_CHAR(S
---------
698000

But PL/SQL has a slightly different interpretation of this. Perhaps because it is intended to be platform-independent, PL/SQL always gives the full 9 digits of precision.

SQL> exec dbms_output.put_line(to_char(systimestamp,'FF'));
706000000

PL/SQL procedure successfully completed.

That might not seem any cause for concern, but you do need to be careful when it comes to querying that component (or any TO_CHAR on a TIMESTAMP column/variable that contains that component) into a variable, because the size of receiving variable then becomes significant.

SQL> variable x varchar2(6)
SQL> select to_char(systimestamp,'FF') into :x from dual;

TO_CHAR(S
---------
321000

SQL> declare
  2    x varchar2(6);
  3  begin
  4    select to_char(systimestamp,'FF') into x from dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> declare
  2    x varchar2(6);
  3    y timestamp := systimestamp;
  4  begin
  5    select to_char(y,'FF') into x from dual;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5


SQL> declare
  2    x varchar2(6);
  3  begin
  4    x := to_char(systimestamp,'FF');
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

A bug has been logged to bring the two into alignment, but until that is fixed, its probably safest to allow 9 characters worth of precision in PL/SQL for the FF component.