Calling a procedure with an interval literal

From: De DBA <dedba_at_tpg.com.au>
Date: Thu, 30 Aug 2012 14:15:00 +1000
Message-ID: <503EE8C4.5000802_at_tpg.com.au>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 29 2012 - 23:15:00 CDT