|
|
Re: the EXCEPTION didn't work correctly [message #307090 is a reply to message #307084] |
Mon, 17 March 2008 16:25   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
SQL> create or replace function test_func
2 return number
3 is
4 begin
5 return 0;
6 exception
7 when others then
8 null;
9 end;
10 /
Function created.
SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE',
2 'ERROR:06002';
Session altered.
SQL> create or replace function test_func
2 return number
3 is
4 begin
5 return 0;
6 exception
7 when others then
8 null;
9 end;
10 /
SP2-0806: Function created with compilation warnings
SQL> show errors
Errors for FUNCTION TEST_FUNC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1 PLW-05005: function TEST_FUNC returns without value at line 9
SQL>
Regards
Raj
|
|
|
|
|
Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307327 is a reply to message #307326] |
Tue, 18 March 2008 08:29   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
RETURN NUMBER
IS
sal NUMBER;
BEGIN
SELECT salary
INTO sal
FROM employees
WHERE employee_id = v_id;
RETURN sal;
EXCEPTION
WHEN OTHERS
THEN
--DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
raise_application_error(-20001,'NO such ID for any Employee in this company ');
END;
/
But the thing is it will show error with error code
ORA-20001: NO such ID for any Employee in this company
If you want only - NO such ID for any Employee in this company
then it will be handled at the front end only.
|
|
|
Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307328 is a reply to message #307324] |
Tue, 18 March 2008 08:33   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
SQL> create or replace function test_func(parm1 varchar2)
2 return number
is
3 4 test_num number;
5 begin
6 test_num := to_number(parm1);
7 dbms_output.put_line('Input value is : ' || test_num);
8 return 0;
9 exception
10 when others then
11 dbms_output.put_line('I am masking the actual error . So please forgive me');
12 return null;
13 end;
14 /
SQL> select test_func('123') from dual;
TEST_FUNC('123')
----------------
0
Input value is : 123
SQL>
SQL> select test_func('abc') from dual;
TEST_FUNC('ABC')
----------------
I am masking the actual error . So please forgive me
SQL>
Quote: |
ORA-06503: PL/SQL: Function returned without value
|
Tell me what you didn't understand from this statement ?
Regards
Raj
|
|
|
Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307329 is a reply to message #307328] |
Tue, 18 March 2008 08:37   |
ora_2007
Messages: 430 Registered: July 2007 Location: Mumbai
|
Senior Member |
|
|
Please check the following code.
I hope it will solve your error of
Quote: | ORA-06503: PL/SQL: Function returned without value
|
CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
RETURN NUMBER
IS
sal NUMBER;
BEGIN
BEGIN
SELECT salary
INTO sal
FROM employees
WHERE employee_id = v_id;
EXCEPTION
WHEN OTHERS
THEN
--DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
raise_application_error(-20001,'NO such ID for any Employee in this company ');
end;
RETURN sal;
END;
/
|
|
|
|
Re: WHAT !!!!!!!?????????!!!!!!!!!!!!! [message #307346 is a reply to message #307329] |
Tue, 18 March 2008 09:33   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
ora_2007 wrote on Tue, 18 March 2008 14:37 | Please check the following code.
I hope it will solve your error of
Quote: | ORA-06503: PL/SQL: Function returned without value
|
CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
RETURN NUMBER
IS
sal NUMBER;
BEGIN
BEGIN
SELECT salary
INTO sal
FROM employees
WHERE employee_id = v_id;
EXCEPTION
WHEN OTHERS
THEN
--DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
raise_application_error(-20001,'NO such ID for any Employee in this company ');
end;
RETURN sal;
END;
/
|
This is just nonsense code.
Why the nested block? Why the WHEN OTHERS? What error will be returned when there is more than one employee with a given id?
|
|
|
|
|
|
|
Re: the EXCEPTION didn't work correctly [message #307804 is a reply to message #307769] |
Thu, 20 March 2008 00:47   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Ok, here is what I mean:
Why are you so keen on only displaying a single line of errors? That can only be of importance if you want dead-end users to use your application.
If that is true, sqlplus/TOAD/PLSQL-Developer and such are NOT the tools to use; these are development tools.
To enable a user, that is scared off by additional error-lines, to use your package, you need to write a wrapper-application. Use Forms, Java or whatever.
User pushes a button, your code is executed, exception is raised (your exception-handler can be removed) and caught by the nice GUI. Next, the GUI strips the extra lines and only displays the error you want to display.
This is called application development. It is, well, what we developers do..
|
|
|
|
|
|
|
Re: please focus in my question [message #307896 is a reply to message #307882] |
Thu, 20 March 2008 05:02   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
Hany Freedom wrote on Thu, 20 March 2008 10:19 | if you have the code that I need , so write it and I will be soooooooooooooooooooo grateful for you .... if not so please DON'T ANSWER.
| Oh, I will answer although there is no code for you. Frank is right: you need a FRONT END application to handle the errors.
Now, I want you to read the the forum guidelines. It's not the first time you display this infantile impatience and frustration.
MHE
PS: changing fonts will not help either.
Edit: Tom Kyte might explain it a little bit further here.
[Updated on: Thu, 20 March 2008 05:11] Report message to a moderator
|
|
|
|
Re: please focus in my question [message #307955 is a reply to message #307896] |
Thu, 20 March 2008 09:35  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The actual exception that occurs when there is no matching employee_id is no_data_found, so it would be better to at least trap that exception first, then return sqlerrm only if that is not the problem and there is some other exception instead. That way if there is something else wrong besides no matching employee_id the one-line error message will give you some clue as to what the problem is.
CREATE OR REPLACE FUNCTION qq (v_id NUMBER)
RETURN NUMBER
IS
sal NUMBER;
BEGIN
SELECT salary
INTO sal
FROM employees
WHERE employee_id = v_id;
RETURN sal;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('NO such ID for any Employee in this company ');
RETURN NULL;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
RETURN NULL;
END;
/
|
|
|