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
PLS-00306: wrong number or types of arguments in call to 'ADD_NEW_CLASSES'
ORA-06550: line 2, column 4:
PL/SQL: Statement ignored
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