Handling a "too many values" exception in PLSQL

Dyumani Sharma 0
Viewed 45

I need to handle the "too many values" exception in a PLSQL program. The table is:

Table structure of emptest

But I am not able to handle the exception and the program always shows a pre-defined error. Anybody has any ideas?

I tried like this:

enter image description here

Not getting the user defined message.

plsql sql
3 Answers

The issue is that, in your table you have only four columns.

ENO
ENAME
ESAL
DEPTNAME

but you are tying to insert five column values.

insert into emptest values(v_eno, v_ename, v_esal, v_deptname, v_deptno);

Why are you inserting v_deptno when it is not present in the table?

Either alter your table and add one more column or update the code to insert four column value only.

Pirate0

What you are trying will not work. The pl/sql block will not compile because the sql statement is invalid - it has too many values.

I think you want to trap the error and show your own error message. But, you need to use EXECUTE IMMEDIATE in order to allow invalid sql in pl/sql.

To trap a specific oracle error with a custom message, use EXCEPTION_INIT

Here is an example:

create table testa (col1 NUMBER);

set serveroutput on size 999999
clear screen
DECLARE
  -- unnamed system exception
  too_many_values EXCEPTION; 
  PRAGMA EXCEPTION_INIT (too_many_values, -00913);   
  c_too_many_values VARCHAR2(512) := 'Dude, too many values !';

BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO testa VALUES (1,2)';
  EXCEPTION WHEN   too_many_values THEN
    dbms_output.put_line(c_too_many_values);
  END;
END;
/

Dude, too many values !


PL/SQL procedure successfully completed.
Koen Lostrie0

In you table you only have four columns, but you try to insert five column so error is generated error.

v_deptno is not your table

change your query as per following

 Insert Into emptest values(v_eno, v_ename, v_esal, v_deptname);

or add one more column to your table

ALTER TABLE emptest ADD DEPTNO Number(4);
Uday Dodiya0
Related