Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00306 error
PLS-00306 error [message #288290] Sun, 16 December 2007 22:54 Go to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

This is my code:
SQL> 
SQL> /* Van Au */
SQL> /* IS 480 */
SQL> /* Final Project */
SQL> /* 12- 17-07 */
SQL> 
SQL> Create or replace package Enroll is
  2  Procedure validate_students
  3  	     ( p_snum in students.snum%type,
  4  	       p_ErrorText out varchar2);
  5  Function validate_callnum
  6  	     ( p_callnum schclasses.callnum%type)
  7  	     return varchar2;
  8  Procedure validate_class_credit
  9  	     ( p_snum in enrollments.snum%type,
 10  	       p_callnum in schclasses.callnum%type,
 11  	       p_ErrorText out varchar2);
 12  Procedure validate_class_capacity
 13  	     (p_callnum in enrollments.callnum%type,
 14  	      p_ErrorText out varchar2);
 15  Function validate_double_enrollments
 16  	     (p_snum students.snum%type,
 17  	      p_callnum schclasses.callnum%type)
 18  	     return varchar2;
 19  Function validate_prereq
 20  	     (p_snum students.snum%type,
 21  	      p_callnum schclasses.callnum%type)
 22  	      return varchar2;
 23  Procedure validate_undeclared
 24  	     (p_snum in students.snum%type,
 25  	      p_callnum in schclasses.callnum%type,
 26  	      p_ErrorText out varchar2);
 27  Procedure Addme (p_snum in students.snum%type,
 28  				      p_callnum in enrollments.callnum%type,
 29  				      p_ErrorMsg out varchar2);
 30  Procedure Dropme (p_snum in students.snum%type,
 31  				       p_callnum in schclasses.callnum%type,
 32  				       p_ErrorMsg out varchar2);
 33  end;
 34  /

Package created.

SQL> show err
No errors.
SQL> 
SQL> Create or replace package body Enroll is
  2  Procedure Addme (p_snum in students.snum%type,
  3  				      p_callnum in enrollments.callnum%type,
  4  				      p_ErrorMsg out varchar2) as
  5  				      v_ErrorText varchar2 (200);
  6  				      v_waitlist varchar2 (5);
  7  	     begin
  8  		     validate_students(p_snum, v_ErrorText);
  9  			     p_ErrorMsg := v_ErrorText;
 10  			     v_ErrorText := validate_callnum (p_callnum);
 11  			     p_ErrorMsg := rtrim (p_ErrorMsg || v_ErrorText);
 12  
 13  			     if p_ErrorMsg is null then
 14  				     validate_class_credit (p_snum, p_callnum, v_ErrorText);
 15  				     p_ErrorMsg := p_ErrorMsg || v_ErrorText;
 16  
 17  				     v_ErrorText := validate_double_enrollments (p_snum, p_callnum);
 18  				     p_ErrorMsg := rtrim (p_ErrorMsg) || rtrim (v_ErrorText);
 19  
 20  				     validate_class_capacity (p_callnum, v_ErrorText);
 21  				     p_ErrorMsg := rtrim (p_ErrorMsg) || rtrim (v_ErrorText);
 22  
 23  				     v_ErrorText := validate_prereq (p_snum, p_callnum);
 24  				     p_ErrorMsg := rtrim (p_ErrorMsg) || rtrim (v_ErrorText);
 25  
 26  				     validate_undeclared (p_snum, p_callnum, v_ErrorText);
 27  				     p_ErrorMsg := rtrim (p_ErrorMsg) || rtrim (v_ErrorText);
 28  			     end if;
 29  
 30  			     if p_ErrorMsg is null then
 31  				     insert into enrollments
 32  					     values (enroll_id.nextval, p_snum, p_callnum, null);
 33  				     p_ErrorMsg := 'Congratulation, you enrolled in the course';
 34  			     else
 35  				     select count (*) into v_waitlist
 36  					     from waitlist
 37  					     where snum = p_snum
 38  					     and callnum = p_callnum;
 39  				     if v_waitlist = 0 then
 40  					     insert into waitlist values (p_snum, p_callnum, sysdate);
 41  					     dbms_output.put_line ('Your course has been added to waitlist');
 42  				     else
 43  					     dbms_output.put_line (p_ErrorMsg);
 44  				     end if;
 45  			     end if;
 46  	     end;
 47  
 48  Procedure Dropme (p_snum in students.snum%type,
 49  				       p_callnum in schclasses.callnum%type,
 50  				       p_ErrorMsg out varchar2) as
 51  				       p_ErrorText varchar2 (200);
 52  				       v_grade varchar2 (2);
 53  				       v_snum students.snum%type;
 54  				       v_callnum schclasses.callnum%type;
 55  				       v_enroll enrollments.enroll_id%type;
 56  cursor curwaitlist is
 57  					     select snum, callnum
 58  					     from waitlist
 59  					     order by time;
 60  begin
 61  	     validate_students (p_snum, p_ErrorText);
 62  	     p_ErrorMsg :=  p_ErrorText;
 63  	     if p_ErrorMsg is null then
 64  		     p_ErrorText := validate_callnum (p_callnum);
 65  		     p_ErrorMsg := rtrim (p_ErrorMsg ||  p_ErrorText);
 66  		     if p_ErrorMsg is null then
 67  			     select count (*) into v_enroll
 68  				     from enrollments
 69  				     where enrollments.snum = p_snum
 70  				     and enrollments.callnum = p_callnum;
 71  				     if (v_enroll > 0) then
 72  					     select grade into v_grade
 73  						     from enrollments
 74  						     where enrollments.snum = p_snum
 75  						     and enrollments.callnum = p_callnum;
 76  					     if v_grade is null then
 77  						     update enrollments
 78  						     set grade = 'W'
 79  						     where snum = p_snum
 80  						     and callnum = p_callnum;
 81  						     dbms_output.put_line ('Your course has been dropped.');
 82  
 83  				     Open curwaitlist;
 84  					     LOOP
 85  						     fetch curwaitlist into v_snum, v_callnum;
 86  						     Exit when curwaitlist%notfound;
 87  						     insert into enrollments
 88  							     values (enroll_id.nextval, v_snum, v_callnum, null);
 89  						     delete from waitlist
 90  							     where snum = v_snum
 91  							     and callnum = v_callnum;
 92  					     END LOOP;
 93  			     end if;
 94  		     else
 95  			     dbms_output.put_line ('You cannot drop this course because the grade has been assigned.');
 96  		     end if;
 97  	     else
 98  		     dbms_output.put_line (p_ErrorMsg);
 99  	     end if;
100  	     else
101  	     dbms_output.put_line (p_ErrorMsg);
102  	     end if;
103  end;
104  
105  Procedure validate_students (p_snum in students.snum%type,
106  							      p_ErrorText out varchar2) as
107  							      v_count_students number;
108  	     begin
109  		     select count (*) into v_count_students
110  			     from students
111  			     where snum=p_snum;
112  			     if (v_count_students=0) then
113  				     p_ErrorText := 'Invalid Student ID';
114  			     else
115  				     p_ErrorText := null;
116  			     end if;
117  	     end;
118  
119  Function validate_callnum(
120  	     p_callnum schclasses.callnum%type) return varchar2 is
121  	     p_callnumcount number;
122  	     p_ErrorText varchar2 (200);
123  begin
124  	     select count (*) into p_callnumcount
125  		     from schclasses, enrollments
126  		     where schclasses.callnum = p_callnum
127  		     and enrollments.callnum = schclasses.callnum;
128  	     if (p_callnumcount =0) then
129  		     p_ErrorText := 'Invalid Callnum';
130  	     else
131  		     p_ErrorText := null;
132  	     end if;
133  	     return p_ErrorText;
134  end;
135  
136  Function validate_double_enrollments (p_snum students.snum%type,
137  									      p_callnum schclasses.callnum%type) return varchar2 is
138  									      r_sch schclasses%rowtype;
139  									      v_countcourse number;
140  									  v_count number;
141  									      p_ErrorText varchar2 (200);
142  	     begin
143  		     select count (*) into v_countcourse
144  			     from enrollments
145  			     where snum = p_snum
146  			     and callnum = p_callnum;
147  		     select * into r_sch
148  			     from schclasses
149  			     where callnum = p_callnum;
150  		     select count (*) into v_count
151  			     from schclasses, enrollments
152  			     where schclasses.dept= r_sch.dept
153  			     and schclasses.cnum = r_sch.cnum
154  			     and enrollments.snum = p_snum
155  			     and schclasses.year = r_sch.year
156  			     and schclasses.semester = r_sch.semester
157  			     and enrollments.callnum = schclasses.callnum;
158  
159  			     if v_countcourse = 0 then
160  				     if v_count = 0 then
161  					     p_ErrorText := null;
162  				     else
163  					     p_ErrorText := 'Sorry! You already enrolled in another section of this course.';
164  				     end if;
165  			     else
166  				     p_ErrorText:= 'Sorry! You already enrolled in this course.';
167  			     end if;
168  		     return p_ErrorText;
169  	     end;
170  
171  Procedure validate_class_credit(p_snum in enrollments.snum%type,
172  								     p_callnum in schclasses.callnum%type,
173  								     p_ErrorText out varchar2) as
174  								     v_total_credit number;
175  								     v_class_credit number;
176  								     v_total_student_credit number;
177  	     begin
178  		     select sum(crhr) into v_total_credit
179  			     from courses, enrollments, schclasses
180  			     where enrollments.snum = p_snum
181  			     and enrollments.callnum = schclasses.callnum
182  			     and schclasses.dept = courses.dept
183  			     and schclasses.cnum = courses.cnum;
184  				     select crhr into v_class_credit
185  					     from schclasses, courses
186  					     where schclasses.callnum = p_callnum
187  					     and schclasses.dept = courses.dept
188  					     and schclasses.cnum = courses.cnum;
189  				     v_total_student_credit := v_total_credit + v_class_credit;
190  			     if (v_total_student_credit >15) then
191  				     p_ErrorText := 'Credits limit met';
192  			     else
193  				     p_ErrorText := null;
194  			     end if;
195  	     end;
196  
197  Procedure validate_class_capacity (p_callnum in enrollments.callnum%type,
198  									p_ErrorText out varchar2) as
199  									v_class_capacity number;
200  									v_total_number_students number;
201  	     begin
202  		     select capacity into v_class_capacity
203  			     from schclasses
204  			     where schclasses.callnum = p_callnum;
205  				     select count (snum) into v_total_number_students
206  					     from enrollments, schclasses, courses
207  					     where enrollments.callnum = p_callnum
208  					     and enrollments.callnum =schclasses.callnum
209  					     and schclasses.dept = courses.dept
210  					     and schclasses.cnum = courses.cnum;
211  				     if ((v_total_number_students +1 ) <= v_class_capacity) then
212  					     p_ErrorText := null;
213  				     else
214  					     p_ErrorText := 'Class capacity met';
215  				     end if;
216  	     end;
217  
218  Function validate_prereq (p_snum students.snum%type,
219  						       p_callnum schclasses.callnum%type) return varchar2 is
220  						       p_ErrorText varchar2 (200);
221  						       v_count_prereq number (5);
222  						       r_sch schclasses%rowtype;
223  	     begin
224  		     select * into r_sch
225  			     from schclasses
226  			     where callnum = p_callnum;
227  			     select count (*) into v_count_prereq
228  				     from ((select prereq.pdept, prereq.pcnum
229  						     from prereq
230  						     where prereq.dept = r_sch.dept
231  						     and prereq.cnum = r_sch.cnum)
232  					     minus
233  					     (select schclasses.dept, schclasses.cnum
234  						     from enrollments , schclasses
235  						     where enrollments.callnum = schclasses.callnum
236  						     and enrollments.snum = p_snum
237  						     and grade in ('A', 'B', 'C', 'D')));
238  
239  		     if v_count_prereq = 0 then
240  			     p_ErrorText := null;
241  		     else
242  			     p_ErrorText := 'You have not met the requirements';
243  		     end if;
244  	     return p_ErrorText;
245  	     end;
246  
247  Procedure validate_undeclared (p_snum in students.snum%type,
248  								     p_callnum in schclasses.callnum%type,
249  								     p_ErrorText out varchar2) as
250  								v_major varchar2 (3);
251  								v_cnum varchar2 (3);
252  
253  	     begin
254  		     select major into v_major
255  			     from students
256  			     where students.snum = p_snum;
257  		     select cnum into v_cnum
258  			     from schclasses
259  			     where callnum = p_callnum;
260  		     if v_major is null  then
261  			     if v_cnum is not null then
262  				     p_ErrorText := 'Sorrry, you need to declare your major';
263  			     else
264  				     p_ErrorText:= null;
265  			     end if;
266  		     else
267  			     p_ErrorText := null;
268  		     end if;
269  	     end;
270  
271  end enroll;
272  /

Package body created.

SQL> show err
No errors.
SQL> spool off



and this is my error when I exec.
SQL> exec Enroll.Addme (111,10110);
BEGIN Enroll.Addme (111,10110); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ADDME'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


IF I take the out parameter ( p_ErrorMsg out varchar2) away, it works well. What if I want to keep this out parameter, what should I do to fix the erro.

Thanks
Re: PLS-00306 error [message #288292 is a reply to message #288290] Sun, 16 December 2007 22:57 Go to previous messageGo to next message
vickie2405
Messages: 27
Registered: May 2007
Location: California, US
Junior Member

a, I got it now,.
Thanks all.
Re: PLS-00306 error [message #288293 is a reply to message #288290] Sun, 16 December 2007 22:58 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SET AUTOPRINT ON
VARIABLE g_out VARCHAR2(2000)
exec Enroll.Addme (111,10110, :g_out)


Previous Topic: group by & having clause
Next Topic: confusion for day of week
Goto Forum:
  


Current Time: Wed Dec 07 08:55:34 CST 2016

Total time taken to generate the page: 0.12167 seconds