Oracle 11gR2 – Default Values in Query Functions

It might be possible if you consider overloading a procedure. Suppose you want the default of 5 for parameter a and 7 for parameter b and want also to check if the parameters were passed. In this case you have to overload the procedure 4 times for each combination of (with and without parameter). Finally, the real thing happens in the private procedure p_:

set serveroutput on size 100000 format wrapped

create or replace package tq84_test_pck as

   procedure p;
   procedure p(a number          );
   procedure p(          b number);
   procedure p(a number, b number);

end tq84_test_pck;
/

create or replace package body tq84_test_pck as

    procedure p_(a number := 5, a_default boolean,
                 b number := 7, b_default boolean) as
    begin

        dbms_output.put_line(
             'a: ' || a || 
             case when a_default then ' [default]'
                                 else ' [passed ]' end);

        dbms_output.put_line(
             'b: ' || b || 
             case when b_default then ' [default]'
                                 else ' [passed ]' end);

        dbms_output.new_line;

    end p_;

    procedure p is
    begin
        p_(a_default => true, 
           b_default => true);
    end p;

    procedure p (a number) is
    begin
        p_(a         => a, 
           a_default => false, 
           b_default => true);
    end p;

    procedure p (b number) is
    begin
        p_(b         => b, 
           a_default => true, 
           b_default => false);
    end p;

    procedure p (a number, b number) is
    begin
        p_(a         => a,
           b         => b, 
           a_default => false, 
           b_default => false);
    end p;

end tq84_test_pck;
/

show errors

declare

begin

   tq84_test_pck.p;
   tq84_test_pck.p( b => 7);
   tq84_test_pck.p( a => 7 );
   tq84_test_pck.p( a => null);
   tq84_test_pck.p( a => 5, b => 6);

end;
/

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;