CODEWITHSUNDEEP
sqlplsqlplsqldeveloper
Jash
Jash

Reputation: 45

Exception handling in Pl/sql

declare
x1 number;
y1 number;

begin
x1:=&x1_value;
y1:=&y2_value;
insert into tblnewdata_jk4 values(70,'July',x1,y1);
commit;

exception
when invalid_number then
dbms.output.put_line('err');

end;
/

I have tried to write an exception block which will validate whether the input entered by the user is number or not.But I am unable to do so.Can anyone help me this issue.

Upvotes: 0

Views: 67

Answers (1)

brenners1302
brenners1302

Reputation: 1478

  • First, dbms.output.put_line('err'); should be written like this dbms_output.put_line('err')

  • Secondly, INVALID_NUMBER for exception wont work unless you are converting a string to a number. You should use VALUE_ERROR

  • Lastly, Please avoid using & as a bind variable in pl sql, it has no meaning in pl sql because it is a feature in SQL*Plus. For more details, read this

Try this:

declare
x1 number;
y1 number;

begin
x1:=:x1_value;
y1:=:y2_value;
insert into tblnewdata_jk4 values(70,'July',x1,y1);
commit;

exception
when VALUE_ERROR then
dbms_output.put_line('err');

end;
/

Upvotes: 3

Related Questions