SQL & PL/SQL

hi
i write a program calculate periods of time and summary it then i used interval day to second data type

oracle support basic 4 math operation (+,-,*,/) on interval
but although oracle does not support summary function like sum() ,avg(),count()!! this is a problem here
the walkaround it by use oracle data cartdrage advanced future like follows steps :

*1- i create type and i call it for ex: DAY_TO_SECOND_SUM_TYPE*


CREATE OR REPLACE DAY_TO_SECOND_SUM_TYPE /*TYPE as object
+(TOTAL INTERVAL day to SECOND , /* type header*/+
STATIC FUNCTION ODCIAggregateInitialize(SCTX IN OUT DAY_TO_SECOND_SUM_TYPE) return number,
member function ODCIAggregateIterate( self in out DAY_TO_SECOND_SUM_TYPE,value in interval day to second) return number,
member function ODCIAggregateTerminate(self in DAY_TO_SECOND_SUM_TYPE,returnvalue out interval day to second , flags in number) return number,
member function ODCIAggregateMerge(self in out DAY_TO_SECOND_SUM_TYPE,ctx2 in DAY_TO_SECOND_SUM_TYPE) return number
+)+
+/+

--------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE "DAY_TO_SECOND_SUM_TYPE" as object
+(TOTAL INTERVAL day to SECOND ,+
STATIC FUNCTION ODCIAggregateInitialize(SCTX IN OUT DAY_TO_SECOND_SUM_TYPE) return number,
member function ODCIAggregateIterate( self in out DAY_TO_SECOND_SUM_TYPE,value in interval day to second) return number,
member function ODCIAggregateTerminate(self in DAY_TO_SECOND_SUM_TYPE,returnvalue out interval day to second , flags in number) return number,
member function ODCIAggregateMerge(self in out DAY_TO_SECOND_SUM_TYPE,ctx2 in DAY_TO_SECOND_SUM_TYPE) return number
+)+
------------------------------------------------------------------------------
create or replace type body DAY_TO_SECOND_SUM_TYPE as /* type body*/


static function ODCIAggregateInitialize(sctx in out DAY_TO_SECOND_SUM_TYPE) return number is
begin

sctx := DAY_TO_SECOND_SUM_TYPE(numtodsinterval(0,'second'));
return odciconst.Success;
end;


member function ODCIAggregateIterate( self in out DAY_TO_SECOND_SUM_TYPE,value in interval day to second) return number is
begin
self.total := self.total value;+
return odciconst.Success;

end ;

member function ODCIAggregateTerminate(self in DAY_TO_SECOND_SUM_TYPE,returnvalue out interval day to second , flags in number) return number is
begin
returnvalue := self.total;
return odciconst.Success;
end;

member function ODCIAggregateMerge(self in out DAY_TO_SECOND_SUM_TYPE,ctx2 in DAY_TO_SECOND_SUM_TYPE) return number is
begin

self.total := self.total ctx2.total;+
return odciconst.Success;
end;
end;
+/+
/**************************************************************************************/

*2- i create artificial function to summary using that type let as called for ex: ds_sum as follows :*


create or replace function ds_sum( input interval day to second ) return interval day to second
parallel_enable aggregate
using DAY_TO_SECOND_SUM_TYPE
+/+

at last i used the function to summary intervals and its work so good but when the result of calculated summary value exceed the default precision of interval : INTERVAL DAY(2) TO SECOND(6)
i get an error :
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
although i can determine the precision of interval the declaration of the new type to maximum like :
+...........+
CREATE OR REPLACE TYPE "DAY_TO_SECOND_SUM_TYPE" as object
+(TOTAL INTERVAL day(9) to SECOND(6) ...........+
+.......................+

but the problem remain (in fact the error move from one functions of type to another function )
due i can't determine the precision of data type when i define function in PL/SQL it is illegal*

HOW CAN I SOLVE THIS PROBLEM AND BE ABLE TO SUMMARY INTERVAL DATA TYPE WHAT EVER THE RESULT OF SUMMARY IS HUGE

Edited by: user1193723 on Jun 18, 2009 4:35 AM

Edited by: user1193723 on Jun 18, 2009 4:36 AM

Edited by: user1193723 on Jun 18, 2009 4:37 AM

Edited by: user1193723 on Jun 18, 2009 4:38 AM

Edited by: user1193723 on Jun 18, 2009 4:38 AM

Edited by: user1193723 on Jun 18, 2009 4:40 AM