0 Replies - 1475 Views - Last Post: 01 September 2011 - 11:30 AM

#1 deepdave   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 19
  • Joined: 25-April 09

SQLPLUS - How to call a procedure with 'in' and 'out'

Posted 01 September 2011 - 11:30 AM

I want to call my procedure using a description and having it return a grade based on the last character of the description. However, it doesn't seem to like my CALL to the procedure from within my code. THe message is '
ORA-06550 error line 39 column 13 (which is where SETGRADE starts)
'PLS 00103 - Encoumntered the symboll 'SETGRADE" when expecting one of the following: := . < @ % ;

I'm also not clear on whether I need to declare and use (in the code) the 'out' paramerer named in the call, or whether I use the 'out' parameter defined and named in the procedure in the CALL statement in the code. I am using the 'out' parameter from the procedure in the code CALL statement below, but I got the same error when I used one I defined in the code I got the same error.

Thanks in advance (Procedure and code below)


CREATE OR REPLACE PROCEDURE SETGRADE (my_description in course9.description%type, gradeVALUE out course9.status%type) 

is
BEGIN

   if substr(my_description, length(my_description),1)  IN ('a', 'A', 'b', 'B', 'c', 'C', 'd', 'D', 'e', 'E', 'f', 'F') then           
      gradeVALUE := 'A';         
   end if;

   if substr(my_description, length(my_description),1) IN ('g', 'G', 'h', 'H', 'i', 'I', 'j', 'J', 'k', 'K')  then
      gradeVALUE := 'B';
   end if;

   if substr(my_description, length(my_description),1)  IN ('l', 'L', 'm', 'M', 'n', 'N', 'o', 'O', 'p', 'P')  then
      gradeVALUE := 'C';
   end if;

   if substr(my_description, length(my_description),1)  IN ('q', 'Q', 'r', 'R', 's', 'S', 't', 'T')  then
      gradeVALUE := 'D';
   end if;

   if substr(my_description, length(my_description),1)  IN ('w', 'W', 'x', 'X', 'y', 'Y', 'z', 'Z')  then
      gradeVALUE := 'E';
   end if;

END SETGRADE;
/


/]

[
 DECLARE
     CURSOR c1 is
          SELECT studentid, courseid, sectionid, grade FROM grade9 order by courseid;
       my_studentid   grade9.studentid%type;
       my_courseid_g  grade9.courseid%type;
       my_sectionid   grade9.sectionid%type;
       my_grade       grade9.grade%type;

 CURSOR c2 is
          SELECT courseid, coursenumber, coursename, description, credithours, status from course9 order by courseid;

       my_courseid_c     course9.courseid%type;
       my_coursenumber   course9.coursenumber%type;
       my_coursename     course9.coursename%type;
       my_description    course9.description%type;
       my_credithours    course9.credithours%type;
       my_status         course9.status%type;

       valuegrade course9.status%type;
     
    BEGIN
      OPEN c1;
      open c2;

     

      FETCH c2 into my_courseid_c, my_coursenumber, my_coursename, my_description, my_credithours, my_status;
      
   
      LOOP
        
      FETCH c1 INTO my_studentid, my_courseid_g, my_sectionid, my_grade;

       if my_courseid_g != my_courseid_c then  
           FETCH c2 into my_courseid_c, my_coursenumber, my_coursename, my_description, my_credithours, my_status;
       end if;   
     
       
       CALL SETGRADE(MY_DESCRIPTION, gradeVALUE);   

           update grade9 
           set grade = gradeVALUE
           where studentid = my_studentid and sectionid = my_sectionid;
           

           DBMS_OUTPUT.PUT_LINE('studentid ' || my_studentid || ' courseid ' || my_courseid_g || ' sectionid ' || my_sectionid || ' grade ' || my_grade || ' course description ' || my_description
 || substr(my_description, length(my_description),1));
      
          
         EXIT WHEN c2%NOTFOUND;
         

         EXIT WHEN c1%NOTFOUND;
      
      END LOOP;

      commit;
      CLOSE c1;
      close c2;
   END;
   /


This post has been edited by baavgai: 01 September 2011 - 01:11 PM
Reason for edit:: tagged


Is This A Good Question/Topic? 0
  • +

Page 1 of 1