Q
Problem solve Get help with specific problems with your technologies, process and projects.

Can't figure out why cursor is invalid

This is my first time using a cursor, and I can't seem to find the error that makes this procedure invalid. Please help.

 
CREATE OR REPLACE  PROCEDURE
 "ROSENJS"."SPADDUSER_ROLE" (p_Name 
     in varchar2,  p_RoleID in Number) IS
 
      cursor get_userid(userName) is
       --input variable = userName
       --Output variable = p_UserID
       Select userID from xsparesUsers where name=
 userName;
       get_userid_record get_userid%ROWTYPE;
       
  BEGIN
   open get_userid(p_Name);
   While True Loop
     fetch get_userid into get_userid_record;
   Exit When get_userid%NOTFOUND;
 
     Insert into User_Role(UserID, RoleID) 
       Values(get_userid_record.userid, p_RoleID);
   END Loop;
   IF(get_userid%ISOPEN) THEN
         CLOSE get_userid;
   END IF;
  
 Commit; 
 Exception
  When Others Then
   RollBack;
  Raise;
 END;

There are some syntax errors, but basically, everything looks ok. So here is what it should probably look like:

CREATE OR REPLACE  PROCEDURE ROSENJS.SPADDUSER_ROLE
(p_Name in varchar2,  p_RoleID in Number) IS

   cursor get_userid(userName) is
      Select userID from xsparesUsers 
      where name=userName;
     
   get_userid_record get_userid%ROWTYPE;
       
BEGIN
   open get_userid(p_Name);
   Loop
      fetch get_userid into get_userid_record;
      Exit When get_userid%NOTFOUND;

      Insert into User_Role(UserID, RoleID) 
        Values(get_userid_record.userid, p_RoleID);
   END Loop;

   -- No need to check. The cursor is open.
   -- so close it
   CLOSE get_userid;

Exception
  When Others Then
  RollBack;
  Raise;
END; 
/

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close