2 Replies - 1818 Views - Last Post: 16 May 2011 - 04:05 AM

#1 Codey09  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 210
  • Joined: 20-August 09

[SOLVED]Using NVL function on a parameter in a procedure

Posted 16 May 2011 - 03:48 AM

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


Is This A Good Question/Topic? 0
  • +

Replies To: [SOLVED]Using NVL function on a parameter in a procedure

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5804
  • View blog
  • Posts: 12,642
  • Joined: 16-October 07

Re: [SOLVED]Using NVL function on a parameter in a procedure

Posted 16 May 2011 - 03:59 AM

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
)


Was This Post Helpful? 1
  • +
  • -

#3 Codey09  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 210
  • Joined: 20-August 09

Re: [SOLVED]Using NVL function on a parameter in a procedure

Posted 16 May 2011 - 04:05 AM

That works thank you :bigsmile:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1