QUESTION POSED ON: 25 January 2008
I am making a program that will ask the user to enter a customer number. All of the customer numbers are numbers only. If the user enters in a customer number with a letter in it by mistake they will receive an ora-06550 error on that line number. How can I create an exception that will allow me to give the user a nice message to retype the customer number instead of all of the ora error lines? Or, how can I write a line of code that will check the user input to make sure it only contains numbers before Oracle sees it as an error, since you can't make an exception for a compilation error?
Here is my code:
set serveroutput on
begin
declare
v_cust_num number := &Customer_Number;
v_name varchar2(20);
v_address varchar2(20);
v_city varchar2(12);
v_state varchar2(2);
v_zip varchar2(5);
begin
if v_cust_num < 0 then
raise_application_error (-20000,'Customer number can not be negative');
else
select firstname || ' ' || lastname, address, city, state, zip
into v_name, v_address, v_city, v_state, v_zip
from customers
where customer#= v_cust_num;
dbms_output.put_line(v_name);
dbms_output.put_line(v_address);
dbms_output.put_line(v_city || ', '|| v_state ||' '|| v_zip); end if; exception
when no_data_found then
dbms_output.put_line(chr(10));
dbms_output.put_line('Customer number entered does not exist.');
end;
exception
when value_error or invalid_number then dbms_output.put_line(chr(10)); dbms_output.put_line('Customer number not entered correctly.'); end;
here is the error if ddd is input by user:
old 3: v_cust_num number := &Customer_Number; new 3: v_cust_num number := ddd;
v_cust_num number := ddd;
*
ERROR at line 3:
ORA-06550: line 3, column 24:
PLS-00201: identifier 'DDD' must be declared
ORA-06550: line 3, column 14:
PL/SQL: Item ignored
ORA-06550: line 10, column 6:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored
|
