CODEWITHSUNDEEP
oracle-databaseplsql
GodDamnLawyer
GodDamnLawyer

Reputation: 67

PLS-00103: Encountered the symbol ";" when expecting one of the following: case The symbol "case" was substituted for ";" to continue

I have a problem in my package body

Here is code:

CREATE OR REPLACE PACKAGE BODY emp_data IS
    PROCEDURE open_emp_cur_var(cv_emp IN OUT rt_emp, p_your_choice IN NUMBER)
    IS 
        BEGIN 
            CASE
                when p_your_choice=1 then open cv_emp for SELECT * FROM employees;
                else OPEN cv_emp for SELECT * FROM employees WHERE salary > 8000;
            end;
    end;
end open_emp_cur_var;
END emp_data;

It returns me it was compiled with errors.

This is two errors it returns me.

8/16      PLS-00103: Encountered the symbol ";" when expecting one of the following:     case The symbol "case" was substituted for ";" to continue. 
11/1      PLS-00103: Encountered the symbol "END"

Upvotes: 0

Views: 2506

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You may use IF..END IF if you are unfamiliar with CASE

Also, you are probably looking for a REFCURSOR OUT variable.

CREATE OR REPLACE PACKAGE  emp_data IS
 PROCEDURE open_emp_cur_var (
          cv_emp OUT SYS_REFCURSOR , p_your_choice IN NUMBER
     );
     END;
     /

CREATE OR REPLACE PACKAGE BODY emp_data IS
     PROCEDURE open_emp_cur_var (
          cv_emp  OUT SYS_REFCURSOR, p_your_choice IN NUMBER
     ) IS
     BEGIN
          IF p_your_choice = 1 THEN
               OPEN cv_emp FOR SELECT *
                               FROM employees;

          ELSE OPEN cv_emp FOR SELECT *
                              FROM employees
                              WHERE salary > 8000;
     END IF;
     END;

END emp_data;
/

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191275

Your first END needs to be END CASE; and you have an extra END:

CREATE OR REPLACE PACKAGE BODY emp_data IS
    PROCEDURE open_emp_cur_var(cv_emp IN OUT rt_emp, p_your_choice IN NUMBER)
    IS 
    BEGIN 
        CASE
            WHEN p_your_choice=1 THEN
                OPEN cv_emp for SELECT * FROM employees;
            ELSE
                OPEN cv_emp for SELECT * FROM employees WHERE salary > 8000;
        END CASE;
    END open_emp_cur_var;
END emp_data; 
/

Upvotes: 2

Related Questions