Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Compilation Error in Procedure

User_GVHF4May 23 2023

I get 2 errors on the last 2 lines of the following code. If I remove the CASE in this Proc then it works fine.

How do I fix it?

[Error] Compilation (22: 1): PLS-00103: Encountered the symbol "END" when expecting one of the following:

; <an identifier> <a double-quoted delimited-identifier>

[Error] Compilation (22: 1): PLS-00103: Encountered the symbol "END" when expecting one of the following:

; <an identifier> <a double-quoted delimited-identifier>

CREATE OR REPLACE PROCEDURE usp_Testproc
     ( p_MemberID IN VARCHAR2, p_MemberType CHAR
     )
   AS
     v_refcur    SYS_REFCURSOR;
   BEGIN    

CASE p_MemberType
   WHEN 'M' THEN
    OPEN   v_refcur FOR
        SELECT * from members WHERE MemberID = p_MemberID;  
    
   WHEN 'T' THEN  
    OPEN   v_refcur FOR 
    SELECT * from members WHERE MemberType = p_MemberType;
    
  ELSE
      Begin
            SELECT * from members;
      End
           
END;
    
    DBMS_SQL.RETURN_RESULT (v_refcur);
  END usp_Testproc;
  /
This post has been answered by Barbara Boehmer on May 23 2023
Jump to Answer
Comments
Post Details
Added on May 23 2023
3 comments
148 views