This content has been marked as final. Show 8 replies
1 person found this helpful
add_job( 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?
add_job( job_id => 202 );
Just use:1 person found this helpful
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'.
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.
>1 person found this helpful
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'
And then the specific section for default values
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.
Here's an example of calling your myprocedure. This example makes use of default values for sal_id, clock_id, lot_id
This however, WILL NOT WORK, since myprocedure does not have a default value declared for init_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; /
begin myprocedure (job_id => your_job_id, job_loc => your_job_loc, mgr_id => your_mgr_id, shift_id => your_shift_id ); end; /
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.