In SQL*Plus, if we SELECT CAST(<expression of type interval day to second> AS INTERVAL DAY(5) TO SECOND(4)), or if we define a table column to be of type INTERVAL DAY(5) TO SECOND (4), SQL*Plus will honor the request: the value will be displayed with five digits for day and four decimal places for second. Oddly, *_TAB_COLUMNS calls 5 the "precision" of the column and 4 the "scale".
It seems SQL Developer disregards such directives - as to precision and scale of interval expressions - and always displays the result as DAY(2) to SECOND(6) - both in the Query Result pane (when run as a query) and in the Script Output (which in many other respects behaves like a very close clone of SQL*Plus). I run everything on Oracle 12.1.2.0.2 on Windows 10; I observed the SQL Developer behavior on 4.1.5 and also on 17.4.1.054.
A few questions about this:
- Is this true? Or am I imagining things? (Or did I miss a setting somewhere?)
- If it's true, is it well known? I did a few searches, both generic Google search and on this forum (using the search feature on this page) and nothing came back, but perhaps I just didn't search well enough.
- If it's true, why?
Thank you!
Demo:
SQL*Plus:
SQL> select interval '1' hour as intrv from dual;
INTRV
---------------------------------------------------------------------------
+00 01:00:00
SQL> select cast(interval '1' hour as interval day(5) to second(4)) as fmt_intrv from dual;
FMT_INTRV
---------------------------------------------------------------------------
+00000 01:00:00.0000
SQL> create table interv_test (z interval day to second);
Table created.
SQL> insert into interv_test (z) values (interval '1' hour);
1 row created.
SQL> select * from interv_test;
Z
---------------------------------------------------------------------------
+00 01:00:00.000000
SQL> truncate table interv_test;
Table truncated.
SQL> alter table interv_test modify (z interval day(5) to second(4));
Table altered.
SQL> insert into interv_test (z) values (interval '1' hour);
1 row created.
SQL> select * from interv_test;
Z
---------------------------------------------------------------------------
+00000 01:00:00.0000
SQL Developer: (shorter demo - you get the idea)
First, COMMIT the last transaction in the SQL*Plus session, so we have something to select. (COMMIT not shown here.) Then SELECT in SQL Plus:
