Home > IT Answers > Oracle > How do you get around an ORA-06550 error?
Help

Question

  Asked: Nov 28 2007   4:52 PM GMT
  Asked by: Jonsorr

How do you get around an ORA-06550 error?


Oracle 10g, ORA-06550, 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? or how can I can 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 compliation 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

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   1
  •  -1
Last Answered: Dec 5 2007  6:55 AM GMT  by Plsql


Latest Contributors: FrankKulash, Jonsorr


Hi Jonsorr,

Place this statement 'v_cust_num number := &Customer_Number;' in Executable section. So that you can trap the exceptions.

Thanks,
Srikanth.
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Oracle.

Looking for relevant Oracle Whitepapers? Visit the SearchOracle.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

FrankKulash  |   Nov 28 2007  6:07PM GMT

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.

 

FrankKulash  |   Dec 6 2007  5:20PM GMT

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.

 

Plsql  |   Dec 7 2007  11:08AM GMT

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.

 

FrankKulash  |   Dec 7 2007  3:23PM GMT

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.