Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01873: the leading precision of the interval is too small (Oracle 10g)
ORA-01873: the leading precision of the interval is too small [message #399628] Thu, 23 April 2009 03:23 Go to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Hi All,
I am encountering this error in a pl/sql procedure.

The procedure is used to update some business related variable values. The procedure is calcualting the total time taken for this update.
At one place the curent timestamp is stored in a variable and after the update the current timestamp is stored in another variable. Both the variables are of type 'systimestamp'. The result of this expression is stored in a variable whose type is given as:
'INTERVAL DAY (0) TO SECOND (6)'.

The error is occurring randomly. Can anyone help me in this regards?

Thanks in advance,
Kaushik

Re: ORA-01873: the leading precision of the interval is too small [message #399630 is a reply to message #399628] Thu, 23 April 2009 03:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01873: the leading precision of the interval is too small
 *Cause: The leading precision of the interval is too small to store the
  specified interval.
 *Action: Increase the leading precision of the interval or specify an
  interval with a smaller leading precision.

One difference is >= 1 day.

Regards
Michel
Re: ORA-01873: the leading precision of the interval is too small [message #399632 is a reply to message #399630] Thu, 23 April 2009 03:53 Go to previous messageGo to next message
k_mitra100
Messages: 28
Registered: October 2007
Location: Kolkata
Junior Member
Thanks Michel,

Could you please explain in detail about "One difference is >= 1 day."?





Thanks,
Kaushik
Re: ORA-01873: the leading precision of the interval is too small [message #399633 is a reply to message #399632] Thu, 23 April 2009 03:59 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare
  2    t1 timestamp := systimestamp;
  3    t2 timestamp := systimestamp + interval '86399' second;
  4    d INTERVAL DAY (0) TO SECOND (6);
  5  begin
  6    d := t2 - t1;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> declare
  2    t1 timestamp := systimestamp;
  3    t2 timestamp := systimestamp + interval '86400' second;
  4    d INTERVAL DAY (0) TO SECOND (6);
  5  begin
  6    d := t2 - t1;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at line 6

Regards
Michel
Previous Topic: How to write query for this
Next Topic: UTL_FILE and invalid directory path
Goto Forum:
  


Current Time: Sat Dec 03 03:49:14 CST 2016

Total time taken to generate the page: 0.16247 seconds