8 Replies Latest reply: Mar 21, 2013 7:38 PM by davejjj RSS

    Using Default Parameter values with Functions or Procedures?

    davejjj
      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?
          JustinCave
          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
            Just use:
            myProcedure(202);
            SY.
            • 4. Re: Using Default Parameter values with Functions or Procedures?
              rp0428
              >
              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
                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
                  >
                  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
                    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
                      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.