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? 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
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!