How do you get around an ORA-06550 error?

20 pts.
Tags:
ORA-06550
Oracle 10g
Oracle error messages
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
ASKED: November 28, 2007  4:52 PM
UPDATED: April 18, 2013  7:38 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi Jonsorr,

Place this statement

'v_cust_num number := &Customer_Number;'

in Executable section. So that you can trap the exceptions.

Thanks,
Srikanth.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • FrankKulash
    Good call! v_cust_num number := ddd; is a compilation error, but v_cust_num number := 'ddd'; is a run-time error. In this case, run-time is only a fraction of a second after compile-time, but, as you pointed out, compilation errors can't be trapped.
    1,240 pointsBadges:
    report
  • FrankKulash
    Jonsorr gave the correct answer to his own question 15 minutes after he asked it. Jonsorr's solution was to treat the substitution variable on line 3 as a string:
    begin
    declare
    v_cust_num number := '&Customer_Number';
    
    This was already in an executable section, which began with the BEGIN statement on line 1 and had an EXCEPTION section already equipped to handle the error. The EXCEPTION section of the nested block, which began on line 2, could not trap the error; that's why Jonsorr used a nested block. Srikanth's solutiion is essentially the same as Jonsorr's, with the added suggestion that the assignment be separated from the declaration: not necessary, but not necessarily bad, either. That would move the error into the inner block (the one starting with DECLARE on line 2), so the exception handling would have to move into the inner block as well. After that, there would be no reason to have the outer block at all, and it should be removed.
    1,240 pointsBadges:
    report
  • plsql
    Hi Frank, Sorry, I haven't seen "begin" of the Jonsorr's code. Now, I got the Jonsorr's question. :-) As you said, If we use the assignemnt statement in Declare section, again we have to write the code in nested block to handle declare section's execeptions. So, please advise me, In this case, what would be the better coding practice, whether the assignement statment should be used in Executable or Declare section?. Thank you, Srikanth.
    25 pointsBadges:
    report
  • FrankKulash
    Hi, Srikanth, The fact that you didn't see the first BEGIN statement illustrates the importance of indenting code. Jonsorr's code actually was indented, but he didn't use the [code] button when posting on this site. You ask, "In this case, what would be the better coding practice", A:
    begin
    	declare
    		v_cust_num number := '&Customer_Number';
    		...
    	begin
    		...
    	end;
    exception
    	when value_error then
    		...
    end;
    
    or B:
    declare
    	v_cust_num number;
    	...
    begin
    	v_cust_num number := '&Customer_Number';
    	...
    exception
    	when value_error then
    		...
    end;
    
    In general, shorter is better. Declaring and initializing together in one statement is shorter than declating in one statement and later initializing in a second statement, and I find it is usually better (easier to read, understand, debug and maintain). On the other hand, one block is shorter than two, and, in my experience, usually better. In this case, I think B is the better coding practice. Anyone writing new code should try to do B rather than A. I'll leave it up to Jonsorr to decide, in this case, which is the better professional practice, given that he already has an instance of A that works perfectly, whether or not he should spend the time to re-write it as B.
    1,240 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following