I want to solve this problem in PL/SQL the EXCEPTION command does not work-CodePudding

I hope you are fine. I am facing a problem. I dont know why it coming to me, although I think that the code is complete and there are no problems. You will find the code and the error below.

THE CODE

DECLARE
v_jobid EMPLOYEES.job_id%TYPE;

BEGIN
SELECT job_id INTO v_jobid
FROM EMPLOYEES
WHERE department_id = 80;

EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('ERROR1');
END;

THE ERROR

ORA-06550: line 2, column 9:
PLS-00201: identifier 'EMPLOYEES.JOB_ID' must be declared
ORA-06550: line 2, column 9:
PL/SQL: Item ignored
ORA-06550: line 6, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored

1. DECLARE
2. v_jobid EMPLOYEES.job_id%TYPE;
3. BEGIN
4. SELECT job_id INTO v_jobid

CodePudding user response：

This is what you have now:

SQL> DECLARE
  2  v_jobid EMPLOYEES.job_id%TYPE;
  3
  4  BEGIN
  5  SELECT job_id INTO v_jobid
  6  FROM EMPLOYEES
  7  WHERE department_id = 80;
  8
  9  EXCEPTION
 10  WHEN TOO_MANY_ROWS THEN
 11  DBMS_OUTPUT.PUT_LINE('ERROR1');
 12  END;
 13  /
v_jobid EMPLOYEES.job_id%TYPE;
        *
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00201: identifier 'EMPLOYEES.JOB_ID' must be declared
ORA-06550: line 2, column 9:
PL/SQL: Item ignored
ORA-06550: line 6, column 6:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored

Aha. Table or view doesn't exist:

SQL> desc employees;
ERROR:
ORA-04043: object employees does not exist

Let's create it (with only necessary columns for your code to work):

SQL> create table employees (job_id number, department_id number);

Table created.

Any improvement?

SQL> DECLARE
  2  v_jobid EMPLOYEES.job_id%TYPE;
  3
  4  BEGIN
  5  SELECT job_id INTO v_jobid
  6  FROM EMPLOYEES
  7  WHERE department_id = 80;
  8
  9  EXCEPTION
 10  WHEN TOO_MANY_ROWS THEN
 11  DBMS_OUTPUT.PUT_LINE('ERROR1');
 12  END;
 13  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5


SQL>

Yes, that's better. Though, it now raised error you didn't handle (no_data_found).

SQL> set serveroutput on
SQL> DECLARE
  2  v_jobid EMPLOYEES.job_id%TYPE;
  3
  4  BEGIN
  5  SELECT job_id INTO v_jobid
  6  FROM EMPLOYEES
  7  WHERE department_id = 80;
  8
  9  EXCEPTION
 10  WHEN TOO_MANY_ROWS THEN
 11  DBMS_OUTPUT.PUT_LINE('ERROR1');
 12
 13  when no_data_found then
 14  DBMS_OUTPUT.PUT_LINE('ERROR2');
 15  END;
 16  /
ERROR2

PL/SQL procedure successfully completed.

SQL>

Now code looks OK.