This discussion is archived
8 Replies Latest reply: Mar 21, 2013 5:38 PM by davejjj RSS

Using Default Parameter values with Functions or Procedures?

davejjj Newbie
Currently Being Moderated
If you have a procedure or function that has DEFAULT values declared for some of the parameters how do you call that function or procedure when you want to use some of the default values? I tried...

myProcedure(202,,);
myProcedure(202,'','');
myProcedure(202,null,null);
myProcedure(job_id=202);
myProcedure(job_id=>202);

None of the above worked.

PROCEDURE add_job (job_id IN jobs.job_id%TYPE
,job_loc IN jobs.job_location%TYPE DEFAULT 145
,mgr_id IN jobs.mgr_id%TYPE DEFAULT 30
);

Thanks.

EDIT -- Let me say this is a simplified example. Please imagine a much larger parameter list with perhaps half having DEFAULT values. Thanks.

PROCEDURE myProcedure (job_id IN jobs.job_id%TYPE
,job_loc IN jobs.job_location%TYPE DEFAULT 145
,mgr_id IN jobs.mgr_id%TYPE DEFAULT 30
,init_id IN jobs.init_id%TYPE
,sal_id IN jobs.sal_id%TYPE DEFAULT 100
,clock_id IN jobs.clock_id%TYPE DEFAULT 'A'
,shift_id IN jobs.shift_id%TYPE
,lot_id IN jobs.lot_id%TYPE DEFAULT 'BACK'
);

Edited by: davejjj on Mar 20, 2013 4:47 PM
  • 1. Re: Using Default Parameter values with Functions or Procedures?
    Justin Cave Oracle ACE
    Currently Being Moderated
    add_job( 202 );
    will work.
    add_job( job_id => 202 );
    will also work (assuming that you are trying to call ADD_JOB not MyProcedure). I'm not sure why you listed this as something you tried that didn't work. What error did you get?

    Justin
  • 2. Re: Using Default Parameter values with Functions or Procedures?
    Solomon Yakobson Guru
    Currently Being Moderated
    Just use:
    myProcedure(202);
    SY.
  • 4. Re: Using Default Parameter values with Functions or Procedures?
    rp0428 Guru
    Currently Being Moderated
    >
    None of the above worked.
    >
    Correct - none of them will work. You don't have a procedure named 'myProcedure'.

    The code you posted is for a procedure named 'add_job'.
  • 5. Re: Using Default Parameter values with Functions or Procedures?
    davejjj Newbie
    Currently Being Moderated
    Yes, my poor code is sort of comical I guess. I'm sorry I am unable to provide the actual transcript which was not so simple as my contrived cut-and-paste example. So in general imagine a large list of parameters with some of them having default values available. What is the general approach to selectively supplying some of those parameters? Thanks.
  • 6. Re: Using Default Parameter values with Functions or Procedures?
    rp0428 Guru
    Currently Being Moderated
    >
    What is the general approach to selectively supplying some of those parameters?
    >
    The best explanation is from the PL/SQL Language doc itself

    See 'Declaring and Passing Subprogram Parameters'
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#i23202

    And then the specific section for default values
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/subprograms.htm#i6821
    >
    Specifying Default Values for Subprogram Parameters
    By initializing formal IN parameters to default values, you can pass different numbers of actual parameters to a subprogram, accepting the default values for omitted actual parameters. You can also add new formal parameters without having to change every call to the subprogram.

    If an actual parameter is omitted, the default value of its corresponding formal parameter is used.

    You cannot skip a formal parameter by omitting its actual parameter. To omit the first parameter and specify the second, use named notation (see Passing Actual Subprogram Parameters with Positional, Named, or Mixed Notation).

    You cannot assign NULL to an uninitialized formal parameter by omitting its actual parameter. You must either assign NULL as a default value or pass NULL explicitly.
    >
    Since parameters MUST either be provided or have a default you should define the required parameters first and the parameters with defaults last.

    That way a caller only needs to provide values for the required ones.
  • 7. Re: Using Default Parameter values with Functions or Procedures?
    Peter Gjelstrup Guru
    Currently Being Moderated
    Hi davejjj,

    Here's an example of calling your myprocedure. This example makes use of default values for sal_id, clock_id, lot_id
    declare
      procedure myprocedure (
        job_id     in jobs.job_id%type,
        job_loc    in jobs.job_location%type default 145,
        mgr_id     in jobs.mgr_id%type default 30,
        init_id    in jobs.init_id%type,
        sal_id     in jobs.sal_id%type default 100,
        clock_id   in jobs.clock_id%type default 'A',
        shift_id   in jobs.shift_id%type,
        lot_id     in jobs.lot_id%type default 'BACK'
      );
    begin
      myprocedure (job_id     => your_job_id,
                   job_loc    => your_job_loc,
                   mgr_id     => your_mgr_id,
                   init_id    => null,
                   shift_id   => your_shift_id
                  );
    end;
    /
    This however, WILL NOT WORK, since myprocedure does not have a default value declared for init_id
    begin
      myprocedure (job_id     => your_job_id,
                   job_loc    => your_job_loc,
                   mgr_id     => your_mgr_id,
                   shift_id   => your_shift_id
                  );
    end;
    /
    Regards
    Peter
  • 8. Re: Using Default Parameter values with Functions or Procedures?
    davejjj Newbie
    Currently Being Moderated
    Thanks to all responders. Apparently I had used 80=>IN_DEPT_ID rather than IN_DEPT_ID=>80 as my parameter and that is why it failed.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points