Calling a procedure with an interval literal

From: De DBA <dedba@xxxxxxxxxx>
To: Oracle Discussion List <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 30 Aug 2012 14:15:00 +1000
G'day all

(Oracle 11.2.0.3)

I'm stuck on a tricky problem, and Google doesn't seem to want to be my 
friend today.. :( What I am trying to achieve is a procedure (to be 
scheduled in the database) that will split all partitions of a certain 
name into two. This is handy, as we archive data in 3-month chunks so we 
want all top partitions (which have a distinctive name) in nominated 
schemas to be split without fail on the same boundary.

For this purpose I want it to accept a day-to-second interval, so that 
the DBA later can decide to increase or decrease the amount of time that 
is contained in the partitions - seems handy to me. The problem is now, 
that I cannot get the procedure to accept day intervals of more than 99 
days. The header that I've got is:

        procedure  split_all_partitions (
                  split_from     in interval day to second
                , partition_name in varchar2 := 'P_TOP'
                , new_name       in varchar2 := ''
                , schema_list    in varchar2 := user
         )  is
        ...

(it's overloaded: another version accepts a maximum date to start the 
archived partition from, hence the parameter name "split_from"). The 
interval is not modified in the body, nor is there a declared interval 
variable. It is only ever added or subtracted from date variables.

This is what happens when I call the procedure:

SQL> exec split_all_partitions ( interval '230 23:0:0.1' day to second );
BEGIN split_all_partitions ( interval '230 23:0:0.1' day to second ); END;

*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at line 1

Adding a leading precision to the formal parameter declaration is not 
possible - does not compile. Adding a leading precision in the call 
doesn't work either:

SQL> exec split_all_partitions ( interval '230 23:0:0.1' day(3) to second );
BEGIN split_all_partitions ( interval '230 23:0:0.1' day(3) to second ); 
END;

                                                         *
ERROR at line 1:
ORA-06550: line 1, column 70:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
) , * & = - + < / > at in is mod remainder not rem =>
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec as between from to using || multiset member
submultiset

But that syntax is correct though:

SQL> select interval '230 23:0:0.1' day(3) to second from dual;

INTERVAL'23023:0:0.1'DAY(3)TOSECOND
===========================================================================
+230 23:00:00.100000

1 row selected.

Any hints?

Cheers,
Tony
--
//www.freelists.org/webpage/oracle-l
Calling a procedure with an interval literal

Other related posts:
