[Codey09]

Hi Guys,

I am currently studying a Foundation degree in computer software development, and one of my assignment in PL/SQL I am stuck on one of the tasks.

I have to create a procedure where one of the parameters needs to have a default value of one, if no value is entered when the procedure is called. I have trued to use the NVL function which worked when using a anonymous block, but now I have to convert that to a procedure. My problem is I'm getting an error.

The code for the procedure is

CREATE OR REPLACE PROCEDURE add_new_classes
   (p_number_of_classes NUMBER := NVL(NULL,1), -- This will enter a default value of 1 if the user does not specify a number
   p_course_id classes.course_id%TYPE,
   p_period classes.period%TYPE,
   p_frequency classes.frequency%TYPE,
   p_instructor_id classes.instr_id%TYPE)
IS
   v_class_id classes.class_id%TYPE; -- Sets up a variable to hold the highest class id
   v_counter NUMBER := 1; -- Sets a counter for the loop
BEGIN
   SELECT MAX(class_id) -- Obtains the highest class_id in the table and assigns the value to the variable
      INTO v_class_id
      FROM classes;
   LOOP      
      v_class_id := v_class_id+1; -- adds 1 to the class_id, so we dont overwrite the existing data
      INSERT INTO classes (class_id, start_date, period, frequency, instr_id, course_id)
         VALUES (v_class_id, SYSDATE, p_period, p_frequency, p_instructor_id, p_course_id); -- Inserts the data into the table
      v_counter := v_counter +1; -- Increments the counter by 1
      EXIT WHEN v_counter > p_number_of_classes; -- checks to see if the counter is greater than the input from the user if so exits the loop
   END LOOP;
END;

I then use this to test it

BEGIN
   add_new_classes(1002,'first','daily',3002);
END;

and the error I get is

Quote

ORA-06550: line 2, column 4:
PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES'
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored
1. BEGIN
2. add_new_classes(1002,'first','daily',3002);
3. END;

I cant see what I am doing wrong, any help would be appreciated

This post has been edited by Codey09: 16 May 2011 - 04:05 AM

[baavgai]

I believe PL/SQL, like most procedural languages, likes it's optional parameters at the end. The reason for this is simple; you can just leave off the optional values in the procedure call.

The procedure behaves a lot like a row insert; if you give it a null, it will use the default value.

This should work:

CREATE OR REPLACE PROCEDURE add_new_classes (
	p_course_id classes.course_id%TYPE,
	p_period classes.period%TYPE,
	p_frequency classes.frequency%TYPE,
	p_instructor_id classes.instr_id%TYPE,
	p_number_of_classes NUMBER := 1
)

Or, perhaps more clear:

CREATE OR REPLACE PROCEDURE add_new_classes (
	p_course_id classes.course_id%TYPE,
	p_period classes.period%TYPE,
	p_frequency classes.frequency%TYPE,
	p_instructor_id classes.instr_id%TYPE,
	p_number_of_classes NUMBER default 1
)

[Codey09]

That works thank you