Home » SQL & PL/SQL » SQL & PL/SQL » Exception query (Oracle 10g)
Exception query [message #573396] Thu, 27 December 2012 19:11 Go to next message
Bagheera
Messages: 2
Registered: December 2012
Junior Member
Hi,

I'm a newbie to PL/SQL. I had a quick query about trapping exceptions.

I have a sample table called my_emp, which contains last name, salary, etc. I have written the following code that takes in an employee salary and if the salary exists it displays the last name and corresponding salary. If two or more rows are returned, the exception handles it. Likewise if there are no records with that salary, the exception takes care of it.

I was trying to input an alphanumeric input, such as 1bbb as the salary and of course ORA-06502 error pops up in the sql command line. I now want to trap this using an exception but whatever I try I still get the ORA-06502 in the calling environment rather than getting the 'Not a number' or 'Some other error occured' message. Any reason why the WHEN VALUE_ERROR or the WHEN OTHERS exceptions are not trapping the error?

Thanks so much.
Birdy


DECLARE
	v_sal NUMBER (12) := '&Enter_salary';
	v_last_name VARCHAR2(10);
BEGIN
	SELECT last_name
	INTO v_last_name
	FROM my_emp
	WHERE salary=v_sal;
	DBMS_OUTPUT.PUT_LINE('Name: ' || v_last_name || ' Salary: ' || v_sal);
EXCEPTION
	WHEN TOO_MANY_ROWS THEN
	DBMS_OUTPUT.PUT_LINE('More than one employee with salary of ' || v_sal);
	WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE('No employees with salary of ' || v_sal);
	WHEN VALUE_ERROR THEN
	DBMS_OUTPUT.PUT_LINE('Not a number');	
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('Some other error occured');
END;

[Updated on: Thu, 27 December 2012 19:31]

Report message to a moderator

Re: Exception query [message #573397 is a reply to message #573396] Thu, 27 December 2012 20:01 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
When you start with the wrong question, no matter how good an answer you get, it won't matter very much.

DBMS_OUTPUT is the wrong "interface" for presenting error messages to most end users.
Most folks use web browser to interact with remote DBs. Right?
EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer
Re: Exception query [message #573410 is a reply to message #573397] Fri, 28 December 2012 04:03 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When you run the procedure and enter something that is not a number, this is the output:
Enter value for enter_salary: 1b
old   2:        v_sal NUMBER (12) := '&Enter_salary';
new   2:        v_sal NUMBER (12) := '1b';
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

Pay attention to the last line which says at line 2. This is line 2:
v_sal NUMBER (12) := '&Enter_salary';
It is in the DECLARE section and is not trapped by any of your WHEN exception handlers. Now, if you rewrite the code in a manner that you ask for the value in the BEGIN-END section, things change:
DECLARE
	v_sal NUMBER (12); 
	v_last_name VARCHAR2(10);
BEGIN
    v_sal := '&Enter_salary';
	
	SELECT ename
	INTO v_last_name
        ...

Enter value for enter_salary: 1b
old   5:     v_sal := '&Enter_salary';
new   5:     v_sal := '1b';
Not a number

PL/SQL procedure successfully completed.

SQL>

Now you got your "Not a number" message.
Re: Exception query [message #573414 is a reply to message #573396] Fri, 28 December 2012 05:01 Go to previous message
Michel Cadot
Messages: 59282
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
	WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('Some other error occured');


NEVER ever do that.
Read WHEN OTHERS.

Regards
Michel
Previous Topic: Passing input parameter to a like variable in procedure
Next Topic: Join tables on Date with MAX
Goto Forum:
  


Current Time: Wed Oct 01 03:50:24 CDT 2014

Total time taken to generate the page: 0.12854 seconds