passing a non default interval to a procedure

August 30, 2012 — Leave a comment

ever wanted to pass a value of type interval to a procedure ? not a big a deal you might think:

create or replace procedure p1 ( a interval day to second ) is
begin
  null;
end;
/
exec p1 ( a => interval '10 00:00:00' day to second );

works as expected. but what if you want to pass an interval with a precision of more than two ?

exec p1 ( a => interval '101 00:00:00' day to second );
BEGIN p1 ( a => interval '101 00:00:00' day to second ); END;
*
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
ORA-06512: at line 1

should work, shouldn’t it ?
let’s try to specify the precision in the procedure’s declaration:

create or replace procedure p1 ( a interval day(3) to second ) is
begin
  null;
end;
  5  /
Warning: Procedure created with compilation errors.
SQL> show errors;
Errors for PROCEDURE P1:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/30	 PLS-00103: Encountered the symbol "(" when expecting one of the
	 following:
	 to

ok, it is not possible to specify this in the declaration. can we pass the precision to the procedure along with the parameter ?

exec p1 ( a => interval '101 00:00:00' day(3) to second );
BEGIN p1 ( a => interval '101 00:00:00' day(3) to second ); END;
                                           *
ERROR at line 1:
ORA-06550: line 1, column 44:
PLS-00103: Encountered the symbol "(" when expecting one of the following:
) , * & = - +  at in is mod remainder not rem
  or != or ~= >= <=  and or like like2
like4 likec between to || multiset member submultiset

syntax error, again. what’s wrong ? the documentation tells that the default precision is 2, but can be of any value between 0 and 9. so this must be possible somehow.

the solution is either to move the procedure to a package and to declare a subtype:

create or replace package ptest
is
   subtype my_interval is interval day(3) to second;
   procedure p1 ( v_interval IN my_interval );
end;
/
create or replace package body ptest
is
   procedure p1 ( v_interval IN my_interval ) 
   is
   begin
     null;
   end p1;
end;
/
SQL> exec ptest.p1 ( interval '230 23:0:0.1' day to second );
PL/SQL procedure successfully completed.
SQL>  exec ptest.p1 ( interval '23 23:0:0.1' day to second );
PL/SQL procedure successfully completed.

… or to reference the column of a table:

SQL> create table t1 ( a interval day(3) to second );
Table created.
SQL> create or replace procedure p1 ( a t1.a%type ) is begin null; end;
  2  /
Procedure created.
SQL> exec p1 ( a => interval '101 00:00:00' day to second);
PL/SQL procedure successfully completed.
SQL> exec p1 ( a => interval '10 00:00:00' day to second);
PL/SQL procedure successfully completed.
SQL> 
Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s